postgresql-cis-and-pgaudit-verification.sql
-- 1. Audit roles with SUPERUSER, CREATEROLE, CREATEDB, or BYPASSRLS privileges
SELECT
rolname,
rolsuper,
rolcreaterole,
rolcreatedb,
rolbypassrls,
rolcanlogin,
rolconnlimit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
AND (rolsuper OR rolcreaterole OR rolcreatedb OR rolbypassrls)
ORDER BY rolsuper DESC, rolname;
-- 2. Inspect active pg_hba.conf rules for insecure 'trust', 'password', or 'md5' methods
SELECT
line_number,
type,
database,
user_name,
address,
netmask,
auth_method,
options
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5')
OR type = 'host'; -- Flag non-SSL host rules (prefer 'hostssl')
-- 3. Detect live unencrypted client connections or weak TLS protocol versions
SELECT
a.usename,
a.application_name,
a.client_addr,
s.ssl,
s.version AS tls_version,
s.cipher,
count(*) AS connection_count
FROM pg_stat_ssl s
JOIN pg_stat_activity a ON a.pid = s.pid
WHERE a.backend_type = 'client backend'
GROUP BY a.usename, a.application_name, a.client_addr, s.ssl, s.version, s.cipher
ORDER BY s.ssl ASC, connection_count DESC;
-- 4. Verify Row-Level Security (RLS) enforcement and pgaudit settings
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
c.relrowsecurity AS rls_enabled,
c.relforcerowsecurity AS rls_forced
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname = 'public'
ORDER BY c.relrowsecurity ASC, c.relname;
SELECT name, setting
FROM pg_settings
WHERE name IN ('password_encryption', 'ssl', 'ssl_min_protocol_version', 'pgaudit.log', 'pgaudit.role');