PostgreSQL supports TLS for client connections and replication. Properly configuring TLS prevents eavesdropping and man-in-the-middle attacks on your database traffic.
Generate Certificates
bash
# Self-signed CA (for internal use)
openssl req -new -x509 -days 3650 -nodes \
-out /etc/postgresql/ssl/ca.crt \
-keyout /etc/postgresql/ssl/ca.key \
-subj '/CN=PostgreSQL CA'
# Server certificate
openssl req -new -nodes \
-out /etc/postgresql/ssl/server.csr \
-keyout /etc/postgresql/ssl/server.key \
-subj '/CN=db.internal'
openssl x509 -req -days 365 \
-in /etc/postgresql/ssl/server.csr \
-CA /etc/postgresql/ssl/ca.crt \
-CAkey /etc/postgresql/ssl/ca.key \
-CAcreateserial \
-out /etc/postgresql/ssl/server.crt
chmod 600 /etc/postgresql/ssl/server.key
chown postgres:postgres /etc/postgresql/ssl/server.keypostgresql.conf
ini
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'
ssl_ciphers = 'HIGH:!aNULL'
ssl_min_protocol_version = 'TLSv1.2'pg_hba.conf: Require TLS
text
# Require SSL for all non-local connections
hostssl all all 0.0.0.0/0 scram-sha-256
# Block non-SSL connections
host all all 0.0.0.0/0 rejectClient: Verify Server Certificate
bash
# Verify server cert against CA (prevents MITM)
PGSSLMODE=verify-full \
PGSSLROOTCERT=/path/to/ca.crt \
psql 'host=db.internal dbname=mydb user=app'
python
# psycopg2
conn = psycopg2.connect(
host='db.internal', dbname='mydb', user='app',
sslmode='verify-full',
sslrootcert='/path/to/ca.crt'
)Check Active TLS on Connection
sql
SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();Key Takeaways
- Use
hostssl+host rejectin pg_hba.conf to force TLS for all clients - Set
ssl_min_protocol_version = TLSv1.2to disable weak protocols - Clients should use
sslmode=verify-fullto prevent man-in-the-middle attacks - Rotate server certificates before expiry — set a calendar reminder 30 days before expiration
JusDB Can Help
TLS certificate management and configuration audits are part of JusDB's security services. Contact us to harden your PostgreSQL TLS configuration.
