Postgresql SSL certificates

I had a devil of a time getting SSL communication between a client and server on 2 virtual machines working, but I finally got it to work.

Helpful documentation:
http://www.postgresql.org/docs/9.1/static/ssl-tcp.html
http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
http://www.postgresql.org/docs/9.1/static/libpq-connect.html

First I followed instructions at
http://blog.didierstevens.com/2008/12/30/howto-make-your-own-cert-with-openssl/
to make a self signed root certificate for signing the server and client keys.

On each certificate request I set the CN to [hostname].mydomain.com and set up the /etc/hosts file on each machine to resolve the other correctly.

config files:
In /etc/postgresql/9.1/main/postgresql.conf I made the following changes:

listen_addresses = '*'
ssl = on
#uncommented ciphers
ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'

In /etc/postgresql/9.1/main/pg_hba.conf:

#/etc/postgresql/9.1/main/pg_hba.conf
#TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
hostssl all all all cert map=development
#hostssl all all all cert
#hostssl all all all md5

In /etc/postgresql/9.1/main/pg_ident.conf:

#/etc/postgresql/9.1/main/pg_ident.conf:
#MAPNAME SYSTEM-USERNAME PG-USERNAME
development /^.*mydomain\.com$ dev

On the client, I added the following to ~/.bashrc:

#choose one of the following:
export PGSSLMODE='verify-full' #full verification of hostname and certificate
#export PGSSLMODE='verify-ca' #verification of certificate
#export PGSSLMODE='require' #just verify security (will check against root.crt if exists)
#export PGSSLMODE='prefer' #let server decide on security

Client certificates are placed in the ~/.postgres folder and are named postgres.key , postgres.crt and root.crt

By following instructions at http://www.howtoforge.com/postgresql-ssl-certificates I realized that I had been putting the certificates in the wrong folders on the server.

I did not expect that even though the configuration files are in /etc the SSL key and certificates need to be in the ‘data_directory’ specified in postgresql.conf. If you put them in the wrong directory you will get errors such as:

dev@client:~$psql -h hostname.domain.com mydb dev
psql: SSL error: certificate verify failed

dev@server:~$tail /var/log/postgresql/postgresql-9.1-main.log
[date] [time] LOG: could not accept SSL connection: tlsv1 alert unknown ca

The host you specify to connect to on the psql command line needs to match the CN of the server.crt, and the CN on the postgresql.crt on the client needs to match the SYSTEM-USERNAME specified in pg_ident.conf.

My next challenge is to get streaming replication set up between the 2 servers.