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:

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:

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.

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.

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:

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.

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:

select policyname, permissive, cmd, roles, qual
  from pg_policies
 where schemaname = 'public' and tablename = '<table>'
 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.

set local role authenticated;
set local request.jwt.claims = '{"sub":"<their user id>","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:

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', <user id>, '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 <temp> 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.

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

### <relation or function name> - <one line of what leaks>
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. Companion skill: postgrest-silent-failures, for the same class of bug on the client side of PostgREST.