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.
First I followed instructions at
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.
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'
#/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
#/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.