Permissions Best Practices

Q: Owner role for migrations?

app_owner NOLOGIN; migrator SET ROLE app_owner or inherits it. Runtime never connects as owner.

Least-privilege patterns for PostgreSQL roles, grants, and RLS in production applications.

How to Use This List

Implement A through C before first production deploy.
Treat D and E as security audit checklist items quarterly.
Pair with migration reviews - grants are schema changes.

A - Role Design

Application connects as non-owner login role. app_api never owns tables.
Migrations use separate app_migrator role. DDL rights do not live in runtime pool.
Group roles (NOLOGIN) model permission tiers. app_readers, app_writers, avoid per-developer logins on prod.
Zero application use of superuser. Break-glass only with MFA and logging.
Revoke dangerous rights from PUBLIC on hardened clusters. Especially CREATE on public schema.

B - Grants & Defaults

Grant schema USAGE before table privileges. Most common permission bug.
Grant sequences with tables for INSERT paths. USAGE, SELECT on sequences.
Configure ALTER DEFAULT PRIVILEGES for migration owner. New tables auto-grant to app roles.
Column-level grants for sensitive fields. SSN, tokens, PII not in analyst SELECT.
Document exceptions in migration SQL comments. Future reviewers need why.

C - Row-Level Security

Enable RLS on all tenant-scoped tables in shared schema. Not just the primary entity table.
Use FORCE ROW LEVEL SECURITY on multi-tenant tables. Close table-owner bypass.
Set tenant GUC with SET LOCAL per transaction. Pool-safe tenant context.
Mirror USING and WITH CHECK for write policies. Prevent cross-tenant inserts.
Index columns referenced in policy expressions. tenant_id leftmost in composites.

D - Bypass & Audit

Inventory roles with BYPASSRLS or superuser. Quarterly access review.
Minimize break-glass roles with TTL passwords. Log to SIEM.
No saved superuser in GUI tools. pgAdmin/DBeaver profiles use app_ro.
Test RLS with automated fixtures per tenant. CI catches policy regressions.
pg_dump exports respect data classification. Superuser dumps bypass RLS.

E - Operations

REASSIGN OWNED when decommissioning roles. No orphaned object ownership.
Connection limits per application role. Contain pool misconfiguration blast radius.
Per-role statement_timeout and idle_in_transaction_session_timeout. Protect against human and bug hangs.
search_path set on application roles. app, public not $user, public only.
SCRAM-SHA-256 for password roles. Rotate on offboarding events.

FAQs

Owner role for migrations?

app_owner NOLOGIN; migrator SET ROLE app_owner or inherits it. Runtime never connects as owner.

RLS without grants?

Both needed - grants allow table access; RLS filters rows within that access.

Roles Basics - login vs group roles
Default Privileges - future object grants
Security Rules - policy list

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

Permissions Best Practices

Least-privilege patterns for PostgreSQL roles, grants, and RLS in production applications.

How to Use This List

Implement A through C before first production deploy.
Treat D and E as security audit checklist items quarterly.
Pair with migration reviews - grants are schema changes.

A - Role Design

Application connects as non-owner login role. app_api never owns tables.
Migrations use separate app_migrator role. DDL rights do not live in runtime pool.
Group roles (NOLOGIN) model permission tiers. app_readers, app_writers, avoid per-developer logins on prod.
Zero application use of superuser. Break-glass only with MFA and logging.
Revoke dangerous rights from PUBLIC on hardened clusters. Especially CREATE on public schema.

B - Grants & Defaults

Grant schema USAGE before table privileges. Most common permission bug.
Grant sequences with tables for INSERT paths. USAGE, SELECT on sequences.
Configure ALTER DEFAULT PRIVILEGES for migration owner. New tables auto-grant to app roles.
Column-level grants for sensitive fields. SSN, tokens, PII not in analyst SELECT.
Document exceptions in migration SQL comments. Future reviewers need why.

C - Row-Level Security

Enable RLS on all tenant-scoped tables in shared schema. Not just the primary entity table.
Use FORCE ROW LEVEL SECURITY on multi-tenant tables. Close table-owner bypass.
Set tenant GUC with SET LOCAL per transaction. Pool-safe tenant context.
Mirror USING and WITH CHECK for write policies. Prevent cross-tenant inserts.
Index columns referenced in policy expressions. tenant_id leftmost in composites.

D - Bypass & Audit

Inventory roles with BYPASSRLS or superuser. Quarterly access review.
Minimize break-glass roles with TTL passwords. Log to SIEM.
No saved superuser in GUI tools. pgAdmin/DBeaver profiles use app_ro.
Test RLS with automated fixtures per tenant. CI catches policy regressions.
pg_dump exports respect data classification. Superuser dumps bypass RLS.

E - Operations

REASSIGN OWNED when decommissioning roles. No orphaned object ownership.
Connection limits per application role. Contain pool misconfiguration blast radius.
Per-role statement_timeout and idle_in_transaction_session_timeout. Protect against human and bug hangs.
search_path set on application roles. app, public not $user, public only.
SCRAM-SHA-256 for password roles. Rotate on offboarding events.

FAQs

Owner role for migrations?

app_owner NOLOGIN; migrator SET ROLE app_owner or inherits it. Runtime never connects as owner.

RLS without grants?

Both needed - grants allow table access; RLS filters rows within that access.

Roles Basics - login vs group roles
Default Privileges - future object grants
Security Rules - policy list

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

Permissions Best Practices

Search Documentation

Permissions Best Practices