Appendix A · The master audit checklist

The RLS Field Guide · 4 min read

Every Check on your project item from the book, accumulated and reorganized for reuse. Run it after significant schema work, before launches, and on your scheduled cadence (Chapter 10).

Identity and keys (Chapter 1)

[ ] Inventory every place the anon/publishable key appears; each marks a surface that relies entirely on policies.
[ ] Inventory every service/secret key call site, each with a one-sentence justification; unjustified hits are service-role creep candidates.
[ ] Confirm service_role is the only client-relevant role with rolbypassrls (pg_roles query).
[ ] Re-verify any "empty data" bug reports as the actual requesting role before treating them as RLS bugs.
[ ] Know which tables are dashboard-created versus migration-created (ownership drives the RLS owner bypass).

Policy quality (Chapter 2)

[ ] For every table, list policies sharing a command and write down what their or permits in combination.
[ ] Flag conditions true regardless of row or caller (true, auth.uid() is not null, unconditional subqueries).
[ ] Confirm every policy names its role explicitly (to authenticated, etc.); no defaults to public.
[ ] For every update policy, state whether its with check is explicit or inherited from using.
[ ] Identify rules that must survive future policy additions and consider restrictive-policy form.

Structure and completeness (Chapter 3)

[ ] Every tenant-owned table carries the tenant id directly or an unambiguous path to it.
[ ] Grants match actual API usage: no unused privileges, nothing held by anon without intent.
[ ] No policy recursively reads its own table without a security definer helper.
[ ] Cross-tenant boundary proofs run green tonight (counts per role, one negative write attempt).

Drift patterns (Chapter 4)

[ ] Catalog scan: zero tables with RLS disabled; count and date recorded.
[ ] Last ten migrations reviewed: every create table paired with security statements; every rebuild recreated its policies and the foreign keys any cascade took with them.
[ ] Zero constant-true policies; any found traced to their origin and replaced or justified.
[ ] Service-key call sites still each carry their justification sentence.
[ ] Buckets and realtime publications inventoried against the table list.

Full audit method (Chapter 5)

[ ] Step 1 run: tables × (rls_enabled, force_rls) triaged into open / locked / protected.
[ ] Step 2 run: every policy read against the four questions; sprawl detector query returns only explainable rows.
[ ] Step 3 run: grants compared to actual usage, including grants to PUBLIC.
[ ] Step 4 run: views (security_invoker), functions (definer / search path / execute), buckets (visibility) each explained.
[ ] Findings recorded with user-facing consequences before fixes.

Proofs and CI (Chapter 6)

[ ] Read-isolation proof adapted to real schema, run for two roles across the boundary, numbers recorded.
[ ] Negative-write proof raises 42501 on the most sensitive table.
[ ] Isolation proofs live in supabase/tests/ and pass via supabase test db.
[ ] CI workflow runs structure + proofs on every pull request; a deliberately broken policy fails the build.

Tenancy model (Chapter 7)

[ ] Pattern (A/B/C, including hybrids) written down and chosen deliberately.
[ ] Pattern A: tenant column present everywhere; Pattern B: grants and default privileges audited per schema; Pattern C: key inventory and rotation owners documented.

Surfaces beyond tables (Chapter 8)

[ ] Every bucket's visibility has a one-sentence owner-approved justification.
[ ] Sensitive bucket has read + upload policies proven by cross-tenant attempts.
[ ] Realtime publications current; delete-event visibility decided explicitly.
[ ] All views either set security_invoker = true or are unreachable by clients.
[ ] Every exposed function answered: definer?, pinned search path?, who holds execute?

Performance posture (Chapter 9)

[ ] Row-independent auth calls wrapped in (select ...).
[ ] Every predicate column leads an index.
[ ] Impersonated explain (analyze) captured before/after optimization; shapes verified (InitPlan, index scans).
[ ] No exposed table anywhere with row security disabled; authorization required for any such statement.

The loop (Chapter 10)

[ ] CI gates live: structural assertions, proof suite, migration greps.
[ ] audit.sql under version control, dated runs diffed monthly.
[ ] Re-audit trigger on the calendar, owned by a role.
[ ] Incident playbook (freeze → diff → fix forward → regression proof → postmortem) in the incident process.
[ ] Appendix D embedded in the schema-change pull-request template.