SSL Not Enforced

Scenario

A compliance audit requires all database connections to be encrypted in transit. PostgreSQL has SSL configured (ssl = on, certificates present) but pg_stat_ssl shows 80% of active connections have ssl = false. The DBA checks pg_hba.conf and finds it uses host (not hostssl) for all rules — clients can choose to connect with or without SSL, and most don’t bother enabling it.

How to Identify

Conditions:

• pg_stat_ssl shows many connections with ssl = false
• pg_hba.conf uses host type (allows both SSL and non-SSL)
• ssl = on in postgresql.conf but not enforced at connection level
• Application connection strings don’t specify sslmode=require
• ssl_min_protocol_version not set — old TLS versions allowed

Analysis Steps

-- Check how many connections are unencrypted
SELECT
    ssl,
    count(*) AS connections,
    round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE backend_type = 'client backend'
GROUP BY ssl;

-- See encryption details for current connections
SELECT a.pid, a.usename, a.client_addr, s.ssl, s.version, s.cipher, s.bits
FROM pg_stat_ssl s
JOIN pg_stat_activity a USING (pid)
WHERE a.backend_type = 'client backend'
ORDER BY s.ssl, a.usename;

-- Check SSL configuration
SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;
SHOW ssl_min_protocol_version;

-- Check pg_hba.conf for SSL enforcement
SELECT line_num, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY line_num;
-- 'host' = allows SSL or non-SSL
-- 'hostssl' = requires SSL
-- 'hostnossl' = requires non-SSL (for internal trusted networks)

Pitfalls

• ssl = on in postgresql.conf only enables SSL — it does not require it. Clients can still connect without SSL unless hostssl is used in pg_hba.conf.
• Changing host to hostssl in pg_hba.conf only requires a reload — but application connection strings must also specify sslmode=require or sslmode=verify-full to actually use SSL.
• ssl_min_protocol_version = 'TLSv1.2' prevents downgrade attacks to older TLS versions (SSLv3, TLSv1.0, TLSv1.1).
• sslmode=verify-full in the client connection string verifies both encryption and that the server’s certificate matches the hostname — best security but requires proper certificate setup.
• Self-signed certificates work for encryption but not for certificate verification (verify-full) — use a CA-signed certificate for production compliance.

Resolution Approach

Replace host with hostssl in pg_hba.conf for all rules that should require encryption. Set ssl_min_protocol_version = 'TLSv1.2'. Update application connection strings to sslmode=require. Reload PostgreSQL configuration.