Securing PostgreSQL Connections with SSL/TLS Configuration: A Complete Guide

PostgreSQL SSL/TLS configuration has two separate jobs. The first is encryption, so someone on the network cannot read credentials or query results in transit. The second is identity, so the client knows it is talking to the real database server and not a machine pretending to be it. Many setups do the first part and accidentally skip the second.

That distinction matters in real deployments. sslmode=require encrypts the connection, but by itself it does not fully verify the server hostname. sslmode=verify-full does. If your application connects across a public network, a shared corporate network, a Kubernetes overlay you do not fully control, or any environment where traffic could be intercepted, verify-full should be the target.

Understanding SSL/TLS in PostgreSQL

SSL/TLS (Secure Sockets Layer/Transport Layer Security) is a cryptographic protocol designed to provide communication security over a computer network. When applied to PostgreSQL, it encrypts the data exchanged between the database server and its clients. This prevents unauthorized parties from intercepting and reading sensitive information like credentials, financial data, or personal details.

PostgreSQL clients support several SSL modes:

sslmode=disable: Only use a plain, unencrypted connection.
sslmode=prefer: Try TLS first, then fall back to plain TCP if TLS is unavailable. This is convenient, but it can hide misconfiguration.
sslmode=require: Require TLS encryption, but do not necessarily verify the server hostname.
sslmode=verify-ca: Require TLS and verify that the certificate chains to a trusted CA.
sslmode=verify-full: Require TLS, verify the CA, and verify that the hostname matches the certificate.

On the server side, ssl = on only means PostgreSQL is capable of accepting TLS connections. It does not force every client to use TLS. Enforcement happens in pg_hba.conf with hostssl rules and by avoiding broader host rules that allow the same users and networks to connect without TLS.

Prerequisites for SSL/TLS Configuration

Before you begin configuring PostgreSQL for SSL/TLS, ensure you have the following:

OpenSSL Installed: The OpenSSL toolkit is essential for generating and managing SSL certificates. It's usually pre-installed on Linux and macOS systems. For Windows, you might need to download and install it separately.
Access to PostgreSQL Configuration Files: You'll need administrative privileges to modify postgresql.conf and pg_hba.conf files.
Understanding of Certificate Authorities (CAs): While you can create self-signed certificates for testing, production environments should ideally use certificates signed by a trusted Certificate Authority (CA) or an internal enterprise CA.

Server-Side SSL/TLS Configuration

The server-side configuration involves enabling SSL, specifying the location of SSL certificates and keys, and configuring client authentication.

1. Generating or Obtaining SSL Certificates and Keys

There are two primary ways to get SSL certificates for your PostgreSQL server:

