How to Test Supabase RLS: SQL Editor, Two Users and pgTAP

By the Tuvio team29 October 202611 min read

How to test Supabase RLS: act as three people, a signed-out visitor, the row's owner and a second signed-up user, and try all four operations as each one. Do it first in SQL by switching to the anon or authenticated role and setting the user's JWT claims inside a transaction that rolls back. Then repeat it from the client with two real test accounts, and finally write it down as a pgTAP file that supabase test db runs on every change. A query that works in the SQL Editor as postgres proves nothing, because that role bypasses RLS.

This post is about checking policies you already have. For the policies themselves, and the difference between using and with check, see Supabase RLS policy examples. The examples below use the owner-only todos table from that post.

How to test Supabase RLS: who and what to test

Every table your app exposes gets the same grid. Each cell is a request that should either work or fail, and you write down which before you run anything.

	Signed-out visitor	The owner	A second user
select	sees nothing (or only public rows)	sees their rows	sees none of the owner's rows
insert	fails	works for their own rows	can't create a row owned by someone else
update	changes nothing	works on their rows	changes nothing of the owner's
delete	removes nothing	works on their rows	removes nothing of the owner's

The second user is the column people skip, and it's the one that catches to authenticated using (true). That policy looks fine when you test as yourself, because you do see your own rows. You just also see everyone else's.

Supabase's Row Level Security guide now treats this as part of securing a table: a test file under supabase/tests/ that "asserts allow and deny for select, insert, update, and delete, for anon and authenticated", and it adds plainly: "Until the suite passes, you don't know whether the policies do what you intended."

Before the first test, know how a denial looks, because there are three shapes and only two of them are errors:

A missing grant raises error 42501 before any policy runs. With the grants that guide recommends (revoke everything from anon on a private table), a signed-out read fails this way.
A with check violation raises 42501 too, with the message "new row violates row-level security policy". This is what a blocked insert looks like.
A using clause filtering the row out raises nothing. The select returns [], and the update or delete reports zero rows changed. The Postgres docs say such rows "are silently suppressed; no error is reported".

So an empty result is a pass only when you expected it. And an update with no error is not proof it worked.

How to check RLS in Supabase from the SQL editor

The quickest test runs in the SQL Editor or in psql. Queries you run in the dashboard execute as postgres, per Supabase's Postgres roles page, and postgres has the bypass-RLS attribute. So you switch to the role a real request would use, set the claims Supabase's auth.uid() reads, and roll back at the end.

First, as the second user. Replace the id with a real one from select id, email from auth.users;:

sql

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "22222222-2222-2222-2222-222222222222", "role": "authenticated"}';

select id, user_id, title from public.todos;

rollback;

You should see only that user's rows. If you see rows with another user_id, the select policy is too open.

Now the writes, still as the second user, aimed at the owner's rows. Run them one at a time, so the result on screen is the one you meant:

sql

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "22222222-2222-2222-2222-222222222222", "role": "authenticated"}';

-- Expect zero rows back. Any row here means the update policy is open.
update public.todos set title = 'hacked'
where user_id = '11111111-1111-1111-1111-111111111111'
returning id;

rollback;

Swap the update for delete from public.todos where user_id = '1111...' returning id; (expect zero rows) and for an insert of a row with the owner's user_id (expect "new row violates row-level security policy"). The rollback means nothing you try stays in the table, even if a policy turns out to be open.

For the signed-out visitor, switch to anon and set no claims:

sql

begin;
set local role anon;
select count(*) from public.todos;
rollback;

Expect 0, or a permission-denied error if you've revoked anon's grants. A non-zero count on a private table is the open-table problem in Supabase RLS disabled in public.

`request.jwt.claim.sub` or `request.jwt.claims`?

Supabase's own test examples use set local request.jwt.claim.sub = '<user id>'. That works for auth.uid(), but auth.jwt() doesn't read it. In the Supabase Auth source, auth.jwt() reads request.jwt.claim or request.jwt.claims, so a policy built on auth.jwt(), an app_metadata role check or the storage folder policies below, sees null and denies everything. Your "deny" tests pass for the wrong reason.

Setting the full request.jwt.claims JSON covers both functions, and lets you add claims your policies read, such as "app_metadata": {"role": "admin"}. The dashboard's own role impersonation sets this same setting, with set_config('request.jwt.claims', ..., true), as you can see in the Supabase Studio source. Pick one style per test and stick to it: if both are set, auth.uid() prefers claim.sub, so the two can disagree about who is asking.

Supabase impersonate user in the dashboard

The dashboard can also run queries as a role or as a specific user without any of the SQL above. Supabase lists user impersonation as a public beta and says you can "use the Table Editor, SQL Editor, and GraphiQL to impersonate users and test queries and mutations." It's handy for a quick look at what one user sees. It doesn't leave anything behind to rerun, so treat it as a spot check and keep the real tests in files.

Test from the client with two real users

