Security Rules

PostgreSQL security rules enforce least privilege, tenant isolation, and auditable human access. Applications must not inherit DBA powers by default.

Recipe

-- Minimum production role model
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_api LOGIN PASSWORD 'vault' CONNECTION LIMIT 100;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
 
GRANT app_readers TO app_api;  -- or app_writers per service
-- NO SUPERUSER, NO BYPASSRLS on app_api
 
ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;

When to reach for this: Security review, SOC2 control mapping, onboarding checklist for new services.

Working Example

# pg_hba.conf excerpt
hostssl myapp  app_api     10.10.1.0/24  scram-sha-256
hostssl myapp  app_ro      10.10.2.0/24  scram-sha-256
host    all    all         0.0.0.0/0     reject

CREATE POLICY orders_tenant ON app.orders FOR ALL TO app_api
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
 
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO app_api;

Security rule summary:

#	Rule
1	No superuser in application connection strings
2	SCRAM-SHA-256 + TLS for remote roles
3	RLS FORCE on shared tenant tables
4	Migrator != runtime role
5	Break-glass roles audited and TTL

Deep Dive

Application Connection Rules

Connection string uses app_api or narrower per microservice
PgBouncer does not substitute for privilege separation - still use least-privilege role
search_path fixed to known schemas - prevents object hijack in public

Human Access Rules

Analysts: app_ro on replica, statement_timeout set
DBAs: jump host + individual login, no shared postgres password
BYPASSRLS count documented quarterly

Data Protection

-- Column-level for PII
GRANT SELECT (id, email) ON app.users TO app_ro;
REVOKE SELECT (ssn_hash) ON app.users FROM app_ro;

Encrypt backups and restrict restore environments
pg_dump as superuser bypasses RLS - classify dump files

Extension and Supply Chain

Extension allowlist in ADR
No CREATE EXTENSION from application role
Pin extension versions in migration SQL

Gotchas

RDS master user in app - Cloud console makes it easy. Fix: Create app roles day one; lock master to break-glass.
RLS without FORCE - Owner migrations see all tenants in psql mistakes. Fix: FORCE on tenant tables.
trust in pg_hba for VPC - "Private network" is not authentication. Fix: SCRAM or cert always.
Public schema CREATE for all - Legacy default allows any user to create objects. Fix: Revoke on hardened clusters.
SECURITY DEFINER functions as backdoor - Escalates to owner rights. Fix: Audit definer functions; minimal owner.

Alternatives

Alternative	Use When	Don't Use When
mTLS client certs	Service mesh identity	Human analysts on laptops
Vault dynamic credentials	Short-lived DB passwords	Simple internal CRUD app
Database-per-tenant	Hard isolation requirement	Thousands of tenants

FAQs

RLS enough without app checks?

Defense in depth - RLS catches app bugs; app still validates tenant for UX and audit.

Encrypt connections inside VPC?

Yes - insider threat and compliance frameworks expect TLS even east-west.

postgres user on laptop?

Local dev only with local socket peer auth - never production credentials on workstation.

Permissions Best Practices - grant patterns
Bypass RLS & Superuser - audit bypass paths
pg_hba.conf & Authentication - network auth

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Source & Internals - Official source code repository.
PostgreSQL Mailing Lists / Community - Official mailing lists for help, development, and announcements.
PostgreSQL DBA (2025 2nd Edition) - Tony CL Chan.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.

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+.

Recipe

-- Minimum production role model
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_api LOGIN PASSWORD 'vault' CONNECTION LIMIT 100;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
 
GRANT app_readers TO app_api;  -- or app_writers per service
-- NO SUPERUSER, NO BYPASSRLS on app_api
 
ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;

When to reach for this: Security review, SOC2 control mapping, onboarding checklist for new services.

Working Example

# pg_hba.conf excerpt
hostssl myapp  app_api     10.10.1.0/24  scram-sha-256
hostssl myapp  app_ro      10.10.2.0/24  scram-sha-256
host    all    all         0.0.0.0/0     reject

CREATE POLICY orders_tenant ON app.orders FOR ALL TO app_api
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
 
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO app_api;

Security rule summary:

#	Rule
1	No superuser in application connection strings
2	SCRAM-SHA-256 + TLS for remote roles
3	RLS FORCE on shared tenant tables
4	Migrator != runtime role
5	Break-glass roles audited and TTL

Deep Dive

Application Connection Rules

Connection string uses app_api or narrower per microservice
PgBouncer does not substitute for privilege separation - still use least-privilege role
search_path fixed to known schemas - prevents object hijack in public

Human Access Rules

Analysts: app_ro on replica, statement_timeout set
DBAs: jump host + individual login, no shared postgres password
BYPASSRLS count documented quarterly

Data Protection

-- Column-level for PII
GRANT SELECT (id, email) ON app.users TO app_ro;
REVOKE SELECT (ssn_hash) ON app.users FROM app_ro;

Encrypt backups and restrict restore environments
pg_dump as superuser bypasses RLS - classify dump files

Extension and Supply Chain

Extension allowlist in ADR
No CREATE EXTENSION from application role
Pin extension versions in migration SQL

Gotchas

RDS master user in app - Cloud console makes it easy. Fix: Create app roles day one; lock master to break-glass.
RLS without FORCE - Owner migrations see all tenants in psql mistakes. Fix: FORCE on tenant tables.
trust in pg_hba for VPC - "Private network" is not authentication. Fix: SCRAM or cert always.
Public schema CREATE for all - Legacy default allows any user to create objects. Fix: Revoke on hardened clusters.
SECURITY DEFINER functions as backdoor - Escalates to owner rights. Fix: Audit definer functions; minimal owner.

Alternatives

Alternative	Use When	Don't Use When
mTLS client certs	Service mesh identity	Human analysts on laptops
Vault dynamic credentials	Short-lived DB passwords	Simple internal CRUD app
Database-per-tenant	Hard isolation requirement	Thousands of tenants

FAQs

RLS enough without app checks?

Defense in depth - RLS catches app bugs; app still validates tenant for UX and audit.

Encrypt connections inside VPC?

Yes - insider threat and compliance frameworks expect TLS even east-west.

postgres user on laptop?

Local dev only with local socket peer auth - never production credentials on workstation.

Permissions Best Practices - grant patterns
Bypass RLS & Superuser - audit bypass paths
pg_hba.conf & Authentication - network auth

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Source & Internals - Official source code repository.
PostgreSQL Mailing Lists / Community - Official mailing lists for help, development, and announcements.
PostgreSQL DBA (2025 2nd Edition) - Tony CL Chan.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.

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+.

Security Rules

Search Documentation

Security Rules