How to test your Supabase RLS policies

To test Supabase RLS, never test in the SQL editor — it runs as the postgres superuser and bypasses RLS, so every policy silently "passes." Test either at the database level with pgTAP while impersonating a role via set local role, or through the client SDK as a real signed-in user. Both exercise the policy the way a real request does.

RLS is the one part of a Supabase app that fails silently. A wrong policy doesn't throw — it just returns the wrong rows, and you don't notice until someone reads data that isn't theirs. The reason most teams think they tested their policies but didn't is a single trap in the tooling.

Why does my RLS policy work in the SQL editor but leak in the app?

Because the Supabase SQL editor connects as the postgres role, which bypasses Row Level Security entirely. Any query you run there ignores your policies, so a broken policy and a correct one look identical. You were never testing RLS — you were testing raw table access as a superuser.

This is the number-one reason a policy that "looked fine" leaks in production. To actually exercise a policy you have to run the query as the anon or authenticated role, with a user's claims attached — exactly the context Supabase creates for a real request. There are two solid ways to do that.

Method 1: pgTAP tests in the database

Supabase's built-in testing runs pgTAP SQL tests through the CLI. The key move is switching roles and setting the JWT claims inside a transaction, then asserting what each role can see. Put this in supabase/tests/database/01-notes-rls.sql:

begin;
select plan(2);

-- Seed two users' rows as the superuser (setup runs before role switch).
insert into public.notes (user_id, body) values
  ('11111111-1111-1111-1111-111111111111', 'alice note'),
  ('22222222-2222-2222-2222-222222222222', 'bob note');

-- Become an authenticated user and claim to be Alice.
set local role authenticated;
set local request.jwt.claims to
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';

-- Alice should see ONLY her row. A correct policy returns 1; USING (true) returns 2.
select is(
  (select count(*)::int from public.notes),
  1,
  'authenticated user sees only their own notes'
);

-- The anon role should see nothing.
set local role anon;
select is(
  (select count(*)::int from public.notes),
  0,
  'anon sees no notes'
);

select * from finish();
rollback;

Run it with supabase test db. Because the whole thing is wrapped in begin/rollback, it leaves no data behind. The critical lines are set local role and set local request.jwt.claims — that's what makes auth.uid() resolve inside the policy the way it does for a real request. This is the fastest, most isolated way to test, and it runs in CI.

Method 2: test through the client SDK

Database tests prove the policy logic; SDK tests prove the whole path — your Next.js server client, the forwarded session, and the policy together. Sign in as a real test user and assert what the query returns:

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(URL, ANON_KEY); // anon key, NOT service_role

test("a user cannot read another user's notes", async () => {
  await supabase.auth.signInWithPassword({
    email: "alice@test.dev",
    password: process.env.TEST_PASSWORD!,
  });

  const { data } = await supabase.from("notes").select("*");
  // Every returned row must belong to Alice.
  expect(data!.every((n) => n.user_id === ALICE_ID)).toBe(true);
});

Use the anon key here, never the service_role key — it bypasses RLS and would make your test pass no matter how broken the policy is. That's the SQL-editor trap in a different costume.

The policy these tests are meant to catch

Here's the exact bug both methods exist to catch — a policy that reads as scoped but isn't:

create policy "Users read their notes"
  on public.notes for select
  using (true);

It's named "Users read their notes," RLS is enabled, and in the SQL editor it looks perfect. But USING (true) returns every row to every caller — the predicate never references auth.uid(). A static scan catches this without you writing a single test. Here's GuardLayer on that migration — live engine output:

A scanner and a test suite aren't redundant: the scan flags a wide-open policy the instant you push, before you've even written a test; the tests prove the policies you did scope actually behave under real roles. Both beat finding out from a user.

A quick manual check

No test harness set up yet? You can still exercise a policy by hand in a psql session:

begin;
set local role authenticated;
set local request.jwt.claims to '{"sub":"<a-user-uuid>","role":"authenticated"}';

select * from public.notes;  -- should return ONLY that user's rows

rollback;

If that returns rows belonging to other users, the policy is broken — regardless of what the SQL editor showed you. For the common follow-up, auth.uid() returning NULL, the fix is almost always that the claims weren't set.

FAQ

Why does my Supabase RLS policy pass in the SQL editor but fail in the app? The SQL editor runs as the postgres superuser, which bypasses RLS, so policies are never exercised there. Test as the anon or authenticated role — via pgTAP with set local role, or through the client SDK signed in as a real user.

How do I test RLS policies in CI? Write pgTAP tests under supabase/tests/database/ and run supabase test db. Wrap each test in begin/rollback so it leaves no data, and use set local role plus set local request.jwt.claims to impersonate a user.

Can I test RLS with the service_role key? No — the service_role key bypasses RLS entirely, so the test passes even when the policy is broken. Always test with the anon key and a real user session.

Do I need pgTAP, or is client-side testing enough? Either works; together they're strongest. pgTAP tests the policy logic in isolation and runs fast in CI. SDK tests verify the full request path including your Next.js session forwarding.

How do I set auth.uid() in a test? Set the JWT claims for the transaction: set local request.jwt.claims to '{"sub":"<uuid>","role":"authenticated"}';. Then auth.uid() returns that sub inside your policies.