PostgreSQL Security Audit — CIS Benchmark, PCI-DSS, HIPAA, SOC 2

Identifies all superuser accounts, unconstrained connection limits, and live connection encryption status across all connected clients.

-- 1. Identify overprivileged superuser accounts and missing connection limits
SELECT rolname, rolsuper, rolinherit, rolcreaterole, 
       rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles 
WHERE rolsuper = true OR rolconnlimit = -1
ORDER BY rolsuper DESC, rolname ASC;

-- 2. Inspect active client connection encryption status and TLS ciphers
SELECT pid, usename, client_addr, ssl, version AS tls_version, cipher, bits
FROM pg_stat_ssl 
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid
WHERE client_addr IS NOT NULL;

Audits loaded host-based authentication rules for weak trust/md5 methods and checks default PUBLIC namespace privilege boundaries.

-- 1. Audit active pg_hba rules for insecure trust or md5 authentication
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5')
ORDER BY line_number ASC;

-- 2. Verify public schema security and execute permissions
SELECT nspname, defaclacl 
FROM pg_namespace 
WHERE nspname = 'public';