ssl on postgres

Question

I'm trying to set up my postgres server to do ssl connections. I set up the postgresql.conf and the pg_hba.conf correctly. I created a key and self-signed cert (server.crt and server.key) in the postgres data dir and copied the server.crt to root.crt.

I also set up a cert and key on another host using the root.crt from the server to sign the client cert. When I use openssl to test the cert everything is fine. However, when I try to connect to the postgres server it fails:

psql -h <HOSTNAME> -U <USER> -d <DB> psql: SSL error: certificate verify failed 

Alternatively:

openssl s_client -connect <HOSTNAME>:5432 -state -msg -showcerts -debug CONNECTED(00000003) SSL_connect:before/connect initialization write to 080ACFE0 [080AD570] (145 bytes => 145 (0x91)) 0000 - 80 8f 01 03 01 00 66 00-00 00 20 00 00 39 00 00 ......f... ..9.. ... 0080 - 04 fd be bd 49 e7 1d 99-f5 bb 7e 24 2e fe 34 e8 ....I.....~$..4. 0090 - d7 . >>> SSL 2.0 [length 008f], CLIENT-HELLO 01 03 01 00 66 00 00 00 20 00 00 39 00 00 38 00 ... be bd 49 e7 1d 99 f5 bb 7e 24 2e fe 34 e8 d7 SSL_connect:SSLv2/v3 write client hello A read from 080ACFE0 [080B2AD0] (7 bytes => 0 (0x0)) 18357:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: 

Finally, if I set up openssl to serve on the server side and connect from the client host, it works fine:

openssl s_client -connect <HOSTNAME>:4433 -state -msg -showcerts -debug 

Any ideas why postgres is balking at my certs? For my current setup the server is FreeBSD 4.7 running postgres 8.1, and the client is CentOS 4 running postgres 9.0. Not sure if that factors in here...

Answer 1

PostgreSQL does not begin the SSL negotiation until you've sent a packet instructing it to do so. s_client expects it to do SSL before that packet. See http://www.postgresql.org/docs/9.0/static/protocol-flow.html#AEN84524 for documentation about how SSL works in PostgreSQL.

Double-check that your root cert is in the correct location on the client, and that it's properly readable by postgres. Perhaps to the point of running strace to make sure it's actually read. that's by far the most common reason things don't work.

And as has been previously noted, you should get off 8.1 as soon as possible, since it's already end-of-lifed. But it should have no major effect on this.

Answer 2

Removing my old ~/.postgresql solved the problem for me. Try to strace psql to check for open calls.

Answer 3

This should work:

echo "" | openssl s_client -starttls postgres -connect EXAMPLE.COM:5432 -showcerts