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

Related

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