Skip to main content

PostgreSQL SSL / TLS configuration

This guide will show you how to configure PostgreSQL to use TLS 1.2 and 1.3 by leveraging OpenSSL, which is the library PostgreSQL relies on for secure connections. The process involves generating SSL certificates and configuring the PostgreSQL server and client.

1. Generating SSL/TLS Certificates

The first step is to create the necessary files for the secure connection: a server key, a server certificate, and a root certificate (or Certificate Authority - CA) to sign the server certificate.

  1. Generate the Root Certificate (CA): This certificate will be used to sign the server and client certificates, establishing a chain of trust.

    # Generate a private key for the CA
openssl genpkey -algorithm RSA -out root.key

# Create the root certificate
openssl req -new -x509 -days 365 -key root.key -out root.crt -subj "/CN=PostgreSQL Root CA"

  2. Generate the Server Certificate: This certificate identifies the PostgreSQL server to the client.

    # Generate a private key for the server
openssl genpkey -algorithm RSA -out server.key

# Create a Certificate Signing Request (CSR) for the server
openssl req -new -key server.key -out server.csr -subj "/CN=your_server_hostname"

# Sign the server CSR with the CA certificate
openssl x509 -req -days 365 -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

  3. Secure the Server Key: For security, the server key should have strict permissions so that only the PostgreSQL user can read it.

    chmod 600 server.key

2. PostgreSQL Server Configuration ️

Now, you need to configure the PostgreSQL server to use these certificates and enforce TLS 1.2 or 1.3.

  1. Copy the Certificate Files: Place the server.crt, server.key, and root.crt files in a secure directory that the PostgreSQL user can access, typically in the data directory ($PGDATA).

  2. Edit postgresql.conf: Open the PostgreSQL configuration file and set the following parameters.

    # Enable SSL support
ssl = on

# Specify the paths to the certificate and key files
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

# Set the minimum SSL/TLS protocol version
# 'tls1.2' is the minimum supported version for TLS 1.2
# You can also use 'tls1.3' if your OpenSSL version supports it
ssl_min_protocol_version = 'TLSv1.2'

# Set the maximum SSL/TLS protocol version to ensure
# higher versions like 1.3 are also allowed.
ssl_max_protocol_version = 'TLSv1.3'

  3. Edit pg_hba.conf: This file controls client authentication. You need to change the connection method to hostssl to enforce SSL/TLS for specific hosts.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl all             all             0.0.0.0/0               scram-sha-256

    The hostssl method requires a secure SSL/TLS connection. This ensures that clients must use TLS 1.2 or 1.3 to connect.

  4. Restart PostgreSQL: Apply the changes by restarting the PostgreSQL service.

    # For systemd-based systems
sudo systemctl restart postgresql

3. Client Configuration

The client application (e.g., psql command-line tool, database driver) also needs to be configured to use SSL/TLS and verify the server's certificate.

  1. Set SSL Mode: The client must be configured to use an SSL mode that requires a secure connection. Recommended modes are verify-full or verify-ca.

    • verify-full: The client connects with SSL, verifies that the server's certificate is signed by a trusted CA, and verifies that the hostname in the certificate matches the hostname it is trying to connect to. This is the most secure mode.

    • verify-ca: The client connects with SSL and verifies that the server's certificate is signed by a trusted CA, but does not verify the hostname.

  2. Copy the Root Certificate: Place the root.crt file on the client machine. The client needs this file to verify the server's identity.

  3. Connecting with psql: Use the --sslmode and --sslrootcert options to specify the configuration.

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

4. Verification and Troubleshooting

Check Protocol Version: To confirm that TLS 1.2 or 1.3 is being used, you can check the PostgreSQL logs. When a client connects, the logs will often show the SSL/TLS protocol version negotiated.

  • Common Errors:

    • could not accept SSL connection: certificate verify failed: This error usually means the client can't verify the server's certificate. Check if the root.crt file is correct and if the hostname in the server certificate (CN field) matches the hostname the client is using.

    • Permission Issues: Ensure the server.key file is readable only by the PostgreSQL user. If not, the server will fail to start.

  • OpenSSL Version: Ensure your OpenSSL library is up-to-date. TLS 1.3 requires OpenSSL version 1.1.1 or higher. You can check your version with openssl version.