PostgreSQL SSL/TLS Configuration

Securing PostgreSQL with SSL/TLS

Encrypting database connections prevents credential sniffing and data interception. PostgreSQL supports TLS natively, and configuring it is straightforward with self-signed or CA-issued certificates.

Generating Certificates

# Generate CA key and certificate
openssl req -new -x509 -days 3650 -nodes -out ca.crt -keyout ca.key \
  -subj "/CN=PostgreSQL CA"

# Generate server key and CSR
openssl req -new -nodes -out server.csr -keyout server.key \
  -subj "/CN=db.example.com"

# Sign with CA
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key \
  -CAcreateserial -out server.crt -days 3650

PostgreSQL Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'
ssl_ca_file = '/etc/postgresql/ssl/ca.crt'

# pg_hba.conf — require SSL for all remote connections
hostssl all all 0.0.0.0/0 scram-sha-256

Enforcing Encrypted Connections

Change host to hostssl in pg_hba.conf to reject unencrypted connections. Verify with:

SELECT datname, usename, ssl, client_addr FROM pg_stat_ssl JOIN pg_stat_activity USING (pid);

Eazy SaaS Tip: For production, use certificates from a trusted CA (Let's Encrypt or your organization's internal CA). Self-signed certs work for dev/staging but require distributing the CA cert to all clients.