depthq - ccm|mac

If you want to check to ensure your SSL or non-SSL ports in PostgreSQL are working properly, read further to find out how.

For SSL support, you need to setup your PostgreSQL to work with SSL properly. Just turn on the ssl flag in postgresql.conf and then make sure you've setup the server.crt as mentioned in PostgreSQL docs about Secure TCP/IP Connections with SSL.

If you want force psql to use non-SSL for the connection, you need to set the PGSSLMODE environment variable. If you're using bash, do something like this:

export PGSSLMODE=disable

And then try to run the psql to connect to the server. I would suggest that you be explicit in your pg_hba.conf to specify that you want hostnossl, so that the server will not accept SSL connections.

If you got things configured correctly, it should work without problems. On the other hand, you can force it to use SSL, like this:

export PGSSLMODE=require

You should see a fatal error message from psql, stating that there is no pg_hba.conf entry for SSL on. You can use the same method to test for SSL-only connections by using hostssl instead in pg_hba.conf.

This environment variable works not only for psql but also for any library or tool that uses libpq. For example, I include the console output for testing the psycopg library. I have already set up the PostgreSQL to work with SSL only.

silviana:~ cheeming$ export PGSSLMODE=disable
silviana:~ cheeming$ python2.4
Python 2.4.5 (#1, Jul 15 2008, 23:37:00) 
[GCC 4.0.1 (Apple Inc. build 5465)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg
>>> psycopg.__version__
'1.1.21'
>>> psycopg.connect('host=127.0.0.1 dbname=mydatabase')
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
psycopg.OperationalError: FATAL:  no pg_hba.conf entry for host "127.0.0.1", 
    user "cheeming", database "mydatabase", SSL off
>>> psycopg.connect('host=127.0.0.1 dbname=mydatabase sslmode=require')
<connection object at 0x6a180>
>>> 
silviana:~ cheeming$ export PGSSLMODE=require
silviana:~ cheeming$ python2.4
Python 2.4.5 (#1, Jul 15 2008, 23:37:00) 
[GCC 4.0.1 (Apple Inc. build 5465)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg
>>> psycopg.connect('host=127.0.0.1 dbname=mydatabase')
<connection object at 0x6a1d8>

As I tried out using the environment variable way, I realised that you can also change the data source name to include sslmode=require, the same like the PGSSLMODE environment variable. I have updated the console listing above to show that as well.

Oh by the way, I heard a rumour that psycopg 1 doesn't support SSL. I am not 100% sure about it. Its quite hard to find out for sure (as I have tried on google, maybe I am not RTFM-ing enough) other than just testing it. The only info I found indicating this is some guy complaining about ssl not being supported in Windows.

Looking at the psycopg source code shows that there is support for something called sslmode. And in the ChangeLog file, its stated as follows:

2004-01-10  Federico Di Gregorio  <fog@debian.org>

    * module.c (psyco_connect): added "sslmode" parameter.

There are more environment variables that you can tweak to change the behaviour of the libpq C library and its stated here.

Hope this is useful for future PostgreSQL bug hunters!

It's really pretty simple actually. But the problem seems to be that the documentation that we wrote somehow skipped this issue altogether or documented it wrongly, which I guess would have caused some really confusing experience.

The problem is when the process that accesses the database is executed as a different user from the user that has the rights to the database. For example, when running Apache, you would normally execute it as the www-data user, but the database user in PostgreSQL is different, like cheeming. What we need to do now, is to tell PostgreSQL about this mapping.

We need to edit the pg_hba.conf and the pg_ident.conf files. In the pg_hba.conf file, we add a line that looks something like this:

local   my-db-name   my-db-user-name   ident my-map-name

Example:

local   depthq       cheeming          ident www

Here is where I had a problem, someone wrote that the my-db-user-name is meant to be the user of the process that accesses the database, e.g. www-data. So I did that and it didn't work. Luckily, I referred to the PostgreSQL documentation. That's a RTFM lesson for me .

And in your pg_ident.conf file, you need something like this

my-map-name   some-user-name   my-db-user-name

Example:

www           www-data         cheeming

According to PostgreSQL client authentication documentation:

... The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied. ...

So does it mean it parses the file from top to bottom? I have no idea. Putting the line right after the first local line worked fine for me.

Once you make the changes to the file, restart the PostgreSQL server and try again. You can do some debugging with the following commands:

sudo su www-data
psql depthq -U cheeming

If you managed to login, then rejoice! You have conquered Ident.