Test Your Supabase RLS Policies Locally: A Free SQL Harness

Row Level Security is the thing standing between your Supabase tables and the whole internet. Your anon key ships in the browser bundle on purpose — that's fine, that's by design, it isn't a secret — but it means any row your policies fail to lock down is a row a stranger can read with curl. So the real question isn't "do I have RLS on?" It's "do my policies actually do what I think they do?"

The good news: you can answer that in a local Postgres, deterministically, without touching production or hitting a rate limit. The trick is that Supabase's auth.uid() and the anon / authenticated roles are just Postgres primitives you can reproduce. This is a walkthrough of a small harness that impersonates anon and any logged-in user right in psql.

Everything here is packaged as a ready-to-run repo: github.com/cekuu35/supabase-rls-leak-demo. Clone it if you'd rather run than copy-paste.

How Supabase auth maps to plain Postgres

Two facts unlock the whole thing:

1. When PostgREST handles a request, it does SET ROLE anon (or authenticated) and stuffs the decoded JWT into a session setting called request.jwt.claims.
2. auth.uid() is just a SQL function that reads sub out of those claims.

The service_role key is different in kind — it's a server secret that bypasses RLS entirely (full read, write, delete, plus Storage). It never belongs in a browser, a public repo, or a client config, and it's not what we're testing here. (Reflex check: a NEXT_PUBLIC_ / VITE_ / EXPO_PUBLIC_ prefix inlines a value straight into the client bundle — safe for the anon key and project URL, catastrophic for service_role. Tell the two keys apart by decoding the JWT's middle segment as base64URL: "role":"anon" vs "role":"service_role" — or by the newer sb_publishable_ vs sb_secret_ prefixes.) We're testing the role that is exposed: anon.

So to reproduce a request locally, we set the role and set the claims. That's it.

Step 1 — a minimal shim

If you run the full stack with supabase start, the auth schema and roles already exist and you can skip this. On a bare Postgres, create them:

-- roles PostgREST switches into
do $$
begin
  if not exists (select from pg_roles where rolname = 'anon') then
    create role anon nologin;
  end if;
  if not exists (select from pg_roles where rolname = 'authenticated') then
    create role authenticated nologin;
  end if;
end $$;

-- the same auth.uid() Supabase gives you
create schema if not exists auth;
create or replace function auth.uid() returns uuid
language sql stable
as $$
  select nullif(current_setting('request.jwt.claims', true)::jsonb ->> 'sub', '')::uuid
$$;

The true argument to current_setting means "return NULL instead of erroring if it's unset" — which is exactly the anonymous case. For anon, auth.uid() is NULL. Keep that in mind: any policy written as auth.uid() = user_id silently denies the anon user, because NULL = anything is never true. That's usually what you want.

Step 2 — a table with a policy to test

create table if not exists documents (
  id       bigint generated always as identity primary key,
  owner_id uuid not null,
  title    text not null,
  body     text
);

alter table documents enable row level security;

-- owners can read their own rows
create policy "owner can read"
  on documents for select
  to authenticated
  using (auth.uid() = owner_id);

Note the to authenticated. A policy with no TO clause applies to all roles, including anon — a common way to leak data by accident. Being explicit about the role is half the battle.

Step 3 — impersonate anon and a real user

Wrap each check in a transaction with SET LOCAL so the role and claims reset automatically on ROLLBACK. That keeps tests isolated and non-destructive.

-- as an anonymous visitor (no JWT)
begin;
  set local role anon;
  select count(*) as anon_sees from documents;
rollback;

-- as a specific logged-in user
begin;
  select set_config(
    'request.jwt.claims',
    json_build_object(
      'sub',  '11111111-1111-1111-1111-111111111111',
      'role', 'authenticated'
    )::text,
    true                       -- is_local: scoped to this transaction
  );
  set local role authenticated;
  select id, title from documents;      -- only their rows
rollback;

Set the claims before switching role, then switch. Now you can assert concrete numbers. With the policy above, anon_sees should be 0, and the authenticated block should return only rows whose owner_id matches that sub. If anon_sees is anything but zero, you have a leak — go look at your TO clauses and USING expressions.

The two mistakes worth internalizing

USING vs WITH CHECK are not interchangeable. USING decides which existing rows a role can see, and which it's allowed to scan for UPDATE / DELETE. WITH CHECK validates the new row values on INSERT / UPDATE. INSERT policies have WITH CHECK only. Getting these backwards is how people write an "owners only" update policy whose USING guards the old row but whose missing WITH CHECK lets anyone reassign a row to themselves.

USING (true) means world-readable for SELECT. A SELECT policy with USING (true) simply lets everyone read — genuinely fine for a public catalog. It only becomes a write hole if a permissive INSERT / UPDATE / ALL policy also opens that path. Permissive policies OR together, so one stray using(true) with no TO widens access to anon in whatever command it targets. And a role needs both a matching policy and the table GRANT: RLS with the grant missing fails closed, while a grant on a table with RLS still disabled fails wide open.

Step 4 — audit the whole schema at once

Before you write per-table tests, get a bird's-eye view. Two read-only queries surface most real problems.

Tables where RLS is off entirely:

select n.nspname as schema, c.relname as table
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and not c.relrowsecurity;

Every policy, with the fields that matter — roles, cmd, and the actual expressions:

select tablename,
       policyname,
       roles,          -- {public} = applies to anon too
       cmd,            -- SELECT / INSERT / UPDATE / DELETE / ALL
       qual       as using_expr,
       with_check as check_expr
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

Read it like a reviewer: any row where roles is {public} and using_expr is true on a table with private data is a red flag. Any table in the first query's output that isn't meant to be fully public is a bigger one. The demo repo ships this audit SQL as a single file plus a seeded leak you can watch the harness catch — a fast way to confirm your setup works before you point it at your own schema.

Wire it into CI

Because the whole thing is SQL against a throwaway database, it runs anywhere Postgres does. A pg_prove file or even a plain script that fails when anon_sees > 0 will do:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f test/rls_anon_readonly.sql

Run it against supabase db reset output on every push. Now a policy regression breaks the build instead of leaking rows in production.

Where to go from here

You now have a repeatable way to prove — not hope — that anon sees exactly what you intend. Clone github.com/cekuu35/supabase-rls-leak-demo, run the audit against your schema, and fix whatever the pg_policies query lights up.

If you'd rather not hand-roll the assertions, I keep a small RLS Audit Kit ($29) — the same harness plus a checklist of per-command policy tests (insert-as-other-user, update-reassignment, delete-scope) and copy-paste pg_prove cases. Entirely optional; the repo above is enough to secure your project today. Either way, go run the anon check before you ship.