How to Enable SSL in PostgreSQL

Last updated on September 9th, 2024 at 06:53 am

Database Security is a top concern for every database administrator. One of the simplest ways to secure your data in transit is to send & receive it over SSL connection. PostgreSQL supports SSL connection that allow users to securely connect to their databases. Once the connection is made, all subsequent data exchanges happen over SSL/TLS connection. In this article we will look at how to enable SSL in PostgreSQL database.

How to Enable SSL in PostgreSQL

We have assumed that you already have a PostgreSQL database up and running, and you also have a PostgreSQL database user. Here are the steps to enable SSL connection in PostgreSQL.

1. Generate SSL Certificates

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

2. Modify File Permissions

It is important to assign read-only permissions to your files. Update the file permission and ownership of private key file.

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

3. Generate Self-Signed Certificate

Next, we create a self-signed certificate valid for 365 days. Run the following command for this purpose.

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

In the above statement, -x509 indicates a self-signed certificate and 365 indicates validity of 365 days. 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

4. Configure PostgreSQL to use SSL Certificates

Open postgresql.conf configuration file located in data directory. Replace <version> with the version of PostgreSQL database.

$ sudo vi /var/lib/pgsql/<version>/data/postgresql.conf

Look for listen_addresses and set it to the following.

listen_addresses = '*'

Update the following parameters to values shown below.

ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
ssl_prefer_server_ciphers = on

Save and close the file. This will allow PostgreSQL to accept SSL connections.

Open pg_hba.conf file.

$ sudo vi /var/lib/pgsql/<version>/data/pg_hba.conf

Update pg_hba.conf to add the following lines to accept SSL connections from all hosts.

# 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

Save and close the file.

Restart PostgreSQL Server

$ /etc/init.d/postgresql restart

Bonus Read : How to Increase Max Connections in PostgreSQL

5. Test SSL Connection

Log into PostgreSQL database with the following command. You will be prompted for password. Enter it to make connection.

$ psql -U <username> -p 5432 -h <hostname or ip>

On successful connection, you will see the message

SSL Connection (protocol: TLS v1.3 ...

The above steps enable SSL connections only in PostgreSQL server. If you also want to enable SSL connection in PostgreSQL client, follow the next step.

6. Enable SSL in PostgreSQL client (Optional)

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.

This will enable SSL in PostgreSQL client as well.

Conclusion

In this article, we have learnt how to enable SSL in PostgreSQL Server as well as client. You can use these steps to establish secure SSL connection to your database and exchange data over SSL/TLS. SSL database connections allow you to protect your data from snooping and other attacks. You can use these steps on almost all PostgreSQL versions, just by changing the version number in step #4.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.