Search across all documentation pages
Production PostgreSQL security starts with a threat model: who can reach the network, how credentials are issued, whether SQL injection is possible, and what insiders can read or change. PostgreSQL 18.4 gives strong primitives (SCRAM, SSL, RLS, roles), but defaults are tuned for local dev, not internet-facing cloud.
-- Least-privilege app role (run as admin once)
CREATE ROLE app_api LOGIN PASSWORD 'rotate-me' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE orders TO app_api;
GRANT USAGE ON SCHEMA public TO app_api;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_api;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_api;# pg_hba.conf excerpt: TLS + SCRAM only for app subnets
hostssl orders app_api 10.20.0.0/16 scram-sha-256When to reach for this:
Harden a single-database SaaS API: separate roles, revoke public defaults, enforce TLS.
-- 1. Revoke dangerous defaults on new DB
REVOKE ALL ON DATABASE orders FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
-- 2. App role: DML only
CREATE ROLE app_api LOGIN PASSWORD 'use-vault-generated-secret'
NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE orders TO app_api;
GRANT USAGE ON SCHEMA public TO app_api;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_api;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_api;
-- 3. Migration role: DDL in CI only (not app runtime)
CREATE ROLE migrator LOGIN PASSWORD 'ci-only-secret' NOSUPERUSER;
GRANT CONNECT ON DATABASE orders TO migrator;
GRANT CREATE ON SCHEMA public TO migrator;
GRANT ALL ON ALL TABLES IN SCHEMA public TO migrator;
-- 4. Read-only analyst (BI)
CREATE ROLE analyst_ro LOGIN PASSWORD 'bi-secret' NOSUPERUSER;
GRANT CONNECT ON DATABASE orders TO analyst_ro;
GRANT USAGE ON SCHEMA public TO analyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_ro;# postgresql.conf (managed or self-hosted)
ssl = on
password_encryption = scram-sha-256
log_connections = on
log_disconnections = onWhat this demonstrates:
PUBLIC is not trusted by defaultapp_api, not superusermigrator used in deployment pipelines| Threat | Vector | Primary control |
|---|---|---|
| Network exposure | Open port 5432 to internet | Private subnet, security groups, hostssl only |
| Credential theft | Leaked .env, shared password | Per-service SCRAM secrets, rotation, vault |
| SQL injection | Unparameterized queries | Prepared statements, ORM parameter binding |
| Insider / over-privilege | GRANT ALL, shared superuser | Role separation, RLS for tenant data |
| Data at rest | Stolen disk snapshot | Cloud KMS / volume encryption |
pg_hba.conf + pg_authid): client proves identity; SCRAM stores salted verifier, not plaintext password.GRANT/REVOKE, RLS): after login, each statement is checked against object privileges and policies.verify-full also validates server certificate hostname.-- Confirm effective role and search_path at session start
SELECT current_user, session_user, current_setting('search_path');
-- List dangerous grants
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.role_table_grants
WHERE grantee IN ('PUBLIC', 'app_api')
ORDER BY 1, 2;postgres superuser in application config - one compromise owns the cluster. Fix: dedicated app_api role with minimal grants.PUBLIC grants on new databases - any login can read/write until revoked. Fix: REVOKE ALL ON DATABASE ... FROM PUBLIC in provisioning runbook.trust or md5 in pg_hba.conf - no encryption or weak password on wire. Fix: hostssl + scram-sha-256 for all non-local roles.$1 placeholders in every driver; never interpolate user input.search_path - object hijacking via malicious schema. Fix: set search_path per role: ALTER ROLE app_api SET search_path = app, public;| Alternative | Use When | Don't Use When |
|---|---|---|
| IAM/database auth (RDS IAM, Cloud SQL IAM) | No long-lived DB passwords in apps | Driver and network path support IAM tokens |
Certificate client auth (cert in pg_hba) | mTLS between known services | Many short-lived clients (rotation pain) |
| Row-level security | Multi-tenant shared schema | Single-tenant with simple role split is enough |
| Network policy only (no DB roles) | Temporary dev sandboxes | Any production data with compliance requirements |
No. Defaults favor local development. Production requires explicit pg_hba.conf, SCRAM, TLS, role grants, and logging.
Break-glass admins and automation that manages extensions only. Application runtimes should never use superuser.
Log log_statement = 'ddl' for schema changes, enable connection logging, and alert on unusual error rates from the app role.
No. RLS is defense in depth when the app connects with a shared DB role. App-layer checks still matter for UX and business rules.
Only install extensions from an allowlist. CREATE EXTENSION requires elevated privileges; gate via migrator role.
90 days for compliance programs, or immediately on engineer offboarding and suspected leak. Prefer IAM/token auth where available.
Rarely. Read-only analyst_ro with column masking or views excluding PII is the usual pattern.
You still own network rules, IAM, extensions, and query design. See Managed Postgres Basics.
log_connections, log_disconnections, failed auth (via connection errors), and DDL changes. Expand with pgaudit for compliance.
Yes. Do not grant superuser; revoke pg_execute_server_program from app roles on PostgreSQL 15+.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 18, 2026