How to Configure SSL on PostgreSQL

Step 1: Generate SSL Certificates

The first step in configuring SSL on PostgreSQL is to generate the SSL certificates. You'll need a server certificate and a private key. Create a directory for SSL certificates:

sudo mkdir -p /etc/postgresql/ssl
sudo chmod 700 /etc/postgresql/ssl

Generate a Private Key:

sudo openssl genrsa -out /etc/postgresql/ssl/server.key 4096
sudo chmod 600 /etc/postgresql/ssl/server.key

Generate a Certificate Signing Request (CSR):

sudo openssl req -new -key /etc/postgresql/ssl/server.key -out /etc/postgresql/ssl/server.csr

During this step, you will be asked to provide information such as your country, state, organization name, and more. Make sure to enter the correct information.

Generate a Self-Signed Certificate: If you don't have a trusted Certificate Authority (CA), you can generate a self-signed certificate:

sudo openssl x509 -req -days 365 -in /etc/postgresql/ssl/server.csr -signkey /etc/postgresql/ssl/server.key -out /etc/postgresql/ssl/server.crt
sudo chmod 644 /etc/postgresql/ssl/server.crt

If you have a CA-signed certificate, you would replace the self-signed certificate with the one issued by your CA. Create the Root CA Certificate (Optional): If you're using a self-signed certificate, you can create a root CA certificate: bash

sudo openssl req -new -x509 -days 365 -key /etc/postgresql/ssl/server.key -out /etc/postgresql/ssl/root.crt

sudo chmod 644 /etc/postgresql/ssl/root.crt

Step 2: Configure PostgreSQL to Use SSL

Now that you have your SSL certificates ready, you need to configure PostgreSQL 16 to use them. Edit the postgresql.conf File: (e.g., /etc/postgresql/16/main/postgresql.conf on Ubuntu). add or modify the following lines:

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'
ssl_ca_file = '/etc/postgresql/ssl/root.crt'  # If using a root CA

Save the changes and exit the editor. Edit the pg_hba.conf File: The pg_hba.conf file controls client authentication. To require SSL for client connections, you need to modify this file. Add the following lines:

#Enforce SSL connections
hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::/0 scram-sha-256

These lines require all connections to use SSL with SCRAM-SHA-256 authentication. Restart PostgreSQL: To apply the changes, restart the PostgreSQL service:

sudo systemctl restart postgresql

Step 3: Verify SSL Configuration

To ensure that SSL is correctly configured, you can use the psql command-line tool or another PostgreSQL client. Connect to PostgreSQL using SSL: Use the following command to connect to your PostgreSQL server over SSL:

psql "sslmode=require host=localhost dbname=mydb user=myuser"

Replace mydb and myuser with your database name and username, respectively. Check the SSL Status: Once connected, run the following SQL command to verify that SSL is in use:

SHOW ssl;

The output should be on, indicating that SSL is enabled. Inspect the SSL Connection: To get more details about the SSL connection, such as the SSL protocol and cipher used, run:

SELECT * FROM pg_stat_ssl;

Conclusion Configuring SSL on PostgreSQL 16 is a critical step in securing your database connections. By encrypting the data transmitted between the server and its clients, you protect sensitive information from potential eavesdropping and man-in-the-middle attacks. Following this guide ensures that your PostgreSQL server is set up to use SSL effectively, providing a more secure environment for your data.

Remember that while self-signed certificates offer basic encryption, using a CA-signed certificate is recommended for production environments to ensure trustworthiness and compliance with industry standards. Additionally, keep your SSL certificates up to date and regularly review your PostgreSQL security settings to maintain a secure database environment.