supabase-app-security-audit v1.0.0A

Immutable. This exact content is served forever at /api/v1/blob/65f41838d44f83f2.

---
name: supabase-app-security-audit
description: "Audit Supabase BaaS apps: RLS write paths, RPC nulls."
version: 1.0.0
author: SeaGull Security Lab
license: MIT
tags: [security, supabase, rls, audit, postgres, storage]
metadata:
  hermes:
    tags: [security, supabase, rls, audit]
    related_skills: [security-checklist, advanced-bug-hunting]
---

# Supabase App Security Audit

Use when auditing a frontend-only app backed by Supabase (anon key in client JS, RLS + Postgres RPC as the entire security boundary). The goal is a ranked finding list + handoff fix doc another session can execute.

## When to use
- "cek security web ini" / "audit keamanannya" for a Pages/Vercel/static site + Supabase.
- Reviewing any app where the anon key is public and all logic lives in RLS policies + SQL functions.

## Core workflow (ordered)
1. **Read ALL source first** — app JS, payment/worker files, and EVERY `.sql` migration in order (v1→latest; later migrations drop/replace earlier policies, so judging by schema v1 alone yields false findings).
2. **Grab the anon key from the LIVE config file** (e.g. `/supabase-config.js`). Don't print it in chat output; extract with regex inside probe scripts.
3. **Run 3 passes**:
   - Pass 1: anon-key reads on every table + every RPC (probe behavior/error messages), storage bucket listing, live response headers, deploy-artifact exposure.
   - Pass 2: authenticated-user (log in as a test account) WRITE paths — this is where most real bugs live: PATCH/INSERT/DELETE on every table, RPCs that mutate.
   - Pass 3: re-verify candidates, storage upload/read paths, auth settings endpoint, confirm "not a bug" list.
4. **Write the handoff report** in the project (`security-audit/SECURITY-AUDIT.md`): findings ranked by severity with file:line, SQL/JS snippets, live probe evidence, fix phases (Phase 1 = SQL/config only, Phase 2 = new components), ready-to-run migration draft, verification checklist. Include a "PROVEN SAFE" section so the fixing session doesn't break working defenses. Put probe scripts next to it.

## Supabase security model facts (audit against these)
- **RLS only applies to PostgREST**. SECURITY DEFINER functions bypass it — the RPC body IS the access control.
- **Public storage bucket = world-readable** via `/storage/v1/object/public/<bucket>/<path>` regardless of any policy. Never store secrets in a public bucket; policies only gate the authenticated endpoint.
- anon key is public by design; leaking it is not itself a finding. BUT when the project may go to a public GitHub repo, still `.gitignore` the config file bundling project URL + anon key (e.g. `supabase-config.js`) and ship a `supabase-config.example.js` with dummy values — "don't open-source the links/keys" is about repo hygiene, not runtime security.
- `GET /auth/v1/settings` is public: reveals `mailer_autoconfirm`, `anonymous_users`, signup state.