The SQL tests prove the policies. A client test proves the whole path: the publishable key, a real sign-in, the Data API, and the grants on the table. Create two test accounts in a development project (not production), then save this as rls-check.mjs and run it with Node 18 or later and @supabase/supabase-js:

import assert from 'node:assert/strict'
import { createClient } from '@supabase/supabase-js'

const url = process.env.SUPABASE_URL
const key = process.env.SUPABASE_PUBLISHABLE_KEY // the publishable key, never the secret key
const options = { auth: { persistSession: false } }

async function signIn(email, password) {
  const client = createClient(url, key, options)
  const { data, error } = await client.auth.signInWithPassword({ email, password })
  if (error) throw error
  return { client, id: data.user.id }
}

// Denied means an error, or no rows touched.
const denied = (res) => res.error !== null || res.data.length === 0

const visitor = createClient(url, key, options)
const owner = await signIn(process.env.OWNER_EMAIL, process.env.OWNER_PASSWORD)
const other = await signIn(process.env.OTHER_EMAIL, process.env.OTHER_PASSWORD)

const { data: row, error } = await owner.client
  .from('todos')
  .insert({ title: 'rls check', user_id: owner.id })
  .select()
  .single()
assert.equal(error, null, 'owner can create a todo')

assert.ok(denied(await visitor.from('todos').select('id').eq('id', row.id)), 'visitor read')
assert.ok(denied(await other.client.from('todos').select('id').eq('id', row.id)), 'other read')
assert.ok(
  denied(await other.client.from('todos').update({ title: 'hacked' }).eq('id', row.id).select()),
  'other update'
)
assert.ok(
  denied(await other.client.from('todos').delete().eq('id', row.id).select()),
  'other delete'
)

const planted = await other.client.from('todos').insert({ title: 'planted', user_id: owner.id })
assert.equal(planted.error?.code, '42501', 'other cannot create a row for the owner')

const after = await owner.client.from('todos').select('title').eq('id', row.id).single()
assert.equal(after.data.title, 'rls check', 'owner row untouched')

await owner.client.from('todos').delete().eq('id', row.id)
console.log('RLS checks passed')

Two details matter here. The update and delete calls end in .select() because, per the supabase-js update reference, "By default, updated rows are not returned". Without it, a blocked update and a successful one look the same: no error, no data. And the final read as the owner is the real proof that the blocked writes changed nothing.

Supabase's testing overview has a Vitest version of the same idea that creates its users with the admin API. That's fine for setup. Just never run the assertions on a client made with the secret key.

Test Supabase Storage policies too

If users upload files, run the same second-user test against the bucket. Storage policies are RLS policies on storage.objects, and Supabase's storage access control guide uses a first folder named after the user id as the owner check. Add this to the script:

const path = `${owner.id}/rls-check.txt`
await owner.client.storage.from('private-files').upload(path, new Blob(['hello']))

const read = await other.client.storage.from('private-files').download(path)
assert.ok(read.error, 'other cannot download the owner file')

const write = await other.client.storage
  .from('private-files')
  .upload(`${owner.id}/planted.txt`, new Blob(['x']))
assert.ok(write.error, 'other cannot upload into the owner folder')

await owner.client.storage.from('private-files').remove([path])

This only means something on a private bucket. Supabase's bucket docs say a public bucket "effectively bypasses access controls for both retrieving and serving files", so anyone with the URL can read the file whatever your select policy says. Uploads, deletes and moves are still checked.

Supabase test RLS policies with pgTAP

Manual checks rot. The version that keeps working is a pgTAP file that runs in a transaction, as each user, every time you change a migration. You need the Supabase CLI and a local stack (supabase start). Create the file and run the suite:

bash

supabase test new todos_rls.test
supabase test db

Put this in supabase/tests/todos_rls.test.sql:

sql

begin;
create extension if not exists pgtap with schema extensions;
select plan(9);

-- Setup runs as postgres, so these inserts skip RLS.
insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', 'owner@test.com'),
  ('22222222-2222-2222-2222-222222222222', 'other@test.com');
insert into public.todos (user_id, title) values
  ('11111111-1111-1111-1111-111111111111', 'owner todo'),
  ('22222222-2222-2222-2222-222222222222', 'other todo');

-- Signed-out visitor
set local role anon;
select is_empty(
  $$select * from public.todos where user_id in ('11111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222')$$,
  'anon reads no todos'
);

-- The owner
set local role authenticated;
set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';
select results_eq(
  $$select title from public.todos where user_id = '11111111-1111-1111-1111-111111111111'$$,
  array['owner todo'],
  'the owner reads their own todo'
);
select results_eq(
  $$update public.todos set done = true
    where user_id = '11111111-1111-1111-1111-111111111111'
    returning title$$,
  array['owner todo'],
  'the owner updates their own todo'
);

-- A second signed-up user
set local request.jwt.claims = '{"sub":"22222222-2222-2222-2222-222222222222","role":"authenticated"}';
select is_empty(
  $$select * from public.todos where user_id = '11111111-1111-1111-1111-111111111111'$$,
  'another user cannot read the owner''s todo'
);
select is_empty(
  $$update public.todos set title = 'hacked'
    where user_id = '11111111-1111-1111-1111-111111111111'
    returning title$$,
  'another user updates nothing'
);
select is_empty(
  $$delete from public.todos
    where user_id = '11111111-1111-1111-1111-111111111111'
    returning title$$,
  'another user deletes nothing'
);
select throws_ok(
  $$insert into public.todos (user_id, title)
    values ('11111111-1111-1111-1111-111111111111', 'planted')$$,
  '42501',
  null,
  'another user cannot insert a todo for the owner'
);
-- No where clause on purpose: see the note below the file.
select throws_ok(
  $$update public.todos set user_id = '11111111-1111-1111-1111-111111111111'$$,
  '42501',
  null,
  'another user cannot hand their todo to the owner'
);

-- Back as the owner: the denied writes left the row alone
set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';
select results_eq(
  $$select title from public.todos where user_id = '11111111-1111-1111-1111-111111111111'$$,
  array['owner todo'],
  'the owner''s todo is intact'
);

select * from finish();
rollback;

The assertions follow the rule in Supabase's guide: errors get throws_ok, silent filtering gets is_empty over a statement with returning, and every denied write is followed by a read showing the target row is intact. The guide is direct about the trap: "Never prove an allowed write with lives_ok. It passes when the write matched zero rows."

The missing where in the last update is deliberate. When an update reads a column, in a where or a returning, Postgres also checks the new row against the select policies, per its CREATE POLICY docs. With a where, a strict select policy would block the move even if the update policy said with check (true), and the test would pass without testing the update policy at all.

To run the suite on every pull request, the testing overview has a short GitHub Actions workflow that runs supabase start and then supabase test db.

Supabase test helpers for less boilerplate

Supabase's advanced pgTAP guide documents the community basejump-supabase_test_helpers package, installed through database.dev. It adds tests.create_supabase_user(), tests.authenticate_as(), tests.get_supabase_uid() and tests.clear_authentication(), which replace the auth.users inserts and claim settings above. It also adds a schema-wide check that every table has RLS on:

sql

begin;
select plan(1);
select tests.rls_enabled('public');
select * from finish();
rollback;

That one test is worth adding even if you write everything else by hand. The install block is long, so copy it from that guide into a 000-setup-tests-hooks.sql file, which runs first because test files run in alphabetical order.

Supabase RLS testing: the false passes

Each of these makes a broken policy look fine:

Testing as postgres. The SQL Editor's default role bypasses RLS. Every query returns every row, so it looks like your app's data is there, and you learn nothing about who else can see it.
Testing with the secret key. The secret key, or the legacy service_role key, uses a role with the bypass-RLS attribute. Supabase's guide adds a subtlety: it bypasses RLS "only when the request carries no user access token". A test client built with the secret key and no sign-in passes everything. More on that key in the Supabase service role key.
Testing only as yourself. The owner always passes. A second user is what catches policies open to any signed-in account.
Reading [] as an error, or a quiet update as success. Filtered reads and writes don't raise errors. Assert on the rows, then read the target row back.
Testing only select. Each policy covers the operation in its for clause. A correct select policy says nothing about insert, update or delete, and a table with an open update policy leaks writes while reads look perfect.
Setting claim.sub for a policy that reads auth.jwt(). The claims are null, the policy denies everything, and the deny tests pass for the wrong reason.

Checklist: test your Supabase RLS before you ship

For every exposed table, write the grid: visitor, owner, second user, across select, insert, update and delete.
Run the reads and writes in SQL as anon and authenticated, inside begin and rollback.
Set request.jwt.claims with the full JSON, including any claims your policies read.
Run the client test with two real test accounts and the publishable key, in a development project.
Test storage buckets the same way, and confirm private files sit in private buckets.
Put the cases in supabase/tests/<table>_rls.test.sql, add tests.rls_enabled('public'), and run supabase test db in CI.
Check every denied write with a read afterwards, and never use lives_ok to prove an allowed one.

What a migration scan adds

Tuvio's free scan, with no account, reads the .sql migration files in your repository and flags the policy shapes these tests tend to catch: policies open to anyone (using (true) or with check (true), including to authenticated using (true)), policies open to any logged-in user, a migration that turns RLS off on a table, policies that trust user_metadata, and security definer functions without a fixed search_path. A table where RLS was never turned on isn't flagged yet; the anon count above catches that one. A table that is public on purpose gets flagged too, so read each finding against what you meant.

It doesn't replace the tests. It reads what your migrations say; it doesn't connect to your database or run your policies, and a policy created only in the dashboard and never committed as a migration isn't seen. The tests above check what the database actually does. The sample report shows what a scan returns.