Forum OpenACS Q&A: Re: ODBC-connection to Postgres database

Posted by Richard Hamilton on
I have this set up on our servers so that I can connect to postgres using MS Access down an SSH tunnel. This avoids having to risk exposing postgres to the outside world and it works very well. Most of the postgres steps have been covered in this thread and I will email you the windows ODBC driver that I am using.

There are a couple of little catches (one is that postgres cannot cope with the Access "Yes/No" datatype), the other is that you must run a modification query against the underlying postgres template (mine is template1) in order to add support for certain ODBC calls that postgres does not support by default. You will find the details of this not in the administrator manual but in the docs in the distribution file tree under ODBC install. All of your databases must be created (createbd) AFTER this modification to the template for obvious reasons.

If the tcpip= entry in the postgres config file is not set to true, postgres will not listen to any IP based connections even localhost, it cannot use unix sockets for ODBC. That is why I use tunnelling to add a layer of protection. It also avoid having to try to configure postgres with encryption.

Hope that helps.