Supabase RLS Not Working? Fix It by Symptom (Correct SQL Inside)

Your policy looks right, the SQL editor says it works, but the JS client returns []. Or an insert throws new row violates row-level security policy. Before you rewrite anything, work this checklist top to bottom. Almost every "Supabase RLS not working" report is one of five things, and the first one is the culprit far more often than people expect.

Two ideas fix half of these before you write any SQL:

• A row is reachable only when the requesting role has BOTH a matching policy AND the table-level GRANT. Public-schema tables are exposed to anon/authenticated through PostgREST's default grants; RLS + policies then decide what each role can actually do.
• USING filters existing rows (SELECT, and which rows UPDATE/DELETE may touch). WITH CHECK validates new row values (INSERT, and the post-image of UPDATE). INSERT has WITH CHECK only. If you omit WITH CHECK on UPDATE/ALL, Postgres reuses USING as the check.

The #1 cause: role mismatch between test and runtime

RLS decisions depend entirely on which database role makes the request and what's in the JWT. The trap is that your SQL-editor test and your real app run as different identities.

In the SQL editor you're postgres, which — like service_role — bypasses RLS entirely and never even runs your policies. So you try to simulate a user:

set role authenticated;
select * from posts;

But set role authenticated alone leaves request.jwt.claims unset, so auth.uid() returns NULL and any policy like user_id = auth.uid() silently matches nothing. You conclude "my policy is broken" when your test was broken.

Meanwhile the JS client sends a real signed JWT (role authenticated) when there's a session, or hits the DB as anon when there isn't. Three different worlds. Verify the real one before touching a policy.

To simulate correctly, you must set the claims — and set local only lives inside a transaction, so wrap it:

begin;
  select set_config('role', 'authenticated', true);
  select set_config(
    'request.jwt.claims',
    '{"sub":"<a-real-user-uuid>","role":"authenticated"}',
    true
  );
  select auth.uid(), auth.role();   -- now returns the uuid + authenticated
commit;

Running those as three separate statements will not work — each set_config(..., true) is local to its own implicit transaction and is gone by the next line.

Symptom 1: "works in the editor, empty/failing in the app" (or vice versa)

Stop guessing and print the real identity from the real request path. The JS client can't select auth.uid() off a normal table query, so expose it through a tiny function once:

create function public.whoami()
returns table(role text, uid uuid)
language sql stable
as $$ select auth.role(), auth.uid() $$;

grant execute on function public.whoami() to anon, authenticated;

Then call await supabase.rpc('whoami') from the app. If uid is NULL where you expected a logged-in user, the client has no valid session — you're actually anon. Confirm the client uses the anon/publishable key and that getSession() returns a user.

(Side note that saves reputations: the anon/publishable key is public by design — it ships in the browser bundle, it is not a secret, and it does not need rotating. The service_role key is the one that must never reach a browser, because it bypasses RLS completely.)

Symptom 2: "select returns [] or null, no error"

An empty read is almost never a query bug. With RLS enabled, no matching SELECT policy = deny, and reads don't error — they silently return nothing. Enabling RLS with zero policies denies everything.

Check what policies actually cover reads. Note cmd can be SELECT or ALL — a FOR ALL policy covers SELECT too, so don't test for SELECT alone:

select policyname, cmd, roles, qual, with_check
from pg_policies
where tablename = 'posts';

If nothing has cmd in ('SELECT','ALL') for your role, add one:

create policy "read own posts"
on public.posts for select
to authenticated
using ( (select auth.uid()) = user_id );

Wrap it as (select auth.uid()) — the subselect lets Postgres evaluate it once per query instead of once per row, a real win on large tables. A genuinely public table still needs an explicit to anon, authenticated using (true); no policy means no rows.

Symptom 3: PGRST116 right after a successful insert

The insert worked, yet the client throws PGRST116. Here's the exact mechanism: in supabase-js v2 an insert returns nothing unless you chain .select(). When you do .insert(payload).select().single(), the write commits, but the follow-up read is filtered by your SELECT policy — so .single() sees 0 rows and raises PGRST116 ("JSON object requested, 0 rows returned").

The fix is a SELECT policy that lets writers see their own row (the Symptom 2 policy usually covers it). If you don't need the row back, just don't call .select() after the insert.

Symptom 4: "new row violates row-level security policy" on insert

This exact string is a WITH CHECK failure — Postgres evaluated your new row against the check and it returned false. (A missing GRANT surfaces differently, as permission denied for table posts; don't confuse the two.) The usual cause: your policy requires the owner column to equal the current user, but you're not authenticated or you're not setting that column.

create policy "insert own posts"
on public.posts for insert
to authenticated
with check ( (select auth.uid()) = user_id );

Verify (1) you're actually authenticated (Symptom 1), and (2) your insert sets user_id — explicitly, or via a column default auth.uid(). Remember INSERT has no USING; a USING clause on an insert policy does nothing.

If instead you hit permission denied for table, restore the grant:

grant select, insert, update, delete on public.posts to authenticated;

Symptom 5: "anon can still read or write when it shouldn't"

Two root causes, in order of likelihood. First, RLS may never have been enabled on the table — the single most common real-world Supabase leak. The default PostgREST grant alone exposes full CRUD, no policy required. Always confirm it's on:

select relname, relrowsecurity
from pg_class
where relname = 'posts';   -- relrowsecurity must be true

alter table public.posts enable row level security;

Second, an over-permissive policy. Permissive policies (the default) OR together, so one leftover to anon using (true) or with check (true) overrides every stricter policy beside it. Scan the pg_policies output from Symptom 2 for any true whose roles include anon or public, and drop it — or convert a guardrail to restrictive (restrictive policies AND in; one false blocks):

create policy "tenant guard"
on public.posts as restrictive for all
to authenticated
using ( tenant_id = (select auth.jwt() ->> 'tenant_id') );

One last landmine: never SELECT from a table inside a policy on that same table — it re-triggers the policy and recurses infinitely. Push membership/tenant lookups into a security definer function or a separate table.

Run the order every time

Confirm the role → confirm RLS is enabled → confirm a policy exists for that command → confirm the GRANT → read USING vs WITH CHECK. Ninety percent of RLS pain lives in those five checks.

Want to watch each symptom fail and pass locally? I keep a runnable repro — a PGlite sandbox (no cloud project needed) plus a free, read-only 9-query audit you paste into the SQL editor — at github.com/cekuu35/supabase-rls-leak-demo. It flags tables with RLS off, policies granted to anon, and USING (true) / WITH CHECK (true) shapes.

If you later want a deeper sweep across a whole schema — multi-tenant leaks, restrictive-policy interactions, storage buckets — I've packaged a more thorough checklist as an RLS Audit Kit ($29). Entirely optional; the free audit above unsticks most people today.