4

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

Eric Snow
  • 145
  • 1
  • 5
  • 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 09 '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 09 '11 at 20:51

2 Answers2

5

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.

Magnus Hagander
  • 2,287
  • 15
  • 9
3

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

foudfou
  • 131
  • 2