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

URL: https://tuvio.dev/blog/how-to-test-supabase-rls
Published: 2026-10-29
By the Tuvio team

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](https://tuvio.dev/blog/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](https://supabase.com/docs/guides/database/postgres/row-level-security) 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](https://www.postgresql.org/docs/current/sql-createpolicy.html) 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](https://supabase.com/docs/guides/database/postgres/roles), 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](https://tuvio.dev/blog/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](https://github.com/supabase/auth/blob/master/migrations/20220531120530_add_auth_jwt_function.up.sql), `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](https://github.com/supabase/supabase/blob/master/packages/pg-meta/src/sql/studio/role-impersonation.ts). 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](https://supabase.com/features/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`:

```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](https://supabase.com/docs/reference/javascript/update), "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](https://supabase.com/docs/guides/local-development/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](https://supabase.com/docs/guides/storage/security/access-control) uses a first folder named after the user id as the owner check. Add this to the script:

```js
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](https://supabase.com/docs/guides/storage/buckets/fundamentals) 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](https://supabase.com/docs/guides/database/testing) 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](https://www.postgresql.org/docs/current/sql-createpolicy.html). 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](https://supabase.com/docs/guides/local-development/testing/pgtap-extended) 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](https://tuvio.dev/blog/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

1. For every exposed table, write the grid: visitor, owner, second user, across select, insert, update and delete.
2. Run the reads and writes in SQL as `anon` and `authenticated`, inside `begin` and `rollback`.
3. Set `request.jwt.claims` with the full JSON, including any claims your policies read.
4. Run the client test with two real test accounts and the publishable key, in a development project.
5. Test storage buckets the same way, and confirm private files sit in private buckets.
6. Put the cases in `supabase/tests/<table>_rls.test.sql`, add `tests.rls_enabled('public')`, and run `supabase test db` in CI.
7. 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](https://tuvio.dev/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](https://tuvio.dev/example) shows what a scan returns.