PostgreSQL TLS Configuration: Certificates, pg_hba.conf, and Client Verification

# 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.key

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'

# 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   reject

# Verify server cert against CA (prevents MITM)
PGSSLMODE=verify-full \
PGSSLROOTCERT=/path/to/ca.crt \
psql 'host=db.internal dbname=mydb user=app'

# psycopg2
conn = psycopg2.connect(
    host='db.internal', dbname='mydb', user='app',
    sslmode='verify-full',
    sslrootcert='/path/to/ca.crt'
)

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