2026-09-22 · 8 min read
- sql
- postgresql
- security
Row-level security moves part of a data-access boundary into PostgreSQL. Once enabled, policies can restrict which existing rows a role sees or targets and which proposed rows it may create through SELECT, INSERT, UPDATE, and DELETE commands.
RLS is strongest when the role model, tenant identity source, policy expressions, connection lifecycle, and administrative bypass rules form one reviewed design. Adding a tenant filter in application queries is not a substitute for that design.
Start with roles and bypass behavior
Define separate migration, operational, and application roles with least privilege. Table owners and roles with bypass capabilities can behave differently from ordinary application identities, so policy tests must connect as the real runtime role.
- Revoke unnecessary direct table privileges.
- Decide whether owners should be subject to forced RLS.
- Keep administrative access explicit and audited.
- Review role inheritance and SET ROLE paths.
Separate row visibility from new-row checks
A USING expression controls visibility or targetability of existing rows. A WITH CHECK expression controls whether proposed rows satisfy the policy. Write both deliberately for update workflows so a caller cannot move a visible row into another tenant or protected state.
Understand how command-specific, permissive, and restrictive policies combine. Null-valued context and expressions should deny access unless the policy intentionally defines another result.
Carry tenant context safely
Bind tenant identity from authenticated server-side state, not an arbitrary client header. If a transaction-local setting supplies context, set it after checkout, keep all related statements in that transaction, and ensure pool cleanup prevents identity leakage.
Policy helper functions require the same care as other privileged database code: qualify objects, constrain search paths, review volatility and ownership, avoid recursive policy queries, and measure query plans under realistic cardinality.
Test denial as a first-class outcome
Use Flashman's SQL formatter for DDL, diff for policy and grant snapshots, JSON formatter for synthetic records, UUID generator for tenant fixtures, and case converter for naming reviews.
Test every role and command, null or missing context, cross-tenant identifiers, joins, foreign keys, views, prepared statements, UPSERT, COPY, triggers, security-definer functions, pooling, owner access, forced RLS, backups, migrations, and explain plans without production data.