Self-Signed Certificates (for testing/development): These are created using OpenSSL and are not trusted by default by external clients. They are useful for initial setup and internal testing.
Certificates from a Certificate Authority (CA) (for production): Obtain certificates from a trusted public CA (e.g., Let's Encrypt, DigiCert) or an internal enterprise CA. This ensures clients can verify the server's identity.

Creating Self-Signed Certificates using OpenSSL:

This is a common approach for development and internal environments. Execute the following commands on your PostgreSQL server or a machine with OpenSSL:

Create a directory for certificates: It's good practice to keep certificates organized.

sudo mkdir -p /etc/postgresql/ssl
sudo chown postgres:postgres /etc/postgresql/ssl
cd /etc/postgresql/ssl

Generate the Server Private Key: This key should be kept secret.
```
sudo openssl genrsa -out server.key 2048
```
Create a Server Certificate Signing Request (CSR): This contains information about your server.
```
sudo openssl req -new -key server.key -out server.csr
```
Use the hostname your clients will connect to, such as db01.internal.example.com. Modern clients normally check the Subject Alternative Name (SAN), so include DNS names in the certificate request when your CA process supports it.

Sign the Certificate with Your Own CA (for internal use):

Create a root CA private key and certificate (if you don't have one):

# Generate CA private key
sudo openssl genrsa -des3 -out root.key 2048
# Create CA certificate (valid for 3650 days)
sudo openssl req -new -x509 -days 3650 -key root.key -out root.crt

Sign the server CSR with the CA: This creates the trusted server certificate.

sudo openssl x509 -req -days 365 -in server.csr -CA root.crt -CAkey root.key -set_serial 01 -out server.crt

Set Permissions: Ensure the PostgreSQL user can read these files.

sudo chown postgres:postgres server.key server.crt root.crt
sudo chmod 600 server.key
sudo chmod 644 server.crt root.crt

Using Certificates from a Public/Enterprise CA:

If you obtain certificates from a CA, you will typically receive:

server.crt: Your server's public certificate.
server.key: Your server's private key.
root.crt (or similar): The CA's root certificate (and potentially intermediate certificates).

Place these files in a secure directory (e.g., /etc/postgresql/ssl/) and ensure the PostgreSQL user has read permissions.

2. Configuring `postgresql.conf`

Edit your postgresql.conf file (usually located in your PostgreSQL data directory) to enable SSL and specify the certificate files.

#------------------------------------------------------------------------------
# SSL
#------------------------------------------------------------------------------

ssl = on

# These are all in PEM format, and are ignored if server key/certificate are
# not configured.  By default, the files are expected to be in the server's
# data directory.  Alternatively, they can be specified as full paths.
ssl_cert_file = '/etc/postgresql/ssl/server.crt'     # (change filename if needed)
ssl_key_file = '/etc/postgresql/ssl/server.key'      # (change filename if needed)
ssl_ca_file = '/etc/postgresql/ssl/root.crt'         # (optional, for client cert verification)

# Optional: specify cipher list if needed
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

# Optional: enable client certificate verification
#ssl_ca_file must be set to a file containing the CA certificate(s) to trust
#ssl_crl_file = ''
#ssl_crl_dir = ''

ssl = on: Enables SSL support on the server.
ssl_cert_file: Path to the server's public certificate.
ssl_key_file: Path to the server's private key.
ssl_ca_file: Path to CA certificates PostgreSQL should trust when verifying client certificates. Clients use their own CA file, such as sslrootcert, to verify the server.

3. Configuring `pg_hba.conf` for SSL Enforcement

The pg_hba.conf file controls client authentication. You need to modify entries to enforce SSL connections.

By default, entries in pg_hba.conf look like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

To enforce SSL, change the host entries to hostssl:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
# IPv4 local connections:
hostssl all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
hostssl all             all             ::1/128                 scram-sha-256

# Example for external network access - requires SSL
hostssl all             app_user        10.20.0.0/16            scram-sha-256
hostssl all             app_user        2001:db8:20::/48        scram-sha-256

hostssl: This record type requires SSL connections. Any connection attempt without SSL will be rejected.
hostnossl: This record type explicitly forbids SSL connections.
host: Allows both SSL and non-SSL connections. If a matching host rule exists before or instead of your hostssl rule, clients may still connect without TLS.

Avoid publishing 0.0.0.0/0 access unless there is a strong reason and other controls are in place. Most production databases should accept connections only from application subnets, bastion hosts, connection poolers, or private network ranges.

4. Restarting PostgreSQL Server

After modifying postgresql.conf and pg_hba.conf, you must restart the PostgreSQL service for the changes to take effect.

# For systems using systemd (most modern Linux distributions)
sudo systemctl restart postgresql

# For systems using init.d
sudo service postgresql restart

Client-Side SSL/TLS Configuration

Clients also need to be configured to connect securely. This involves specifying connection parameters, potentially providing client certificates, and verifying the server's certificate.

1. Connection String Parameters

When connecting via psql or any PostgreSQL client library, you can specify SSL parameters in the connection string or as individual options.

Basic SSL Connection (Server Authentication Only):

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

sslmode: Controls the client's SSL behavior.
- disable: Only allow non-SSL connections.
- allow: Allow non-SSL, but prefer SSL if the server supports it.
- prefer (default): Prefer SSL, but allow non-SSL if SSL fails.
- require: Only allow SSL connections. If the server doesn't support SSL, the connection fails.
- verify-ca: Only allow SSL connections and verify that the server certificate is signed by a trusted CA. The sslrootcert parameter must be set.
- verify-full: Only allow SSL connections, verify the server certificate against a trusted CA, and verify that the server hostname matches the certificate's common name (CN) or subject alternative name (SAN).

Verifying Server Certificate (verify-ca or verify-full):

For enhanced security, clients should verify the server's identity. This requires the client to trust the CA that signed the server's certificate.

Obtain the CA Certificate: Get the root.crt file (or the appropriate CA certificate) that was used to sign the server's certificate.
Specify sslrootcert: Tell the client where to find this CA certificate.

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

This is the connection string you should test from the same host or container that runs the application. A common failure is that psql works from an administrator laptop because the CA file exists there, while the application container fails because the CA bundle was never mounted.

2. Client Certificates (Mutual Authentication)

For an even higher level of security, you can implement mutual authentication, where the server also verifies the client's identity using client certificates.

Generating Client Certificates:

Similar to server certificates, you'll need a client private key and a client certificate signed by a CA trusted by the server (often the same CA as the server certificate).

Generate Client Private Key:

openssl genrsa -des3 -out client.key 2048

Create Client CSR:
```
openssl req -new -key client.key -out client.csr
```
Provide details, ensuring the Common Name is unique for the client.

Sign Client CSR with CA:

sudo openssl x509 -req -days 365 -in client.csr -CA root.crt -CAkey root.key -set_serial <unique_serial> -out client.crt

Set Permissions:

chmod 600 client.key
chmod 644 client.crt

Configuring `pg_hba.conf` for Client Certificate Authentication:

On the server, you need to configure pg_hba.conf to accept client certificate authentication. This often uses the cert authentication method.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Require SSL and client certificate authentication for specific user/db
hostssl all             your_user       your_client_ip/32       cert map=your_cert_map

You might also need to define a certificate map file (cert_map option) if you want to map specific client certificate details (like Subject or SubjectAltName) to PostgreSQL users. Consult the PostgreSQL documentation for detailed cert authentication and certificate mapping setup.

Configuring Client to Use Certificates:

Update the client's connection string to include paths to its certificate and key:

psql "sslmode=verify-full host=your_server_hostname dbname=your_db user=your_user \
sslrootcert=/path/to/your/root.crt sslcert=/path/to/your/client.crt sslkey=/path/to/your/client.key"

Best Practices and Tips

Use verify-full sslmode: Aim to use verify-full on the client side to reduce man-in-the-middle risk.
Protect Private Keys: Ensure private keys (.key files) have strict file permissions (e.g., chmod 600) and are only readable by the PostgreSQL user on the server and the connecting user on the client.
Regularly Renew Certificates: Certificates have expiration dates. Implement a process to renew them before they expire to avoid connection disruptions.
Centralized Certificate Management: For larger deployments, consider using a certificate management system or automating certificate issuance and renewal.
Monitor Logs: Check PostgreSQL logs for any SSL-related errors during startup or connection attempts.
Documentation: Refer to the official PostgreSQL documentation for the most up-to-date parameters and advanced configuration options specific to your PostgreSQL version.

Quick Verification Checklist

After restarting PostgreSQL, check that the server is listening with TLS enabled:

SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;

Then test from a client host:

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

Inside the session, confirm the connection is encrypted:

SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

If ssl is false, your pg_hba.conf rules are not enforcing what you think they are. If verify-full fails but require works, the certificate is probably missing the right hostname, the client does not trust the CA, or the application is connecting by IP address while the certificate is issued for a DNS name.

Good PostgreSQL TLS setup is not just ssl = on. It is a chain: a certificate with the right names, private keys with strict permissions, hostssl rules that actually enforce TLS, and clients that verify the server instead of merely encrypting the socket.