Securing Your PostgreSQL Database with SSL: A Step-by-Step Guide

PostgreSQL is often simply referred to as "Postgres." It is known for its reliability, robustness, and performance, and is used by many organizations, from small startups to large enterprise companies. As an open-source RDBMS, we need to ensure security is at the extra level.

What is SSL?

SSL (Secure Sockets Layer), now generally known as TLS (Transport Layer Security), is a security protocol used to establish a secure and encrypted connection between a server and a client.

In the case of webservers, SSL communication is handled by your browser(client) which makes sure whatever information you pass to the webserver cannot be intercepted by your Internet service provider or any other third party.

Why SSL is needed on Postgres?

SSL ensures the data transmitted between two entities is secure and protected from interceptors, this is important while transmitting sensitive information such as credit card details, passwords, location coordinates, etc.

Most of the time database connections are from the app backend, if both database and backend are in different networks then data traverse from one network to another which needs to be encrypted.

Without SSL, sensitive information such as passwords and other confidential data could be intercepted and compromised. Additionally, SSL can also be used to authenticate the identity of the server to the client, providing an additional layer of security for the connection.

Obtain Certificates for your Server

You can obtain certificates from third-party organizations such as Norton, GoDaddy, and Let's Encrypt or generate a self-signed certificate with OpenSSL. Follow the below steps to generate self-signed certificates using OpenSSL:

• Install the OpenSSL library if not done already

# openssl on ubuntu
sudo apt update
sudo apt install openssl

• Generate private key, this is required for signing certificate, to generate CSR (Certificate Signing Request)

openssl genpkey -algorithm RSA -out server.key

• Generate a certificate signing request (CSR) using the private key and additional information about the certificate, such as the common name (CN), organization (O), and email address (emailAddress), you will get prompted for the required information

openssl req -new -key server.key -out certificate.csr

• Create a self-signed certificate using CSR file generated

openssl x509 -req -days 365 -in certificate.csr -signkey server.key -out server.crt

server.key and server.crt files are required to setup SSL for the Postgres database.

How to enable SSL on Postgres?

Once you have installed PostgreSQL server and everything is running correctly you can set up SSL.

• Obtain SSL certificates: Get server.key and server.crt files generated above and place them in the Postgres data directory.
• Configure Postgres to use SSL: Next, you will need to configure your Postgres server to use SSL. To do this, you will need to modify the postgresql.conf configuration file is typically located in the data directory of your Postgres installation. You will need to add the following lines to the file:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

• Patroni Configuration: If you are using patroni as postgres cluster manager you can use patroni commands to edit the configuration.

patronictl -c <patroni_config> edit-config

• Restart the Postgres server: After modifying the configuration file, you will need to restart the Postgres server to apply the changes. You can do this by running the following command:

sudo service postgresql restart

• Check logs: Check Postgres Logs for any failure in the startup
• Verify SSL Connection: To connect to the Postgres database using SSL, you will need to specify the sslmode parameter while connecting. You can do this using the psql command-line client or through a graphical interface like pgAdmin. For example:

psql -h hostname -p port -U username -d database -W -sslmode=require

Alternatively, you can specify the sslmode parameter in the connection string of your application or library. For example, in Python you might use something like this:

conn = psycopg2.connect(host='hostname', port='port', dbname='database', user='username', password='password', sslmode='require')

By following these steps, you should be able to enable SSL on your PostgreSQL database and ensure that your data is transmitted securely between the server and the client.

Conclusion:

SSL mode is required for having secured and encrypted data transfer between client and server. Enabling SSL on Postgres will ensure no sensitive data will be intercepted in plain format.