Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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...

share|improve this question
    
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

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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