Configuring PostgreSQL for SSL Connections

Once you have generated the SSL certificates, the next step is to configure PostgreSQL to use them for secure connections. Follow these steps:

Step 1: Place SSL Certificates in the PostgreSQL Data Directory

Copy the generated certificate and key files (server.crt and server.key) to the PostgreSQL data directory. For example:

cp server.crt server.key /var/lib/pgsql/data/
chown postgres:postgres /var/lib/pgsql/data/server.*
chmod 600 /var/lib/pgsql/data/server.*

Ensure the ownership and permissions are correct so that only the PostgreSQL process can access the files.

Step 2: Modify PostgreSQL Configuration File

Open the postgresql.conf file, typically located in the data directory, and make the following changes:

nano /var/lib/pgsql/data/postgresql.conf

Enable SSL by adding or updating the following line:

ssl = on

Step 3: Configure pg_hba.conf for SSL Connections

Edit the pg_hba.conf file to define how clients authenticate when connecting via SSL:

nano /var/lib/pgsql/data/pg_hba.conf

Add the following entry to require SSL for client connections:

hostssl all all 0.0.0.0/0 cert

This rule enforces SSL for all users connecting to the server from any IP.

Step 4: Restart PostgreSQL

Apply the changes by restarting the PostgreSQL service:

systemctl restart postgresql

Step 5: Verify SSL Configuration

You can confirm SSL is enabled by connecting to PostgreSQL and running:

SHOW ssl;