rls-policy-auditor

name

rls-policy-auditor

description

Audit and (where asked) author row-level-security policies for a tenant-scoped database — inspect SELECT/INSERT/UPDATE/DELETE policies per table for missing tenant scope, deny-by-default gaps, policy recursion, unsafe SECURITY DEFINER helpers, over-broad GRANTs, service-role/superuser leakage, and frontend-derived (client-supplied) tenant scope, and ALWAYS deliver a negative-test plan proving wrong-tenant/wrong-role/missing-auth access is denied per command. Use when asked to review, write, or fix RLS/tenant-scoping policies, when RLS is enabled but unverified, or before shipping a tenant table. Do NOT use for app/API-layer isolation tests (multi-tenant-security-tester), general migration review (secure-migration-reviewer), or non-database threat modeling (threat-modeler).

RLS Policy Auditor

Purpose

Produce a database-level verdict on whether row-level security actually enforces tenant and role scope, plus the negative tests that prove it. This skill absorbs both policy authoring and negative-test design (reconciliation §3): it inspects each table's SELECT/INSERT/UPDATE/DELETE policies for the classic failure modes, writes or corrects policies where asked, and ALWAYS delivers a per-command negative-test plan. RLS enabled is not RLS enforced — a table with RLS on and no restrictive policy, or a policy that trusts a client-supplied tenant id, is a finding. The deliverable is severity-ranked findings with the offending policy quoted, remediation, and executable negative tests as SQL/session sequences.

Use When

• Use when: asked to review, audit, write, or fix RLS policies or tenant scoping at the database layer.
• Use when: RLS is enabled on tenant tables but nobody has verified the policies deny cross-tenant access.
• Use when: before shipping a new tenant-owned table, or after adding SECURITY DEFINER helpers or new GRANTs.
• Use when: converting an RLS finding into a permanent negative test.
• Do NOT use when: the isolation tests belong at the app/API layer across many surfaces — that is multi-tenant-security-tester (DB and app layers are complementary; hand off the non-DB surfaces).
• Do NOT use when: reviewing a whole migration for privilege/destructive changes beyond RLS — that is secure-migration-reviewer (route the RLS portion here).
• Do NOT use when: the ask is design-time threat enumeration — threat-modeler.
• Do NOT use when: tenant semantics are undefined — tenant-modeler first.

Inputs to Inspect

1. The schema and migrations: CREATE TABLE, ALTER TABLE … ENABLE ROW LEVEL SECURITY, every CREATE POLICY, CREATE FUNCTION, and GRANT. No policy text or schema → Stop Conditions.
2. How tenant/user context reaches the database: session settings (current_setting), JWT claims, auth.uid()/auth.jwt() equivalents, connection roles — and whether that context is server-established or client-influenced.
3. The tenant model (tenant-modeler) and authorization matrix (authorization-matrix-designer) — policies are audited against THESE definitions of tenant and role, not re-derived here.
4. Roles and their GRANTs: application role, service/service_role, anon, and any role that BYPASSRLS or owns the tables.
5. Helper functions used inside policies: their SECURITY mode, search_path, and whether they re-query the same table (recursion risk).
6. Existing RLS tests, if any, and prior incidents.

Workflow

1. Confirm real policy text exists and pin the tenant/role definitions being enforced. Undefined boundary → stop and route to tenant-modeler.
2. Inventory tables that hold tenant-owned data; for each, record whether RLS is ENABLED, whether it is FORCED (owners bypass otherwise), and which policies exist per command. A tenant table with RLS disabled or unforced is an immediate finding.
3. Audit each command separately — SELECT, INSERT, UPDATE, DELETE — using references/rls-audit-checklist.md:
   • SELECT/USING: does every row require the caller's tenant scope?
   • INSERT/WITH CHECK: can a row be written with another tenant's id, or with no tenant id? Missing WITH CHECK on INSERT is a write-side hole.
   • UPDATE: both USING (which rows) AND WITH CHECK (resulting row) — a missing WITH CHECK lets a row be moved to another tenant.
   • DELETE/USING: can a caller delete another tenant's rows?
