# Supabase RLS Auditor > **Purpose:** Audit a Supabase database migration file or SQL containing Row Level Security (RLS) policies for correctness and security. Catches the mistakes that silently either expose all data or lock out all users. Every Supabase table touched by users should pass this audit before going to production. --- ## Invocation ``` /rls-audit ``` --- ## The 8-Point RLS Checklist ### Check 1: RLS Enabled on Every Table Every table that stores user data must have: ```sql ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY; ``` **Failure:** Table has no RLS — all authenticated users can read/write all rows. --- ### Check 2: No Unrestricted `USING (true)` on Sensitive Tables ```sql -- ❌ BAD — exposes ALL rows to ALL authenticated users CREATE POLICY "all_read" ON users FOR SELECT USING (true); -- ✅ GOOD — only user's own rows CREATE POLICY "own_rows" ON users FOR SELECT USING (auth.uid() = user_id); ``` **Exception:** Public tables (e.g., `skills` with `status = 'active'`) may use `USING (true)` for SELECT only. --- ### Check 3: INSERT Policies Have `WITH CHECK` ```sql -- ❌ BAD — INSERT policy with no WITH CHECK CREATE POLICY "insert_tip" ON tips FOR INSERT TO authenticated USING (true); -- ✅ GOOD CREATE POLICY "insert_tip" ON tips FOR INSERT TO authenticated WITH CHECK (auth.uid() = tipper_id); ``` --- ### Check 4: No Anon Role Access to Private Tables Policies with `TO anon` on tables containing user data are dangerous. Flag any policy where: - Role is `anon` or omitted (defaults to all roles including anon) - Table contains PII (email, phone, address) or financial data --- ### Check 5: Service Role Bypasses Are Documented ```sql -- This bypasses ALL RLS — it should be intentional and documented CREATE POLICY "service_role_all" ON [table] USING (auth.role() = 'service_role'); ``` Flag: every service-role bypass needs a comment explaining WHY it's needed. --- ### Check 6: auth.uid() Typo Check Common typo that silently evaluates to `NULL = user_id` (always false — locks out all users): ```sql -- ❌ BAD TYPOS USING (auth.uid = user_id) -- missing () → NULL USING (auth.uid() = userId) -- camelCase column name USING (auth_uid() = user_id) -- underscore instead of dot -- ✅ CORRECT USING (auth.uid() = user_id) ``` --- ### Check 7: Separate Policies per Operation ```sql -- ❌ BAD — FOR ALL lets users UPDATE and DELETE other users' data if USING passes CREATE POLICY "user_access" ON orders FOR ALL USING (auth.uid() = user_id); -- ✅ GOOD — explicit per-operation policies CREATE POLICY "select_own" ON orders FOR SELECT USING (auth.uid() = user_id); CREATE POLICY "insert_own" ON orders FOR INSERT WITH CHECK (auth.uid() = user_id); CREATE POLICY "update_own" ON orders FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id); ``` --- ### Check 8: Recursive Policy Risk Policies that query the same table they protect can cause infinite recursion. Flag any policy that contains a subquery on the same table. --- ## Output Format ``` ## RLS Audit: [migration file name] ### Tables Audited: N --- #### [table_name] | Check | Status | Notes | |-------|--------|-------| | RLS enabled | ✅ / ❌ FAIL | | | No unrestricted USING | ✅ / ⚠️ REVIEW | | | INSERT has WITH CHECK | ✅ / ❌ FAIL | | | No anon leaks | ✅ / ❌ FAIL | | | Service bypasses documented | ✅ / ❌ FAIL | | | auth.uid() correct | ✅ / ❌ FAIL | | | Per-operation policies | ✅ / ⚠️ REVIEW | | | No recursion risk | ✅ / ❌ FAIL | | **[table_name] verdict:** PASS / FAIL / NEEDS REVIEW --- ### Overall Audit Result: PASS / FAIL Critical issues (must fix before production): [N] Warnings (review before production): [N] ### Fix SQL [SQL to fix any FAIL items] ``` --- ## Rules - Check 1 (RLS enabled) failure is always CRITICAL — no exceptions - Check 2 (USING true) on private tables is always CRITICAL - Flag any policy targeting `FOR ALL` — it's almost always wrong - If a table has no policies at all (but RLS is enabled): that's a lockout — all inserts/selects will fail silently ## Playground
Supabase RLS AuditorExample · SkillSlap
Input: RLS policy
-- skills table
CREATE POLICY "read_public"
  ON skills FOR SELECT
  USING (true);

CREATE POLICY "author_update"
  ON skills FOR UPDATE
  USING (author_id = auth.uid());
Output: Audit findings
🔴 read_public: USING(true)
Returns ALL rows including unlisted
and draft skills to any anon user.
Fix: USING(visibility = 'public')

✅ author_update: correct
Only skill author can UPDATE. Good.

🟡 Missing: DELETE policy
Explicit policy documents intent:
CREATE POLICY "author_delete"
  ON skills FOR DELETE
  USING (author_id = auth.uid());

🟡 Missing: INSERT policy for authors