How to Enable SSL in PostgreSQL

PostgreSQL supports SSL connection that allow users to securely connect to their databases. In this article we will look at how to enable SSL in PostgreSQL database.

How to Enable SSL in PostgreSQL

Here are the steps to enable SSL connection in PostgreSQL. On PostgreSQL server, we need 3 certificates in data directory for SSL configuration. They are:

• root.crt (trusted root certificate)
• server.crt (server certificate)
• server.key (private key)

Open terminal and run the following command to run as root

$ sudo -
$ cd /var/lib/pgsql/data

Generate private key using openssl. You will be prompted for passphrase.

$ openssl genrsa -des3 -out server.key 1024

Remove passphrase

$ openssl rsa -in server.key -out server.key

Bonus Read : Top Database Blogs to Follow

Update the file permission and ownership of private key file.

$ chmod 400 server.key
$ chown postgres.postgres server.key

Similarly, create server certificate

$ openssl req -new -key server.key -days 3650 -out server.crt -x509

In the above statement, -x509 indicates a self-signed certificate. You will be prompted for details such as email, country, etc. Enter it and complete the certificate generation.

Since we are using self-signed certificate we will use our server key as root certificate.

$ cp server.crt root.crt

Update pg_hba.conf to add the following lines

# IPv4 remote connections for authenticated users 
hostssl all www-data 0.0.0.0/0 md5 clientcert=1
hostssl all postgres 0.0.0.0/0 md5 clientcert=1

Edit postgresql.conf to add the following line

ssl = on

Restart PostgreSQL Server

$ /etc/init.d/postgresql restart

Bonus Read : How to Increase Max Connections in PostgreSQL

Enable SSL in PostgreSQL client

We also need 3 files to enable SSL in PostgreSQL client. We will store them at ~/.postgresql/ directory

• root.crt (trusted root certificate)
• postgresql.crt (client certificate)
• postgresql.key (private key)

Create postgresql.key on client machine and remove passphrase.

$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key

Bonus Read : PostgreSQL Performance Tuning Tips

Next, we create postgresql.crt, and sign it with using the trusted root (private key file from server). Please note, when you are prompted for certificate common name (CN), set it to database name.

$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
$ openssl x509 -req -in server.req -out /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Copy the three files created in server’s /tmp directory to client machine. Copy root.crt from server /tmp directory to client’s ~/.postgresql/ directory.

Hopefully, this article will help you enable SSL in PostgreSQL.