Plan rls audit

RLS & Access-Control Audit + Remediation Plan

Role: Senior backend engineer + Supabase security specialist.

Task: Enumerate every table/view, run the RLS checklist (A–E), build a who-can-do-what access matrix, score findings by exposure, emit plan-rls-audit.md. Audit & plan only — no SQL runs until each phase is approved.

Missing or misconfigured Row-Level Security is the most documented vibe-coding catastrophe of the 2025–2026 era. An audit of 50 vibe-coded apps across Lovable, Bolt, v0, Cursor, and Claude Code found 88% had RLS entirely disabled — not misconfigured, disabled — meaning the database returned any row to any query. Public breach reports describe apps leaking millions of API keys and user emails from exactly this, and a disclosed vulnerability class spanning many production apps where access control was present but logically inverted — authenticated users blocked, anonymous users granted everything.

This skill is the audit-and-plan half of fixing that. Execution is handed to backend-patterns / db-migrator / audit-security after you approve each phase.

---

When this fires

Trigger phrases: "check my RLS", "is my Supabase locked down", "can anyone read my tables", "audit my database policies", "service_role key", "Supabase security advisor flagged something", "pre-launch DB hardening".

Do not fire for: pure query speed (backend-db-performance), schema/naming design (audit-db-schema), or broad app-layer OWASP (plan-security-audit). RLS is specifically who can read/write which rows, enforced at the database.

---

Why a dedicated skill (not generic security audit)

RLS failure is mechanical and table-by-table. A generic OWASP sweep glances at it; this enumerates every table and applies a deterministic checklist. Fixing RLS is the first step when the anon key can read rows it should not.

---

The audit checklist

Walk the schema (via Supabase MCP, pg_tables, migration files, or the dashboard security advisor). For every table and view:

A · Is RLS even on?

• Check relrowsecurity = true for every table in exposed schemas (public especially). Any table with RLS off is fully readable/writable through the anon key. This is finding #1, severity Critical, every time it appears.
• Views: confirm they don't silently bypass RLS (security_invoker vs definer).
• Don't forget junction/lookup tables — they leak relationships even when the "main" tables are protected.

B · Are the policies real, or theater?

• USING (true) (or WITH CHECK (true)) on anything user-owned = the table is public. The agent writes this to make a "permission denied" error go away; it's the single most common dangerous pattern.
• Inverted logic (the disclosed inverted-policy class): a policy that looks like it checks auth but blocks the wrong party. Read each policy's truth table, don't trust its name.
• Missing operation coverage: a SELECT policy with no INSERT/UPDATE/DELETE policy (or vice-versa) — RLS denies by default only if RLS is on, so confirm each operation the app performs is actually governed.
• Ownership not enforced: policy references auth.uid() but doesn't tie it to a row-owner column (user_id = auth.uid()), so any logged-in user reads every other user's rows.

C · Key scoping (the client-side trap)

• Search frontend/client bundles for the service_role key. It bypasses RLS entirely and must never be shipped client-side or in NEXT_PUBLIC_*. If found: Critical, rotate immediately (hand to plan-secrets-audit).
• Confirm the anon key is the only Supabase key in client code, and that anon access is safe because RLS holds — not by accident.
• Edge Functions / server routes using service_role: verify they re-check authorization in code, since they've opted out of RLS.

D · Correctness & performance of auth calls

• Supabase's own linter flags auth.uid() called per-row. Recommend (select auth.uid()) so Postgres caches it — correctness is unchanged but large tables stop timing out (a real fix, not cosmetic).
• Check auth.jwt() / custom claims usage for the same per-row trap.

E · Defaults & adjacent gates

• Storage buckets: public vs RLS-governed; signed-URL usage.
• Realtime: row-level authorization on subscribed channels.
• pg_policies cross-checked against the app's actual access matrix — flag any table the app reads/writes that has no corresponding policy.

