Configuring ODBC for access from Unix with unixodbc - Mailing list pgsql-odbc

I have developed the following set of instructions for using ODBC with
Unix and unixodbc.  I would be grateful for comments.  Readers of the
PostgreSQL lists should note that these instructions are written for the
Debian package.  The appropriate pathnames should be substituted as
necessary, and PostgreSQL should be built with these configuration
options:

   --enable-odbc --with-unixodbc

/etc/odbcinst.ini and /etc/odbc.ini will be provided by unixodbc (in
Debian, by the package odbcinst1).

The instructions and the template files are attached.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "If we confess our sins, he is faithful and just to
      forgive us our sins, and to cleanse us from all
      unrighteousness."       I John 1:9
HOW TO set up odbc-postgresql with unixodbc to allow access to other
Unix programs
====================================================================

1. Install packages unixodbc and odbcinst1

2. Add a PostgreSQL driver to /etc/odbcinst.ini:

   # odbcinst -i -d -f /usr/lib/postgreql/share/odbcinst.ini.template

3. Add the template1 database (with read only access) to the default odbc.ini

   # cat /usr/lib/postgreql/share/odbc.ini.template >>/etc/odbc.ini

   Edit /etc/odbc.ini as required, to add extra databases, change the read only
   status and so on.

4. Add ODBC required functions to the databases that you wish to access
   from ODBC:

   # su - postgres
   $ for dbname in database1 database2 ...
     do
        psql -d  $dbname < /usr/lib/postgresql/sahre/odbc.sql
     done

   If you include template1 in the list, any database created in future will
   automatically have these functions included when it is created.


At this point, you should be able to use the ODBC access features of anyr
application to connect to any of the listed databases.   For example, in
StarOffice, you can click New->Database, select the ODBC database type
and browse the list of databases from /etc/odbc.ini and ~/.odbc.ini.


Each user has or can have a file ~/.odbc.ini, whose structure is the same
as /etc/odbc.ini.  This file is read together with the /etc/odbc.ini to give
a complete list of databases accessible to the user.  (Of course, any
access restrictions imposed by pg_hba.conf are still applicable.)

You can create templates for different databases and allow users to add
them to their own ~/.odbc.ini.  Use the odbc.ini template file provided in
/usr/lib/postgreql/lib/odbcinst.ini.template as a model.  A good and
reliable way for a user to add this template to his ~/.odbc.ini without
risk of damaging his file is for him to use this command:

  $ odbcinst -i -s -f /path/to/your/template/file
[PostgreSQL]
Description=PostgreSQL ODBC driver for Linux and Windows
Driver=/usr/lib/postgresql/lib/libodbcpsql.so
Setup=/usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 1

[PostgreSQL]
Description         = PostgreSQL template1
Driver              = PostgreSQL
Trace               = No
TraceFile           = /tmp/odbc.log
Database            = template1
Servername          = localhost
UserName            =
Password            =
Port                = 5432
Protocol            = 6.4
ReadOnly            = Yes
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =