07
Extension Security
| Extension | Purpose | Risk |
|---|
| pgaudit | Audit trail | SAFE |
| pgcrypto | Cryptographic functions | SAFE |
| pg_stat_statements | Query statistics | SAFE |
| postgres_fdw / dblink | Remote connections — credential leakage | CAUTION |
| adminpack / file_fdw | Server file system access | HIGH |
| plpython3u / plperlu | Untrusted language — runs OS commands | HIGH |
shared_preload_libraries Whitelist
# Approved libraries only — restart required
shared_preload_libraries =
'pg_stat_statements,pgaudit,passwordcheck'
# Current value: SHOW shared_preload_libraries;
Installation & Function Access
-- Privilege control: install extension into a dedicated schema
CREATE SCHEMA ext AUTHORIZATION postgres;
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA ext;
-- Functions are granted to PUBLIC by default!
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA ext FROM PUBLIC;
GRANT EXECUTE ON FUNCTION ext.pgp_sym_decrypt(bytea, text)
TO app_secure;
ALTER DEFAULT PRIVILEGES
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
-- Installed extension inventory
SELECT e.extname, e.extversion, n.nspname AS schema,
r.rolname AS owner
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
JOIN pg_roles r ON r.oid = e.extowner;
SECURITY DEFINER Functions
CREATE FUNCTION app.rotate_key() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp -- mandatory!
AS $$ ... $$;
Trusted extensions (PG13+): Extensions marked trusted = true can be installed by non-superusers with CREATE privilege on the database. For tighter control, enforce a whitelist with pgextwlist.
⚠ Warning: Untrusted languages (*u) and COPY ... PROGRAM let a superuser run OS-level commands — which is why superuser = the OS user postgres.
08
Security Checklist
Go-Live Readiness
| Check Item | Priority |
|---|
| pg_hba.conf — trust removedpg_hba_file_rules WHERE auth_method='trust' | Critical |
| scram-sha-256 active, no md5 hashes leftSHOW password_encryption; | Critical |
| SSL/TLS enabled, TLSv1.2+ enforcedSHOW ssl; · pg_stat_ssl | Critical |
| Superuser count ≤ 2SELECT count(*) FROM pg_roles WHERE rolsuper; | Critical |
| public schema restricted\dn+ public | Important |
| pgaudit installed and activeSHOW pgaudit.log; | Important |
| Password policy enforced (passwordcheck + VALID UNTIL)SHOW shared_preload_libraries; | Important |
| Backup encryption activegpg / repo1-cipher-type | Important |
| RLS enabled on critical tablespg_class WHERE relrowsecurity | Recommended |
| log_connections onSHOW log_connections; | Recommended |
Criticalfix now Importantthis sprint Recommendedplan
One-Query Security Audit
-- dbaguide.com · security quick audit
SELECT name, setting,
CASE
WHEN name = 'ssl' AND setting = 'on' THEN '✓'
WHEN name = 'password_encryption'
AND setting = 'scram-sha-256' THEN '✓'
WHEN name IN ('log_connections',
'log_disconnections')
AND setting = 'on' THEN '✓'
WHEN name = 'shared_preload_libraries'
AND setting LIKE '%pgaudit%' THEN '✓'
WHEN name = 'listen_addresses'
AND setting <> '*' THEN '✓'
ELSE '✗ CHECK'
END AS status
FROM pg_settings
WHERE name IN ('ssl', 'password_encryption',
'log_connections', 'log_disconnections',
'shared_preload_libraries', 'listen_addresses');
-- Critical tables without RLS
SELECT relname FROM pg_class
WHERE relkind = 'r' AND NOT relrowsecurity
AND relnamespace = 'app'::regnamespace;
✓ Best Practice: Run these checks weekly via CI/CD or cron and ship results to your monitoring system. Re-run the full list after every major upgrade.