The .odbc.ini file, which contains the access information for the psqlODBC driver, must be placed in the your home directory. The .odbc.ini file has two main sections:
The first section is called [ODBC Data Sources]. This is a list of arbitrary names, called data source names (DNS), and a brief description of the data source.
The second section contains the data source specification. There will be one of these specifications for each name given in the first section. Every specification must be labeled with the name given in [ODBC Data Sources] and must contain at least the Database and Servername entries, these setting controls the access information for the DSN.
Here is a sample .odbc.ini file:
[ODBC Data Sources] debug = Debugging Database RO = Read-only Database RHDB = Red Hat Database [debug] Database = rhdb1 Servername = machine1 Username = pcheung Password = "pcheung1" Driver = /usr/lib/libpsqlODBC.so Port = 5432 Debug = 1 CommLog = 1 ReadOnly = 0 [RO] Database = rhdb1 Servername = localhost ReadOnly = 1 [RHDB] Servername = localhost Database = basketball ReadOnly = No CommLog = 1 Debug = 1 |
In this sample .odbc.ini file, the first section specifies three DSNs: debug, RO, and RHDB.
The debug DSN specifies a connection to the database rhdb1 as user pcheung and password pcheung1, connecting to the server running on machine1 listening to port 5432, and using the driver found in /usr/lib/libpsqlODBC.so. Modify the Driver setting if another ODBC driver is used instead of the default one. Port 5432 is the default port number the server listens on, and 5432 is the default value if Port is not set in a DSN. Modifications can be made to the database because ReadOnly is set to 0 (off). Debug information and communications to and from the backend are logged by setting Debug = 1. When Debug is on, debug information goes to the /tmp/mylog_<userid><pid>.log file. Similarly, when CommLog is turned on (set CommLog to 1/Yes), the communications to and from the backend are logged to the /tmp/psqlODBC_<userid><pid>.log file. The CommLog contains information about the connection, the DSN, the queries being carried out, and so on. Both Debug and CommLog are off by default.
For the DSN RO, writing to the rhdb1 database on the local machine is prohibited because ReadOnly is set to on (1), its default value. The userid and password for this DSN will have to be handled within the application itself because they are not specified in the DSN definition.
The RHDB DSN connects to the database basketball with the database server running local, and with debug and communications output generated. One can also turn a setting on or off by giving it a value of Yes or No. For example, the DSN RHDB setting ReadOnly = No enables a user to update the database basketball.
Permission to access the databases from a client machine is required in order for ODBC to connect to it. To enable access to the database, modify the pg_hba.conf file appropriately. TCP/IP connection are required in order for ODBC to connect to the databases; you can do this by making appropriate changes to the tcpip_socket setting in the postgresql.conf file. Restart postmaster after all modifications are complete to invoke the new settings. For more information on granting database access permission, enabling TCP/IP connections and restarting the postmaster, see the Red Hat Database Administrator and User's Guide.