Enabling SSL on PostgreSQL

In this post, we are going to walk through the steps for enabling ssl on postgresql on your Mac OSX. Enabling SSL requires the following steps.

1. Install openssl on your machine
2. Creating a new server side certificate
3. Update postgresql.conf to turn ssl on
4. Restart the database and test the connection

To install openssl simply type brew install openssl on your command prompt.

Once ssl is installed, create a new server key request

$ openssl req -new -text -out server.req

Enter a passphrase

Follow the steps to create the private key.

Next use the private key to create a server.key

 Enter the passphrase to confirm generation of the server key


 Now delete the private key to prevent it from falling in the wrong hands.

Use the appropriate commands to create a server certificate

openssl req -X509 -in server.req -text -key server.key -out server.cert

Next edit the file postgresql.conf by typing

$ sudo vi /usr/local/var/postgres/postgresql.conf

Change ssl to on

Next, copy the server.crt and server.key files to the $PG_DATA folder

$ sudo mv server.{crt,key} /usr/local/var/postgres

Make sure the database owner has ownership on the server.key file

$ sudo chown arthgallowachs /usr/local/var/postgres/server.key


Finally, restart the Postgresql database by entering the following command

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart

Once the database has restarted, login by specifying localhost to make sure the database is being connected over TCP/IP

$ psql -h localhost -U postgres

That's it we have enabled SSL using a self signed certificate.