## Critical bug patterns (verify these explicitly)
1. **SQL NULL-bypass**: `if col <> auth.uid() then raise` — with anon caller `auth.uid()` is NULL, `x <> NULL` = NULL, condition never fires → check bypassed. **Always probe every SECURITY DEFINER RPC with NO JWT** (anon key only, no Authorization header). Fix: `IS DISTINCT FROM` or explicit `auth.uid() is null` guard. Note `=` comparisons happen to be NULL-safe (NULL = x → NULL → not true) but don't rely on it.
2. **Client-trusted payment confirmation**: any RPC that sets an order PAID based solely on caller identity with no server-side payment verification = free products. Fix pattern: Edge Function with service_role creates AND verifies the payment (server-side donation amount, tx_id stored in DB, status checked server-to-server).
3. **Policy gaps per operation**: RLS needs a policy per command. Check SELECT, INSERT, UPDATE, DELETE each. INSERT uses WITH CHECK only; UPDATE/DELETE need USING; an UPDATE policy without WITH CHECK does not validate the NEW row.
4. **ON DELETE CASCADE chains**: a user-deletable row that cascades to orders/payments = buyers lose paid goods. Test DELETE paths.
5. **LIKE path policies**: `name like '%images/%'` is substring matching — `notimages/`, `ximages/` pass. Use `(storage.foldername(name))[N] = 'x'` instead.
6. **Client-side-only enforcement**: cooldowns, role checks, self-purchase blocks, signup-domain restrictions (e.g. gmail-only) in JS only → bypass via direct REST. Move to trigger/RPC. Signup gates go in a BEFORE INSERT trigger on `auth.users`; verify by inserting a non-matching email directly in SQL Editor (must raise) and a matching one (must succeed), then delete both test rows. See `references/remediation-runbook.md` §6.
7. **`grant execute ... to anon`** on mutating RPCs — usually wrong. Two flavors:
   - Public-facing mutating RPC granted anon/authenticated with NO auth/ownership check in the body = sabotage (e.g. `pop_stock_items(order_id, product_id, qty)` let ANY caller mark a seller's stock items "sold" → inventory DoS; delivery content stayed safe only because it needed PAID, but stock was destroyed).
   - Internal helpers (only called from other SECURITY DEFINER functions, e.g. stock-pop inside a payment-confirm RPC): revoke EXECUTE from anon/authenticated/public — internal calls still work because SECURITY DEFINER runs as owner, so revoking breaks nothing.
   - **Audit method**: don't just check suspicious functions — dump the LIVE grant matrix for the whole public schema in one shot (`json_agg` over `has_function_privilege('anon'|'authenticated', oid, 'EXECUTE')` for every `pg_proc`), read every body via `pg_get_functiondef`, and probe each anon-granted mutating RPC once with a harmless nonexistent-id call (200 = callable = vulnerable; 401/404 = gated). Expected end state: trigger/helper functions anon=f auth=f; worker token-gated RPCs anon=t auth=f; user-facing RPCs anon=f auth=t. Trigger functions also get their EXECUTE revoked (they only ever fire as triggers).
8. Auth: `mailer_autoconfirm=true` → accounts without email confirmation; signup error `user_already_exists` = enumeration; hardcoded single-admin email = SPOF.
9. Frontend: grep every `innerHTML` template for un-sanitized variables; `.src=`/`textContent` assignments are safe sinks. Check CDN scripts for SRI, missing CSP/HSTS/X-Frame-Options.
10. Deploy artifacts: probe live site for `.sql`, `.env`, backups, `.wrangler/`, audit docs. Add `.pagesignore`/`.vercelignore`. (Pages serves `index.html` fallback for unknown paths — a 200 alone is NOT proof of exposure; check response BODY.)
11. **`INSERT ... RETURNING` (and supabase-js upsert) forces SELECT-policy evaluation on the new row.** supabase-js `.upload()` upserts: it SELECTs for an existing object before INSERT. If the bucket's SELECT policy is stricter than its INSERT policy (e.g. private-delivery bucket where SELECT requires an existing PAID order via a `can_access_delivery()` helper), the SELECT on the not-yet-existing row fails → whole upload fails with `new row violates row-level security policy` even though the INSERT policy matches. Symptom signature: sibling buckets with identical INSERT path policies behave differently. Fix: SELECT policy = owner-folder OR paid-access: `using (bucket_id='X' and ((storage.foldername(name))[1] = auth.uid()::text or public.can_access_delivery(name)))`. Test both buckets with `INSERT ... RETURNING` under RLS, not bare INSERT.
12. **Auth/role-escalation must be tested by attacking, not reading.** After confirming defenses exist (triggers, RPC `is_admin()` gates, forced-role logic in `on_auth_user_created`), run an impersonation attack matrix in the SQL Editor to prove they hold — see `references/storage-rls-and-escalation-matrix.md` for the exact SQL.
13. **Client-computed money in order RPCs (price tampering).** Order/checkout RPCs that accept `unit_price`, `p_subtotal`, `p_discount_total`, `p_tax_total`, `p_grand_total`, `p_amount_paid` straight from the client and store them without reconciling against `menu_items.base_price` + modifier deltas = free products (staff or anyone can submit total=1 for a 50k item). Detect: read the RPC param list from bundle call sites; confirm: sandbox-org PoC (create own outlet + item, submit tampered totals, read back stored order — see `references/pos-blackbox-audit.md` Phase 3). Related smell: `requires_approval` / approval flags enforced only client-side. Fix: server recomputes all money from DB and RAISEs on mismatch.
    - **Overpay/refund cash-out.** When `p_amount_paid` and `p_grand_total` are both client-controlled, an attacker (or insider) can submit a tiny `grand_total` (e.g. Rp 1.000) with a huge `amount_paid` (e.g. Rp 999.999.999) and receive `change = amount_paid - grand_total` as "kembalian tunai" — money exits the register without any real deposit. In production, `pay_open_bill` returned `{"change": 999998999, "grand_total": 1000}` for a 1k-order "paid" with 999M. Detect: the pay RPC computes `change` from BOTH client-supplied values. Fix: `grand_total` must be computed server-side from DB prices BEFORE the change calculation, never from `p_grand_total`.
    - **Quantity tampering.** `p_items[*].quantity` ingested without validation → 99.999 porsi recorded as 1 line item at the price of 1. Stock disappears, reports are fiction. Fix: either validate quantity against a reasonable ceiling, or price the order by `quantity × server_price` (so the total still explodes).
    - **`unit_price=0` free order.** Item base_price 50k, create_order with `unit_price=0` → `grand_total=0` → pay with `amount_paid=0` → order completed at Rp 0. Same root cause: client controls all money. Fix: the server-side recompute covers all three sub-patterns at once.
14. **Mixed-seller checkout on single-recipient payment rails (money misrouting).** In marketplaces where each order pays out to ONE seller payment handle (Saweria username / QRIS merchant / single sub-merchant), a mixed-seller cart silently routes the whole payment to one seller while delivering products from another. Audit the order-creation RPC for a server-side single-seller check (`if v_prod.seller_id <> p_seller_id then raise ...` inside the item loop) — frontend grouping/confirmations are UX only, the RPC is the gate. Also check: stock not reserved at order creation (check-but-decrement-on-PAID = 15-min double-sell race on last item), and multi-item delivery aggregation (`string_agg` for keys/content is fine, but `min(file_path)` returns only the first file). Implementation pattern in `references/multi-seller-checkout-enforcement.md`.
15. **Non-idempotent payment RPCs (duplicate payment rows).** A single call to the pay/confirm RPC can insert TWO `paid` rows for one order when the function lacks an existence check or unique constraint (observed: `pay_open_bill` → 2× `{"amount":1,"status":"paid"}`). After exercising any payment RPC in a sandbox PoC, always `select *` from `payments` for that order and COUNT rows. Fix: `IF EXISTS(... WHERE order_id = X AND status='paid') THEN RETURN; END IF;` + partial unique index `ON payments(order_id) WHERE status='paid'`.
16. **Re-audit cycles expose partial fixes.** Owners fix headline tables and miss siblings. On re-audit: (a) re-run the FULL anon read sweep on EVERY table — observed 7 of 18 left open after a fix round; (b) re-run the whole RPC matrix as anon — previously open RPCs should now be 401 `permission denied for function`, while PGRST202 `no matches found` on an old signature means the **parameter list changed** (re-mine the fresh bundle for the new call site, don't report fixed or broken); (c) probe NEW tables the owner added between audits (`audit_logs` appeared) — they can block FK cleanup and be RLS-silent on DELETE (204 returned but rows persist — verify emptiness after every cleanup DELETE).
17. **Client-side role spoofing (admin UI hijack).** A non-admin user intercepts the `profiles` response in DevTools (fetch interceptor, proxy) and overrides `role` to `'ADMIN'`. The frontend trusts `currentUser.role` and renders the full admin dashboard with all user/seller/revenue data. Backend RPCs (`admin_set_user_suspended`, `admin_delete_user`) are `SECURITY DEFINER` + `is_admin()` and WILL reject the mutation, but the UI with sensitive data is fully exposed. Fix: never gate admin UI on `currentUser.role` from a client-side response. Add an `adminVerified` flag set only after a server-side RPC (`current_role()` SECURITY DEFINER) returns `'ADMIN'`. Gate `enterAdminRoute`, `switchView('admin')`, `renderAdminDashboard`, and the nav button click ALL on `adminVerified`, not `currentUser.role`. Reset on logout. Full implementation in `references/client-role-spoof-prevention.md`.\n18. **Exploit battery: after finding a money bug, run the full tamper matrix in the sandbox org.** One root cause (client-trusted money) spawns a family of exploits — test each variant, not just the first hit. Gevixa battery, all from the same root cause: (a) `unit_price=0` free order; (b) overpay/refund cash-out (1k order, 999M paid, `change`=999M); (c) `quantity=99999` at 1-item price; (d) `discount > subtotal` (negative grand total — DB check constraint rejected this one: `23514`); (e) negative tax (rejected, same); (f) loyalty-points overdraft (rejected: `Poin pelanggan tidak cukup` — loyalty RPC validates against customer record). Server-side validation that would catch each: recompute all money from DB prices (kills a/b/c), CHECK constraints on non-negative totals (kills d/e), loyalty balance check in RPC (already present). Note which variants the target ALREADY defends — those go in the report's PROVEN SAFE section.

## Pitfalls
- **Supabase REST returns HTTP 206 (Partial Content), not 200**, for `?limit=0&Prefer: count=exact` and often for filtered reads. Probe scripts that only accept 200 mark every table as broken. Accept 200/206. Row count lives in the `Content-Range` header (`*/N` or `0-0/N`).
- **OpenAPI endpoint may be empty under anon**: `/rest/v1/` with `Accept: application/openapi+json` returned `{definitions: {}}` on one production project. Source the schema from the Next.js bundle instead (grep `.from("...")` / `.rpc("...")` across all chunks) — see `references/pos-blackbox-audit.md` Phase 0.
- **Consent gate on invasive live PoCs** (creating real orders/records in production): stop, record evidence at logic + probe level, mark in report. Evidence from SQL logic + RPC behavior probes is sufficient. Exception: a self-created sandbox org (own outlet, own items) touched only by the test account is acceptable and upgrades suspicion to proof — but clean up every row in FK order and verify emptiness afterwards.
- **PoC scripts must include cleanup instructions** in the file header.
- **FK-safe sandbox cleanup: delete children first, then verify.** Orders → payments → menu_items → customers → payment_methods → audit_logs → outlet → org. `audit_logs` may reject DELETE with 204-but-no-op (rows persist) and block outlet/org deletion via FK (`23503`); `profiles.organization_id` may reject PATCH with `P0001 Perubahan organization_id tidak diizinkan`. Residual rows (org, outlet, audit_logs, test accounts) that need admin access MUST be listed with IDs in the final report so the owner can finish cleanup.
- Probe scripts: build curl args as lists via subprocess (shell-quoting JWTs with `+`/`=` is fragile); keep the key-extraction regex as plain ASCII in a known-good file (ellipses/unicode corruption from edits breaks `group(1)` calls).
- Distinguish identical error messages to localize the auth gate: "Order tidak ditemukan" before any auth check = no gate; "Silakan login" = gate present.
- Test accounts: register one probe account early and reuse across passes; note it in the report for cleanup.
- **Secret redaction corruption**: when writing real API keys via patch/terminal/chat context, the harness redaction pipeline can silently replace the value with a 15-char ellipsis placeholder — the file looks edited but the key is garbage, and production breaks with 401s that seem inexplicable. Never route a real key through assistant context. Pattern that works: extract the key from the dashboard DOM inside `browser_exec` and write the config file directly there, or read it from disk inside a local deploy script (`_deploy_fix.py` style: script reads key from file → sets worker secret → deploys). Always verify by length/hash, never by eyeballing the value. Same corruption hits tokens flowing through tool stdout: a JWT captured from a response and re-read via assistant context came back as a 13-char placeholder ("Expected 3 parts in JWT; got 4"). Fix: write the response straight to a file in the same call (`curl -o resp.json` / Python `urlopen` → `open(...).write(tok)`), read the file back for use, never print the token.
- SQL Editor `Run` button and automation quirks (Monaco index, index-based button click, only-last-statement results): see `references/storage-rls-and-escalation-matrix.md` §3.

## Support files
- `references/supabase-baas-audit-notes.md` — detailed patterns, probe recipes, and fix templates (payment-verification Edge Function design, migration snippets, `_headers` CSP template).
- `references/pos-blackbox-audit.md` — full black-box recipe without source/dashboard access: Next.js bundle mining for tables+RPC signatures, anon read sweep, RPC authorization probe matrix with response classification, sandbox-org price-tamper PoC with FK-safe cleanup, re-audit-after-patching cycle, and owner-facing report shape.
- `references/pos-exploit-battery.md` — the tamper matrix and its results from the GevixaServe audit (gevixa.my.id): free-order, overpay/refund cash-out, quantity tampering, loyalty abuse attempts, double-payment, plus which defenses HELD (loyalty idempotency, idempotency-key reuse, DB check constraints). Use as the checklist when a POS money RPC accepts client-side totals.
- `references/storage-rls-and-escalation-matrix.md` — `INSERT ... RETURNING` SELECT-policy denial diagnosis/fix, role-escalation attack matrix SQL, SQL Editor browser automation quirks.
- `references/client-role-spoof-prevention.md` — admin-UI hijack via DevTools fetch interceptor (`role=ADMIN` spoof) and the `adminVerified` + RPC verification fix pattern.
- `references/multi-seller-checkout-enforcement.md` — single-seller-per-order enforcement for single-recipient payment rails (4-layer pattern + RPC gate + stock-race/file-aggregation checks), plus offline audit via local `funcdefs*.json` dumps when the Supabase dashboard SPA goes blank.
- `references/nextjs-blackbox-recon.md` — mining a Next.js production bundle for Supabase tables/RPC signatures when there is no source or dashboard access.
- `references/remediation-runbook.md` — fix-phase templates for findings (payment-verification edge function, migration snippets, signup-trigger verification).
- `scripts/supabase_rest_dump.py` — GET-only probe CLI for black-box audits: list/count/read/dump/search against Supabase REST with 200/206 handling and Content-Range row counts. Sidecar-configures via `sb_url.txt` + `anon_key.txt` + `tables.txt` (or env vars); keeps keys out of chat context.