PostgreSQL Row-Level Security Policy Design Prompt
Produces a reviewed Row-Level Security design for multi-tenant isolation, with CREATE POLICY statements separating USING and WITH CHECK, a tenant-context strategy via session variables, performance analysis, and a test matrix proving tenants cannot see each other's rows.
- Target user
- DBAs and backend engineers building multi-tenant PostgreSQL schemas that must enforce tenant isolation in the database rather than trusting application code alone
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL DBA who designs Row-Level Security for multi-tenant systems where a single leaked row across tenants is a serious incident. You reason from first principles: RLS is a per-row visibility and write filter that the planner injects into every query, so the security guarantee is only as strong as the weakest combination of policy logic, the role it applies to, and how tenant identity is established for the session. You never assume the application will always send the right tenant id; you design so that even a buggy query cannot return another tenant's rows. You treat read visibility (USING) and write authorization (WITH CHECK) as distinct concerns that must both be reasoned about.
I will paste:
- [SCHEMA] — the tables to protect, their tenant-key column(s), and foreign-key relationships
- [TENANT_CONTEXT] — how the tenant identity is known at runtime (connection per tenant, shared pool with set session variable, JWT claim, separate role per tenant)
- [ROLES] — the database roles/users the application connects as, including any superuser, table owner, or BYPASSRLS roles
- [ACCESS_RULES] — what each role may read, insert, update, and delete; any cross-tenant admin or reporting needs
- [CONNECTION_LAYER] — pooler in use (PgBouncer mode), whether connections are reused across tenants, and how/where tenant context would be set
- [PERFORMANCE_PROFILE] — table sizes, hot query patterns, and existing indexes on the tenant key
- [VERSION] — PostgreSQL version and any ORM/framework that issues the queries
Then work through the following steps:
1. **Choose the tenant-context mechanism** — Evaluate the options in [TENANT_CONTEXT] and recommend one: a session GUC read via `current_setting('app.tenant_id', true)`, a role-per-tenant model, or a connection-per-tenant model. Spell out the trust boundary: who is allowed to set the tenant value and how a forged or empty value is handled.
2. **Enable and force RLS correctly** — Specify `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` and, critically, `FORCE ROW LEVEL SECURITY` so that the table owner is not silently exempt. Identify every role that bypasses RLS (superusers and `BYPASSRLS` roles) and confirm the application does not connect as one.
3. **Author the policies with USING and WITH CHECK separated** — Write `CREATE POLICY` statements per command (SELECT/INSERT/UPDATE/DELETE or a combined policy where appropriate). Use USING to filter which rows are visible/affectable and WITH CHECK to prevent writing or moving a row into another tenant. Explain why omitting WITH CHECK on UPDATE/INSERT can let a tenant smuggle rows across the boundary.
4. **Handle defaults and null context safely** — Ensure that a session with no tenant set (NULL `current_setting`) sees zero rows rather than all rows, and that `INSERT` defaults populate the tenant key from the context so applications cannot accidentally write the wrong tenant.
5. **Analyze performance** — Show how the policy predicate is added to every query, why the tenant-key column must be indexed (often as the leading column of composite indexes), and how to confirm via `EXPLAIN` that policies are not forcing sequential scans. Note planning-time effects of complex policy expressions and functions marked appropriately (e.g., STABLE).
6. **Design cross-tenant/admin access deliberately** — Define how reporting or admin roles get broader access without weakening per-tenant isolation: a dedicated BYPASSRLS role used only by audited jobs, or a permissive admin policy, never by loosening the tenant policy itself.
7. **Build the isolation test matrix** — Provide test cases that set the context to tenant A, attempt to read/update/delete tenant B's rows, attempt an INSERT with a foreign tenant id, and run with no context set, asserting the expected (usually empty or rejected) result each time.
Output format: Return (a) a fenced SQL block with the ENABLE/FORCE statements and all CREATE POLICY statements, commented to show which protect reads versus writes, (b) a policy summary table with columns Table | Command | USING predicate | WITH CHECK predicate | Notes, and (c) an isolation test matrix with columns Test | Session Context | Action | Expected Result.
Guardrails: Present this as a design for human review, not statements to auto-apply. Test every policy on a replica or staging database with realistic multi-tenant data before production, since a wrong predicate can either expose cross-tenant rows or silently hide a tenant's own data. Always verify with FORCE ROW LEVEL SECURITY that the table owner and pooled connections are actually subject to the policies, confirm the application role lacks BYPASSRLS, and have a security-aware human approve the policy set and the test results before rollout. Take a backup before enabling RLS on populated tables in case access must be rolled back.
Why this prompt works
Row-Level Security is deceptively easy to enable and dangerously easy to get subtly wrong, because a policy that looks correct can still leak rows under conditions the author did not consider: the table owner being exempt, an empty tenant context defaulting to all-rows, or an UPDATE that lets a tenant rewrite a row’s tenant key. This prompt is structured to surface exactly those traps. By forcing the model to reason about the trust boundary for tenant identity first, then to insist on FORCE ROW LEVEL SECURITY and an audit of bypass roles, it addresses the two most common real-world RLS failures before writing a single policy. That ordering reflects how a security-minded DBA thinks: the question is not “does this policy match the right rows” but “can any path through the system reach a row it should not.”
The separation of USING and WITH CHECK is the conceptual core of correct RLS, and the prompt treats it as a required step rather than an afterthought. USING governs which rows are visible and affectable, while WITH CHECK governs which rows may be written or moved into the tenant’s scope, and conflating them is how cross-tenant smuggling bugs appear in production. By requiring the model to articulate both predicates per command and to handle the NULL-context case explicitly so an unset session sees nothing, the prompt produces policies that fail closed rather than open, which is the only acceptable default for tenant isolation.
The prompt also refuses to ignore the operational consequences of RLS. Because the planner injects the policy predicate into every query, isolation and performance are the same problem: a tenant key that is not properly indexed turns RLS into a cluster-wide sequential-scan generator. By requiring EXPLAIN verification, deliberate handling of admin and reporting access through audited BYPASSRLS roles, and a concrete isolation test matrix run against staging before any production change, the prompt yields a design that is both provably isolating and performant, with the human review and backup discipline that a security control of this consequence demands.