Plan Rls Audit

---
name: plan-rls-audit
description: >
  Audit a Supabase/Postgres project for Row-Level Security and access-control gaps, then
  produce a phased remediation plan. Use when "RLS", "is my Supabase secure", "anyone
  can read my data", "lock down my tables". App-layer session/route gates →
  audit-auth-flows.
license: MIT
---

# RLS & Access-Control Audit + Remediation Plan

**Degree of freedom: MIXED** — access matrix and severity are judgment; table
inventory and policy reads run exactly. Stay **plan-only**. No SQL until approved.

## This skill vs neighbors

| Skill | Owns |
|---|---|
| **plan-rls-audit** (this) | Who can read/write which rows |
| `plan-security-audit` | OWASP umbrella (not the first RLS match) |
| `audit-db-schema` | Schema design quality |
| `plan-secrets-audit` | Where `service_role` lives |
| `audit-auth-flows` | App-layer session / route×gate / getSession vs getUser |


**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.**

## How to reason (every plan item)

1. **Propose** — enable RLS, scope a policy, rotate a client `service_role`, or fix inverted logic
2. **Risk** — anon/authed can read or write rows they should not
3. **Keep-working** — tables whose policies already enforce ownership
4. **Phase** — Stop exposure → Ownership → Correctness-perf → Adjacent gates (do not execute)

## Worked example

> **Propose:** enable RLS on `profiles`; replace `USING (true)` on `orders` SELECT with `user_id = (select auth.uid())`.
> **Risk:** anon key (public) reads every profile; every authed user reads every order.
> **Keep-working:** `orders` INSERT already checks owner.
> **Phase:** Phase 1 — Stop active exposure (Critical).
> **Direction:** policy shape only — no SQL in this pass. Client `service_role` → `plan-secrets-audit`.

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  [HIGH freedom]

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  [HIGH freedom — plan only]

1. **Inventory.**  [LOW freedom — run exactly] 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.

## Self-critique before the burndown  [LOW freedom — do not skip]

1. **evidenced-not-assumed** — every table has `relrowsecurity` + policy set from MCP/migrations/advisor; unseen tables listed
2. **plan-only** — no `ALTER TABLE`, no `CREATE POLICY`, no widening access to "verify"
3. **severity/phase justified** — RLS-off / `USING (true)` / inverted / client `service_role` = Critical even if the fix is one line
4. **right-owner** — rotate exposed `service_role` → `plan-secrets-audit`; app-layer session/route gates → `audit-auth-flows`; schema design → `audit-db-schema`
5. **no-false-safety** — client `.eq('user_id', …)` is not RLS; anon key is public; "works in the demo" is not secure

---

## Report template — `plan-rls-audit.md`

```markdown
# 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.