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:

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

FAQs

Related

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

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