2026-09-22 · 6 min read · Rahul Chitturi
- sql
- postgresql
- security
PostgreSQL row-level security can make the same query return different rows for different roles and session context. A missing row, rejected insert, or unexpected visible record often comes from policy selection rather than the SQL text alone.
Owners, privileged roles, forced RLS, permissive and restrictive policies, and connection-pool state all affect the final decision.
Capture the effective identity
Record the login role, current role, table owner, applicable memberships, row-security settings, transaction boundaries, and tenant context used by policy expressions. Reproduce with the same identity as the application, not an administrative console role.
- Inspect policies from the deployed catalog.
- Separate SELECT visibility from write checks.
- Reset session context before returning pooled connections.
- Avoid building policy SQL from untrusted strings.
Evaluate both sides of a write
USING controls which existing rows a command can see or target. WITH CHECK controls whether a proposed new row is allowed. An update can therefore find a row under one expression and reject its new values under another.
Trace policy command, role targeting, permissive or restrictive combination, function volatility, and null behavior. Treat security-definer helpers as privileged code that needs a fixed search path and review.
Build a role and command matrix
Use Flashman's SQL formatter for policy DDL, diff for catalog snapshots, JSON formatter for synthetic tenant rows, and UUID generator for disposable identities.
Test owners and application roles, no matching policy, null tenant context, cross-tenant reads and writes, INSERT, UPDATE, DELETE, UPSERT, prepared statements, pooling, migrations, backups, and forced RLS.