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


openssl s_client -connect <HOSTNAME>:5432 -state -msg -showcerts -debug
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...

  • PostgreSQL version 8.1 is already End Of Life. Consider an upgrade to a newer version, SSL support has also improved in later versions. 9.0 is a good candidate, you are already using the psql client. – Frank Heikens Feb 9 '11 at 10:55
  • Yeah, we are setting up our old server to replicate to the new one (9.0) via slony. We're setting up SSL for those connections. – Eric Snow Feb 9 '11 at 20:51

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.