How to Enable SSL for PostgreSQL on VPS

What you will read?

1 Enabling SSL on PostgreSQL Server
2 Testing and Verifying SSL Configuration

Securing database connections is a critical component of maintaining the integrity and confidentiality of data. PostgreSQL supports SSL (Secure Sockets Layer) to encrypt the communication between clients and the server. By enabling SSL, you can ensure that sensitive data such as credentials and queries is transmitted securely, preventing unauthorized access or data interception.

Using SSL in PostgreSQL is especially vital for VPS setups, where connections over public networks may be more vulnerable. Proper configuration of SSL not only boosts security but also aligns your PostgreSQL server with modern security best practices and compliance standards.

Enabling SSL on PostgreSQL Server

To enable SSL on a PostgreSQL server, follow these steps to ensure secure connections between the server and its clients.

Verify SSL Support in PostgreSQL

Ensure your PostgreSQL installation supports SSL. Run the following command:

psql -c "SHOW ssl;"

If the result is off, you need to enable SSL in the configuration.

Place SSL Certificates in the Correct Directory

Ensure the SSL certificate (server.crt) and private key (server.key) are located in the PostgreSQL data directory (e.g., /var/lib/pgsql/data/).

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

Modify PostgreSQL Configuration

Edit the postgresql.conf file to enable SSL. Locate the line for SSL and update it:

ssl = on

Ensure the paths to ssl_cert_file and ssl_key_file match your certificate and key locations, if needed.

Configure Client Authentication

Edit the pg_hba.conf file to enforce SSL connections. Add the following entry:

hostssl all all 0.0.0.0/0 cert

Restart PostgreSQL Service

Restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

Test SSL Connection

Connect to PostgreSQL with SSL enabled. For example, using psql:

psql "host=your_server_ip dbname=your_db user=your_user sslmode=require"

Verify the connection is encrypted by running:

SELECT ssl_is_used();

Testing and Verifying SSL Configuration

To ensure that SSL is correctly configured and functional on your PostgreSQL server, follow these steps to test and verify the setup.

Step 1: Check Server SSL Status

Connect to your PostgreSQL server and check if SSL is enabled. Run the following query:

SHOW ssl;

If SSL is configured properly, the output should display on.

Step 2: Verify SSL Certificate Details

You can check the details of the SSL certificate being used by the server. Connect to PostgreSQL using psql and run:

SELECT version(), ssl_version(), ssl_cipher();

This query displays the PostgreSQL version, the SSL protocol version, and the cipher suite being used.

Step 3: Test SSL Connection from Client

Ensure the client uses SSL to connect. Use the following connection string with sslmode=require:

psql "host=your_server_ip dbname=your_db user=your_user sslmode=require"

If SSL is configured correctly, the connection will succeed with encryption.

Step 4: Validate Certificate on the Client

To ensure the client verifies the server’s certificate, use the sslrootcert parameter in your connection:

psql "host=your_server_ip dbname=your_db user=your_user sslmode=verify-full sslrootcert=/path/to/ca.crt"

The server’s certificate must match the CA certificate to establish the connection successfully.

Step 5: Debug SSL Connections

Enable logging for SSL-related activities in PostgreSQL by modifying the postgresql.conf file:

log_connections = on
log_statement = 'all'

Restart PostgreSQL and monitor the logs for SSL connection details.