POSTGRESQL SECURITY CHECKLIST

Self-hosted Postgres has more knobs than any managed service, which is the source of both its power and its security failures. The most common failures are also the oldest: trust authentication left enabled, pg_hba.conf with overly broad host entries, application services using the postgres superuser, no SSL on internal traffic. The checklist below is what we look for first when we audit a self-hosted Postgres deployment.

Treat Critical as launch-blocking. High is week-one. Medium is the cleanup once the database is in production.

How to use this checklist

Walk it once on a representative production cluster, ticking items as you go. Run the SQL audit queries below in psql — they’ll catch most of the items mechanically. Audit replicas separately; they often inherit a config that was correct for the primary’s role but wrong for the replica’s.

Critical (fix before launch)

1. Restrict pg_hba.conf to specific hosts and methods

Why it matters. pg_hba.conf controls who can connect and how. The default file in many distributions allows host all all 0.0.0.0/0 trust (or similar) on a fresh install, which means “any user from any IP, no password required.”

How to check. Read pg_hba.conf. Any entry with trust method, 0.0.0.0/0 source, or all user/database (combined with non-local source) is suspect.

How to fix. Replace trust with scram-sha-256 (or cert for service accounts). Replace all with specific user names. Restrict source to specific IPs or CIDRs. Reload the config: pg_ctl reload.

2. Use SCRAM-SHA-256 password authentication, not md5

Why it matters. md5 password storage is fundamentally broken — md5 hashes can be brute-forced offline. scram-sha-256 (Postgres 10+) uses a proper KDF and per-user salt.

How to check. Run show password_encryption. Should be scram-sha-256. Check pg_hba.conf — auth method should be scram-sha-256 for password connections.

How to fix. Set password_encryption = scram-sha-256 in postgresql.conf. Force users to reset passwords (the new password will be stored as SCRAM). Update pg_hba.conf to require SCRAM.

3. Enable SSL with valid certificates and require it for all connections

Why it matters. Plaintext Postgres connections expose every query and result on the wire. On a shared network (cloud VPC, Kubernetes), an attacker with packet capture reads everything — including credentials sent over password auth.

How to check. show ssl. Should be on. Check pg_hba.conf — entries should use hostssl not host for non-local connections. Try connecting with sslmode=disable; should fail.

How to fix. Generate or obtain a server certificate; set ssl = on, ssl_cert_file, ssl_key_file in postgresql.conf. Use hostssl everywhere for remote connections. Reject sslmode=disable clients.

4. Create least-privilege roles per service

Why it matters. Application services running as postgres (the superuser) can drop tables, create extensions, and read every database. Most apps need CONNECT on one database and INSERT/UPDATE/SELECT on a few schemas.

How to check. Run:

select rolname, rolsuper, rolcreatedb, rolcreaterole
from pg_roles
where rolsuper = true or rolcreatedb = true or rolcreaterole = true;

Cross-reference against your services. Service accounts should not appear here.

How to fix. Create per-service roles with minimum grants. GRANT CONNECT ON DATABASE x TO svc; GRANT USAGE ON SCHEMA public TO svc; GRANT SELECT, INSERT, UPDATE ON x.y TO svc. Migrate services to the new role; drop superuser usage.

5. Disable trust authentication in production

Why it matters. A single trust line in pg_hba.conf for an unauthenticated entry — even just for the local socket — can be exploited by any process on the host. Production Postgres should never use trust.

How to check. grep -E '^\s*\w+\s+all\s+all\s+\S+\s+trust' pg_hba.conf. Any match is a bug.

How to fix. Replace with peer (Unix socket, requires matching OS user) or scram-sha-256. Reload.

6. Enable Row Level Security on multi-tenant tables

Why it matters. If multiple tenants share a database, every query needs to filter by tenant. RLS makes this enforced at the database level, so an application bug that forgets the filter doesn’t leak across tenants.

How to check. For multi-tenant tables, run select tablename from pg_tables where schemaname = 'public' and rowsecurity = false. Confirm tables that should be tenant-scoped have RLS on.

How to fix. alter table x enable row level security then add policies scoped by current_setting('app.current_tenant') or equivalent. Set the GUC at session start in the app.

High (fix in the first week)

7. Enable pgaudit for sensitive tables

pgaudit (extension) logs every read/write against tables you specify. Use it for tables holding PII or financial data. Ship logs to your SIEM.

8. Configure connection rate limits and lock_timeout

Set max_connections to what your app actually needs (lower is safer). Set lock_timeout and statement_timeout per role to prevent runaway queries from taking down the cluster.

9. Apply security patches within the support window

Postgres ships minor releases with security fixes regularly. Schedule patching; don’t wait for a CVE to force the issue.

10. Rotate role passwords on a schedule

Quarterly rotation, plus on-demand after team changes or near-leaks. Postgres supports overlapping passwords if you use cert auth alongside SCRAM.

11. Restrict superuser-only extensions

CREATE EXTENSION can introduce dangerous capabilities (plpython3u, dblink). Restrict who can create extensions; keep the installed list short.

12. Disable pg_stat_statements for shared environments unless needed

pg_stat_statements can leak query patterns and constants across users in multi-tenant environments. Restrict access via GRANTs or disable.

Medium (fix when you can)

13. Configure log_statement for security events

Log DDL and connection events. Skip log_statement = all (too noisy and contains data); use targeted logging instead.

14. Set up backup verification and restore drills

Backups exist for a reason. Test restores quarterly to confirm they work and the data is what you expected.

15. Audit installed extensions periodically

Each extension is attack surface. Keep the list minimal; pin versions; check for CVEs.

16. Restrict who can create roles

CREATEROLE is privileged; only ops/admin accounts should have it.

17. Enable connection encryption for replication

Streaming replication should use TLS. Confirm ssl is used in primary_conninfo.

18. Set up alerting on connection-count and failed-auth anomalies

Sudden spikes or repeated failed-auth attempts signal an attack or a misconfigured service.

After every config change

• Re-read pg_hba.conf end-to-end.
• Re-test connection from an unauthorized source (should fail).
• Re-run the role audit query.
• Confirm SSL is still required.

Common attack patterns we see in self-hosted Postgres

The trust-auth open port. Postgres on a cloud VM with pg_hba.conf defaulting to trust for local IPv6 — and the IPv6 interface bound to a public address. Anyone on that IP space connects without auth.

The superuser app. App service connects as postgres. SQL injection in the app gives the attacker DROP DATABASE, CREATE EXTENSION, and the ability to add new admin roles.

The plaintext replication. Streaming replication between primary and replica without TLS. Internal LAN packet capture reads every WAL record — including every column written.

The forgotten dblink. App uses dblink extension for legacy reasons. Attacker exploits an injection, uses dblink to connect to other databases the role can reach, exfiltrates data nobody knew was network-adjacent.

Related Resources

How to Secure PostgreSQL

Step-by-step guide for hardening a self-hosted Postgres — pg_hba.conf patterns, role design, SSL setup, and the RLS patterns above in long form.

Is PostgreSQL Safe?

In-depth analysis of Postgres’s defaults — what trust auth does, what the role system protects, and what we find when we audit a typical self-hosted deployment.

Automate Your Checklist

A checklist tells you what to look for. A scanner tells you what’s actually broken in the deployed app that connects to your database. VibeEval scans the app, attempts SQL injection and credential-leak vectors, and reports what got through.