1
HOW TO set up odbc-postgresql with unixodbc to allow access to other
3
====================================================================
5
1. Install odbc-postgresql and the packages unixodbc and odbcinst1
7
2. Add a PostgreSQL driver to /etc/odbcinst.ini:
9
# odbcinst -i -d -f /usr/lib/postgresql/share/odbcinst.ini.template
11
3. Add the template1 database (with read only access) to the default odbc.ini
13
# cat /usr/lib/postgresql/share/odbc.ini.template >>/etc/odbc.ini
15
Edit /etc/odbc.ini as required, to add extra databases, change the read only
18
4. Add ODBC required functions to the databases that you wish to access
21
# su -s /bin/bash - postgres
22
$ for dbname in database1 database2 ...
24
psql -d $dbname < /usr/share/psqlodbc/odbc.sql
27
If you include template1 in the list, any database created in future will
28
automatically have these functions included when it is created.
31
At this point, you should be able to use the ODBC access features of any
32
application to connect to any of the listed databases. For example, in
33
StarOffice, you can click New->Database, select the ODBC database type
34
and browse the list of databases from /etc/odbc.ini and ~/.odbc.ini.
37
Each user has or can have a file ~/.odbc.ini, whose structure is the same
38
as /etc/odbc.ini. This file is read together with the /etc/odbc.ini to give
39
a complete list of databases accessible to the user. (Of course, any
40
access restrictions imposed by pg_hba.conf are still applicable.)
42
You can create templates for different databases and allow users to add
43
them to their own ~/.odbc.ini. Use the odbc.ini template file provided in
44
/usr/lib/postgresql/share/odbc.ini.template as a model. Ignore the file
45
/usr/lib/postgresql/lib/odbcinst.ini, which is provided by the postgresql
46
package. It may be good for use with iodbc, but I have no experience
49
A good and reliable way for a user to add a template to his ~/.odbc.ini
50
without risk of damaging the file is for him to use this command:
52
$ odbcinst -i -s -f /path/to/your/template/file
54
Note: I have had a report that OpenOffice objects to tabs and/or whitespace
55
in ~/.odbc.ini and /etc/odbc.ini. I have not found this problem with
56
StarOffice 5, however.
61
If you have schemas that are not in the default search path ($user, public)
62
you have to set the search path in the connection settings of the odbc.ini
63
file. Here's an example, where the search path must be set to prod, public:
66
Description = Production control
69
TraceFile = /tmp/odbc.log
71
Servername = localhost
81
ConnSettings = SET SEARCH_PATH TO prod, public
added
removed
debian/unixodbc.HOWTO
