SSL encryption for database connections

Setting Up SSL Encryption for Database Connections

SSL/TLS encryption of DB connections protects data in transit between application and database server. Required for compliance (PCI DSS, GDPR, SOC 2) and necessary when application and database are in different networks.

PostgreSQL SSL

Generating Self-Signed Certificate

# For production use Let's Encrypt or internal CA
openssl req -new -x509 -days 365 -nodes \
  -out /etc/ssl/certs/postgresql.crt \
  -keyout /etc/ssl/private/postgresql.key \
  -subj "/CN=db.company.internal"

# Permissions
chmod 600 /etc/ssl/private/postgresql.key
chown postgres:postgres /etc/ssl/certs/postgresql.crt /etc/ssl/private/postgresql.key

Server Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'  # for mutual TLS
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:!aNULL:!MD5'

Enforce SSL in pg_hba.conf:

# Only SSL connections from specific subnet
hostssl all all 10.0.0.0/8 scram-sha-256
# Reject non-SSL
host    all all 10.0.0.0/8 reject

Verification:

SELECT ssl, client_addr, version, cipher FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE datname = current_database();

Connection Strings with SSL

# psql
psql "host=db.company.internal sslmode=require sslcert=/etc/ssl/app.crt sslkey=/etc/ssl/app.key"

# DSN in application
DATABASE_URL=postgresql://user:[email protected]:5432/myapp?sslmode=require

Parameters sslmode:

• disable — no SSL
• require — SSL mandatory, certificate not checked
• verify-ca — SSL + CA check
• verify-full — SSL + CA check + hostname

MySQL SSL

# /etc/mysql/mysql.conf.d/mysqld.cnf
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON  # enforce SSL for all

-- Require SSL for specific user
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Or with specific certificate
ALTER USER 'app_user'@'%' REQUIRE X509;

Connection string:

mysql://user:pass@host/db?ssl-ca=/path/ca.pem&ssl-cert=/path/cert.pem&ssl-key=/path/key.pem

Client Libraries

Node.js (pg)

const { Pool } = require('pg')
const pool = new Pool({
  host: 'db.company.internal',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/etc/ssl/certs/ca.crt'),
    cert: fs.readFileSync('/etc/ssl/certs/client.crt'),
    key: fs.readFileSync('/etc/ssl/private/client.key'),
  }
})

Python (psycopg2)

import psycopg2
conn = psycopg2.connect(
    host='db.company.internal',
    sslmode='verify-full',
    sslcert='/etc/ssl/app.crt',
    sslkey='/etc/ssl/app.key',
    sslrootcert='/etc/ssl/ca.crt'
)

Certificate Rotation Without Downtime

# 1. Add new CA to old (both active)
cat old-ca.crt new-ca.crt > combined-ca.crt

# 2. Update ssl_ca_file in postgresql.conf, reload config
pg_ctl reload

# 3. Issue new client certificates
# 4. Update clients
# 5. Remove old CA from combined-ca.crt

Certificate Expiration Monitoring

# Check expiration date
openssl x509 -in /etc/ssl/certs/postgresql.crt -noout -dates

# Automatic check (in cron)
EXPIRY=$(openssl x509 -in /etc/ssl/certs/postgresql.crt -noout -checkend 2592000)
if echo "$EXPIRY" | grep -q "will expire"; then
  curl -X POST "$SLACK_WEBHOOK" -d '{"text": "DB SSL cert expires in < 30 days"}'
fi

Implementation Timeline

Setting up SSL for PostgreSQL or MySQL with certificate verification — 0.5–1 business day.