When I was first asked to encrypt my postgres DB connections I had no idea where to start.
I needed to learn more about SSL. So I spent a few nights reading blogs and creating CAs and certs to secure a simple nginx website with SSL on one of my VBox VMs. I used a fantastic blog post to create CA, intermediate, and server certificates: OpenSSL Certificate Authority. This was perfect for simulating a real setup.
Then I spent an hour or so figuring out how to secure connections on my playground DB. Turns out securing postgres DB servers is not difficult, so here’s a quick guide to set it up.
These procedures assume that you already have an SSL server certificate and SSL server key in PEM format. Your server key should not be more than 2048 bits. Keys using 4096 bits result in dramatically higher CPU usage.
You also need to ensure you have the CA bundle installed on all your client machines. If you’re using a certificate signed by a public signing authority like DigiCert or Verisign, then their CA bundle is probably already installed on your clients.
Do not use a passphrase-protected SSL key since it will require manually typing in the passphrase for the key each time postgres is started.
Install the key and certificate on your DB server:
cp server.crt server.key /var/lib/pgsql/9.5/data/
chown postgres:postgres server.key server.crt
chmod 600 server.key
chmod 644 server.crt
Be sure to restrict permissions on the server.key file or SSL will not work.
If your server certificate was signed by an “intermediate” signing certificate, then you will need to also append the intermediate certificate to the “server.crt” file.
Next you need to enable the following parameters in the “postgresql.conf” file:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
Restart postgres.
Some applications will not make an SSL connection unless the certificate can be fully validated. In most cases they will use the local OS builtin CA bundle database to validate the certificate.
Unfortunately psql won’t use the OS builtin CA database, so you will have to copy the CA bundle to a special directory for psql.
mkdir ~/.postgresql
cp your_root_ca_file ~/.postgresql/root.crt
By default psql will not perform any certificate validation. This. Makes. Me. Cry. But you can change that behavior by setting the following environment variable:
export PGSSLMODE=verify-full
Now you can use psql to do a full certificate validation test.
psql -U pg_user -h pg.server.com -d postgres
You should see a message like this if it is working:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Once you have it tested and working, you can force all connections to only use SSL. Simply change your “host” entries to “hostssl” in your “pg_hba.conf” file:
hostssl postgres pg_user 192.168.1.1/24 md5
That’s it! Your DB is now secured.
pgdba 