--- name: supabase-rls-audit description: "Audit whether row level security on a Supabase or Postgres database is actually protecting anything, rather than only looking like it does. Load this skill when reviewing or changing RLS policies, when adding a view or a SECURITY DEFINER function, when a security advisor flags a table, when someone asks whether the anon key can read something, before shipping a migration that claims to restrict access, and any time a policy was written and nobody tested it as a real signed-in user. Also load it when a permission change needs to be proven safe before it lands, when a technician or tenant may be seeing rows they should not, and when RLS is suspected in a slow query or statement timeout. Reading the migration files is not an audit and never has been - this skill is the catalog query and the impersonation test that tell you the truth." license: MIT metadata: author: Anthony Limpert organization: Clickflame version: "1.0.0" date: September 2026 abstract: A catalog-first audit procedure for Postgres row level security on Supabase. Written from production incidents where the migration said one thing and the database did another. Covers views that bypass RLS, grants that add instead of restrict, tables created with RLS off, permissive policies that OR away a restriction, sweeps that match on name instead of shape, and RLS quals that make an index unreachable. --- # Supabase RLS Audit Row level security fails in a particular way. It does not throw. The migration applies, the assertions pass, the screen looks right, and the data is readable by someone who should never have seen it. Every finding in this skill was found in a running production database whose migrations read as though they had already handled it. So the method here has one rule underneath it, and the rest is detail: **Ask the catalog what is true, then ask the database as a real user. Never grade a policy by reading the SQL that created it.** A structural fact is not a behavioral one. "The column exists, the function exists, the policy text contains the right clause" can be true on every count while the wrong people still read every row. ## Start here Run this first, on any database you are asked to audit or change. It asks which relations the `anon` role can select from, and whether anything stands in the way: ```sql select c.relname, c.relkind, c.relrowsecurity, (select count(*) from pg_policy p where p.polrelid = c.oid) as policies from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'public' and c.relkind in ('r','v','m','p') and has_table_privilege('anon', c.oid, 'SELECT'); ``` Read the result this way: | relkind | relrowsecurity | policies | verdict | |---|---|---|---| | `r` table | `true` | 0 | Safe. RLS on with no policy fails closed. | | `r` table | `true` | 1 or more | Read the policies. Go to *Permissive is not restrictive*. | | `r` table | `false` | any | **Open to the public key.** Go to *Tables born with RLS off*. | | `v` view | ignored | ignored | **Suspect.** Views do not obey RLS by default. Go to *Views bypass RLS*. | | `m` matview | ignored | ignored | Same as a view, and it cannot even take a policy. | `relrowsecurity` is `false` on views and materialized views whatever you do, so the column tells you nothing there. That is the point of the next section. The grant on its own is not the bug. A table with RLS on and zero policies is locked, and the grant is harmless. Chase the rows where nothing is standing guard. **`scripts/rls_audit.sql` runs this query and five more** - anon executable functions, every policy by table, tables carrying more than one permissive policy for a command, surviving `FOR ALL` policies, and views without `security_invoker`. It is read only and safe on production. Run it first and read section 1; the other sections explain whatever section 1 found. ## Views bypass RLS A view created with plain `create view` has no `security_invoker`, so it runs as its **owner**, and row level security on the tables underneath it does not apply. Supabase's default privileges then grant `anon` select on it. Those two facts together turn a convenience view into a public read of protected data. This was found on a live database in September 2026. A totals view defined in the very first core migration returned over two thousand rows of job, labor and parts money across every tenant, to the anon key that is printed into every page load. Confirmed with one HTTP call: `206` and a `Content-Range` naming the full count. The base table's RLS was correct the whole time. ```sql alter view public.some_view set (security_invoker = on); revoke all on public.some_view from anon; ``` The reason this keeps happening is that RLS on the base table reads like the whole defense. It is not. Any later migration that adds a view quietly re-exports everything the policy was hiding, and nothing in the migration looks wrong. **Every new view needs `security_invoker = on` unless bypassing RLS is the actual intent, and then say so in a comment on the view.** ## Grants add, they do not restrict `grant execute on function f() to authenticated;` reads like a restriction. It is not one. Postgres grants EXECUTE on a new function to `PUBLIC` by itself, and Supabase's default privileges add `anon`, `authenticated` and `service_role` on top. Grants are additive. That line adds a permission the function already had and leaves the open door standing beside it. An audit of one project found 48 of 53 SECURITY DEFINER functions callable by `anon` through the published key, across 85 migrations of careful looking `to authenticated` grants. Two thirds guarded themselves with `auth.uid()` checks. Seventeen did not, including one that returned pay statements. It is invisible in the migration files. It shows only in `pg_proc.proacl`, as an entry with an empty grantee (`=X/postgres`). Reading the SQL will never catch it. ```sql select p.proname, p.proacl from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'public' and p.prosecdef and p.prokind = 'f' and p.prorettype <> 'trigger'::regtype and has_function_privilege('anon', p.oid, 'EXECUTE'); ``` The fix is a revoke, and it takes **both lines every time**: ```sql revoke execute on function public.f() from public; revoke execute on function public.f() from anon; ``` Supabase's default privilege gives `anon` a grant of its own, which a revoke from `public` does not touch. A migration that wrote only the first line was re-checked the next day and four new helper functions were still anon callable. Exclude trigger functions. PostgREST will not expose a function returning `trigger`, so those are false positives, and one of them is usually the signup handler you do not want to break. Default privileges are unchanged by any of this, so **every new function reopens the hole**. This is a recurring check, not a one time cleanup. ## Tables born with RLS off `create table as select ...` produces a table with RLS **off**, and Supabase grants `anon` select on new public schema tables by default. Nothing warns you. Found live on one project: seven merge and cleanup working tables with no RLS, holding over a thousand customer rows, plus backup tables of names, phones and emails. Confirmed readable with the site's own published key. The real `customers` table returned zero rows, because RLS was doing its job everywhere it had been switched on. These tables are the ones nobody thinks of as tables. They are scratch, backups, merge plans, audit dumps, import staging, the thing you made at 2am to check something. **Enable RLS in the same statement that creates it, with no policy.** No policy means no signed in role can read it, and `service_role` bypasses RLS anyway, so the maintenance session that made the table can still use it. ```sql create table junk_backup_20260901 as select * from customers; alter table junk_backup_20260901 enable row level security; ``` Never leave it as a tidy up for later. Later is the window. ## Permissive is not restrictive Postgres combines PERMISSIVE policies with **OR**. A policy whose name says "scope" or "limit" or "only" but which is PERMISSIVE cannot restrict anything, because a sibling permissive policy grants the row regardless. Two shapes of this, both real. **One, the odd table out.** A comm events table had two SELECT policies and both were PERMISSIVE. The account members policy alone let any member read every row in the account, so the technician scope policy could never restrict anything. Technicians could read every conversation in the business, and the database was burning twenty seconds a query computing a restriction that had no effect. The sibling tables had it right and this one did not, because it was written by a different migration on a different day. **Two, policy sprawl.** An enquiries table carried five permissive policies, because later migrations added per command policies beside the original `ALL` policy and never dropped it: ``` own_shop ALL <- the new restriction went here own_shop_read SELECT <- and this granted the row anyway own_shop_update UPDATE own_shop_delete DELETE own_shop_insert INSERT ``` A migration added the column, added the helper function, added the clause to `own_shop`, and asserted all three existed. Every assertion passed. The behavior did not change by one row, because for a SELECT both `own_shop` and `own_shop_read` are evaluated and either one being true is enough. **So before changing who can see what, list every policy on the table:** ```sql select policyname, permissive, cmd, roles, qual from pg_policies where schemaname = 'public' and tablename = '' order by cmd, policyname; ``` Change all of them, or collapse them to one. And a policy whose job is to restrict must be written `as restrictive`, scoped `to authenticated`. ## Sweep on shape, never on name A migration split every `for all` policy into per command policies and added a `not is_viewer()` clause to the writes, driven by a loop over `pg_policies`. Its filter was `cmd = 'ALL' and policyname = 'own_shop'`. Twenty eight tables matched. One did not. Its policy had been named something else by a later migration that named things its own way. The read only role could add and delete rows on that table for weeks, on the one table the sweep existed to protect, and every test of the viewer role passed because every other table was correct. Names are written by people on different days. Shapes are written by Postgres. - Sweep on `cmd = 'ALL'` alone, and list any hand handled exclusions out loud. - **Then verify with the same query in reverse.** After the sweep, `select tablename, policyname from pg_policies where schemaname='public' and cmd='ALL'` must come back **empty**. That one line is the only proof the sweep reached everything. Every sweep over the catalog deserves its inverse as a check. Write both. ## Prove it as a real user This is the check that separates a policy that works from a policy that is merely present, and it is the only one that ever should have counted. ```sql set local role authenticated; set local request.jwt.claims = '{"sub":"","role":"authenticated"}'; select count(*) from public.enquiries; ``` Run it for one person of **every role**, before and after the change. Record both numbers. A permission change that empties a working screen is worse than the hole it closes, so the before number is not optional. Here is what that looked like on a real tightening, measured before it was applied: | | before | after | |---|---|---| | technician A | 1,159 rows | 92 | | technician B | 1,159 rows | 132 | | admin | unchanged | unchanged | Those numbers are the deliverable. "The policy now says the right thing" is not. **Through Supabase's Management API**, which runs as `postgres` and bypasses policies entirely, you need a little more scaffolding. Open a `DO` block, use `set_config('request.jwt.claims', json_build_object('sub', , 'role', 'authenticated')::text, true)` and `set_config('role', 'authenticated', true)`, then attempt the write inside `begin ... exception when others`. Collect results into a temp table and `grant all on to authenticated`, or the probe refuses itself. `select` from that temp table as the **last statement**, because the endpoint returns only the final result and swallows `raise notice`. **A delete test needs a row that is actually there.** Deleting nothing looks exactly like being blocked. This is the whole lesson in miniature. ## When RLS is the reason a query is slow An index cannot rescue a lookup that sits inside an RLS policy. Postgres must apply the RLS qual **before** a user qual that is not leakproof, so that nobody can use a cheap function to probe rows the policy would reject. A policy matched a row to a customer by normalized phone, as a plain subquery. Adding an index on the exact expression made the standalone query run in 0.118 ms and changed the policy's plan not at all, because `right(regexp_replace(...))` is not leakproof and can never become the Index Cond. The policy scanned every row first. Statement timeouts for technicians on mobile, never for an admin. **The fix is to move the lookup into a `STABLE SECURITY DEFINER` function.** Inside it there is no RLS to order around, so the index is used. Timeout became 592 ms. Two things to carry with you when you do that: 1. **Re-state by hand whatever the bypassed policy was giving you.** The customers policy was also providing the tenant fence, so the new function has to repeat `account_id in (select account_id from profiles where id = auth.uid())`. Bypassing RLS for speed silently drops every other guarantee that policy carried, and nothing tells you which ones those were. 2. **An expression index must match the policy's expression character for character.** Change one and the other silently stops being used. ## Triage the advisor, do not just read it `get_advisors(type:'security')` is worth running after any schema change, and it is not the audit. - **It reports false positives.** It flags every SECURITY DEFINER function `anon` can execute, but PostgREST refuses to expose functions returning `trigger`. On one run, three of five findings were trigger functions that answered 404 live. Check the return type before treating one as a finding. - **It misses real holes.** On that same run it did not flag the totals view as anon readable at all. It noted only that the view was SECURITY DEFINER. The actual leak came from the catalog query at the top of this skill. - **The output is large**, on the order of 160k characters. Save it to a file and parse the file rather than pulling it into the conversation. Read the ERROR level first. Then run the catalog queries anyway. ## Reporting an audit Produce findings in this shape, worst first. Each one needs the proof, not the suspicion, because an RLS finding that turns out to be wrong costs more trust than it was worth: ``` ### - Severity: critical | high | medium Exposure: which role, through which key, how many rows Proof: the catalog query result or the live call that confirmed it Cause: the migration or default that created it Fix: the SQL, with the re-check that proves it landed Blast: what breaks for legitimate users if the fix ships ``` `Blast` is not decoration. A tightening that empties a working screen gets reverted at speed and the hole comes back with it. ## The pattern under all of it Every incident in this file is the same shape. Something in the SQL reads as a restriction and is actually an addition, or a default, or a name. The migration is honest and the database disagrees with it. - A grant that adds a permission the function already had. - A view that re-exports what the policy hid. - A `create table as` that arrives with the fence down. - A permissive policy named like a restriction. - A sweep that matched a name instead of a shape. - An assertion that the parts exist, standing in for a test of the behavior. When you audit, do not read for intent. Query the catalog for state, then impersonate a user for behavior. Those two together are the audit. Everything else is reading a promise. --- *Written from production incidents on a multi-tenant Supabase application, by [Clickflame](https://clickflame.com). Companion skill: `postgrest-silent-failures`, for the same class of bug on the client side of PostgREST.*