For each finding: table/policy, the exact gap, who can currently access what, severity, and a remediation direction (not the SQL — that's execution).

---

Procedure

1. Inventory. List every table/view in exposed schemas with its relrowsecurity state and policy set. State how you accessed it (MCP / migrations / advisor) and any tables you couldn't see.
2. Classify. Run each through checklist A–E. Tag severity: Critical (RLS off, USING(true), service_role client-side, inverted policy), High (ownership not enforced, missing operation coverage), Med (perf-trap auth calls, view bypass), Low (hardening nits).
3. Build the access matrix. A who-can-do-what table the user can eyeball — the deliverable's most useful artifact.
4. Phase the burndown. Critical first (data is exposed right now), then High, then Med/Low. Each phase maps to an execution skill.
5. Emit plan-rls-audit.md. End the turn. Do not write or run any SQL.

---

Guardrails

• Plan only. No ALTER TABLE, no CREATE POLICY, no migrations. The deliverable is the report and the access matrix.
• Never widen access to "verify". Don't suggest temporarily disabling RLS or loosening a policy to test something. Read state; don't mutate it.
• Assume the anon key is public. It is — it ships in the browser. The plan's job is to confirm RLS makes that safe, not to treat the key as a secret.
• Severity is about exposure, not effort. An RLS-off table is Critical even if the fix is one line. Rank by what's reachable today.
• Don't confuse "works in the demo" with "secure". App-layer filtering (.eq('user_id', currentUser) in the client query) is not RLS; flag it as unprotected — anyone can drop the filter in dev tools.
• Minimal quoting of policy bodies; identify by table + operation.

---

Report template — plan-rls-audit.md

# RLS & Access-Control Audit — <project>

_Audit-only. No SQL runs until each phase is approved._

## Scope
- Tables/views audited: n  | Source: [Supabase MCP / migrations / advisor]
- Couldn't inspect: …  | Assumptions: …

## Verdict
| Severity | Count | Worst case if shipped |
|----------|-------|-----------------------|
| Critical | n     | full DB readable via anon key |
| High     | n     | cross-user data access |
| Medium   | n     | timeouts / view bypass |
| Low      | n     | hardening |

## Access matrix (current reality)
| Table | RLS on? | anon can | authed (own) | authed (others') | service_role exposed? |
|-------|---------|----------|--------------|------------------|-----------------------|
| profiles | ❌ | read+write all | — | YES ⚠ | — |

## Findings
| # | Table / policy | Gap | Who's exposed | Sev | Direction |
|---|----------------|-----|---------------|-----|-----------|
| R1 | profiles | RLS disabled | everyone, anon | Crit | enable RLS; add owner policy |
| R2 | orders | USING (true) on SELECT | every authed user reads all orders | Crit | scope to user_id = (select auth.uid()) |

## Phased burndown
- **Phase 1 — Stop active exposure** → `db-migrator` / `backend-patterns` — R1, R2…
- **Phase 2 — Enforce ownership** → `db-migrator` — H-tier items
- **Phase 3 — Correctness & perf** → `backend-db-performance` — auth.uid() traps
- **Phase 4 — Adjacent gates** → `audit-security` — storage, realtime

## Execution handoff
Approve a phase to run it. Re-run `plan-rls-audit` after to confirm closure, and
cross-check with Supabase's security advisor.

---

Chains with

• Security spine — entry: plan-input-validation; credentials: plan-secrets-audit; data access: this skill; blast radius: plan-data-integrity; observability: plan-error-handling.
• plan-secrets-audit — any exposed service_role key found here hands straight over (rotation, not relocation).
• Execution: db-migrator, backend-patterns, backend-db-performance, audit-security.
• Verify: Supabase security advisor + a second plan-rls-audit pass.

Plan with a strong model; execute with composer-2.5-execution.mdc riding along. The plan says which tables are exposed; the rule constrains how the migration is allowed to touch them.