4. Hunt the classic failure modes: missing tenant scope; deny-by-default gap (permissive policy that ORs open access); recursion (policy calls a function that selects the same table under RLS); unsafe SECURITY DEFINER (no fixed search_path, broader than needed, returns rows bypassing caller scope); over-broad GRANT (e.g. GRANT ALL … TO anon); service-role leakage (service role reachable from a client-influenced path, or BYPASSRLS role used in request handling); frontend-derived scope (tenant id taken from a client value the user controls rather than a server-established session/JWT claim).
5. Rank findings. A confirmed cross-tenant read or write via policy is CRITICAL; each needs the concrete path (which command, which caller, which client-controlled input). No demonstrable path → cap at medium and name the confirming test.
6. Author/correct policies where asked — deny-by-default, tenant scope from server context, WITH CHECK on write commands, SECURITY INVOKER helpers with fixed search_path unless a DEFINER is justified and minimal. Present as a migration for secure-migration-reviewer to gate; do not apply to a live DB from this skill.

Output Format

RLS AUDIT — <database/scope>
Enforced against: <tenant + role definitions (from tenant-modeler / authz matrix)>
Context source: <server session/JWT — or client-influenced (finding)>
Table inventory: <table — RLS enabled? forced? policies per command>
Findings (severity-ranked):
  [CRITICAL|HIGH|MEDIUM|LOW] <table.command> — <failure mode>
    Policy: <quoted policy or "absent">
    Path: <command + caller + client-controlled input → cross-tenant effect>
    Fix: <corrected policy / direction>
Authored/corrected policies (if requested): <SQL — as a migration, ungated>
Negative-test plan (per command):
  <id> — SET context = tenant A; <attempt on B> — EXPECT <0 rows / rejected>
         + positive control: <A on A's rows> — EXPECT allow
Not audited: <tables/commands + why>
Handoffs: <secure-migration-reviewer to gate the migration; multi-tenant-security-tester for app-layer>

Validation Checklist

• Every tenant table's RLS enabled/forced status recorded; unprotected tables flagged.
• SELECT, INSERT, UPDATE, DELETE audited SEPARATELY per table.
• INSERT and UPDATE WITH CHECK presence verified (write-side holes).
• Recursion, SECURITY DEFINER search_path, broad GRANTs, service-role leakage, and frontend-derived scope each explicitly checked.
• Every finding quotes the offending policy (or "absent") and states a concrete cross-tenant path; CRITICALs have a demonstrable path.
• A per-command negative-test plan exists with positive controls, including anon and service-role attempts.
• Authored policies are deny-by-default and use server-derived scope; delivered as a migration, not applied live.
• Not-audited list present.

Security Rules

• RLS enabled ≠ enforced: a table with RLS on and no restrictive policy, or a permissive policy that ORs open access, is treated as unprotected.
• Client-supplied / frontend-derived tenant scope in a policy is a finding regardless of demonstrated exploit — tenant scope must come from server-established session/JWT context.
• SECURITY DEFINER helpers without a pinned search_path are a finding (search-path hijacking); DEFINER is used only where justified and minimal.
• Service-role / BYPASSRLS credentials must not be reachable from request handling influenced by end users.
• No RLS finding is suppressed without written rationale via human-approval-boundary; every audit ships negative tests (master-prompt §6).

Gotchas

• A correct SELECT policy with a missing INSERT WITH CHECK lets tenant A create rows owned by tenant B — write-side holes hide behind read-side correctness.
• UPDATE needs BOTH USING and WITH CHECK; with only USING, a caller can update their own row to carry another tenant's id (tenant hopping).
• Policies calling a helper that selects the same table re-enter RLS and either recurse or silently return nothing under load — test, don't assume.
• FORCE ROW LEVEL SECURITY matters: without it the table owner (often the migration/app role) bypasses policies entirely.
• Postgres RLS is deny-by-default only once a policy exists; enabling RLS with zero policies denies all — but a single permissive policy can reopen everything, so read every policy, not just the first.
• A DEFINER helper that returns rows to the caller can launder around the caller's own RLS — audit what the helper returns, not just that it exists.

Stop Conditions

• No policy text, schema, or migration is available → stop; this skill does not audit RLS from a description.
• Tenant/role semantics are undefined or contested → stop; tenant-modeler / source-of-truth-reconciler first.
• A CRITICAL cross-tenant policy hole is confirmed in a live database → report the minimal reproduction immediately; containment is the human's call (human-approval-boundary) before continuing.
• Asked to APPLY authored policies to a live database → stop; deliver them as a migration for secure-migration-reviewer and human approval — this skill audits and authors text, it does not run DDL on live systems.

Supporting Files

• references/rls-audit-checklist.md — per-command audit questions, the seven failure-mode catalog with detection and fix, and the negative-test session-sequence templates.
• evals/evals.json — trigger + behavior cases.
• evals/trigger-evals.json — discrimination against multi-tenant-security-tester, secure-migration-reviewer, and the shipped tenant-isolation-reviewer (tenant security cluster).