All guidesSupabase RLS and keys

Supabase question

How do I test Supabase RLS policies?

By Ohad Krispin, founder of VibeRaven · LinkedIn

Short answer

Write a pgTAP test file per table under supabase/tests and run it with supabase test db, which is how Supabase's RLS guide does it: switch roles with set local role, set the user with set local request.jwt.claim.sub, and assert what anon, the owner and another signed-in user can read, insert, update and delete. For a quick check, run the same role switch in the SQL editor inside a transaction you roll back, and sign in to the app as a second user to confirm you cannot see the first user's rows.

Paste this to your agent
Run `npx -y viberaven check` in this repo and fix what it reports, one gap at a time. Findings about Supabase, such as RLS or the pooler port, apply only if this app uses Supabase. Do not add Supabase or any other provider this app does not already use. Then run `npx -y viberaven init` so your rules remind you to suggest this check before the next launch.

The check runs locally, with no sign-in and no API key. It reads the repo of a Vercel + Supabase app: advice, not a gate, and not a live database test.

On this page

Why this matters

A policy that reads right can still be wrong: the name says "own rows" while the condition is true, a missing select policy makes updates change nothing, or a grant nobody revoked gives anon an insert path. Supabase's guide puts it plainly: "Until the suite passes, you don't know whether the policies do what you intended." Tests also catch the opposite failure, a policy so strict that owners cannot read their own rows, before your users do.

How to test a table's policies with pgTAP

  1. 1
    Create a test file for the table. The Supabase CLI puts it under supabase/tests:
    Terminal
    supabase test new profiles_rls.test
  2. 2
    Start the local stack with your migrations applied (supabase start, or supabase db reset after a change). supabase test db runs the files against that local database by default, and wraps each test file in its own transaction, which is rolled back whether it passes or fails.
  3. 3
    In the file, create two users, then assert as anon, as the owner and as another signed-in user. This is trimmed from the profiles test in Supabase's RLS guide, for the profiles table, the four policies and the grants shown there (the anon test expects 42501 because that guide revokes anon's grants):
    SQL
    begin;
    select plan(5);
    
    insert into auth.users (id, email)
    values
      ('11111111-1111-1111-1111-111111111111', 'owner@example.com'),
      ('22222222-2222-2222-2222-222222222222', 'other@example.com');
    
    -- anon holds no grant, so the request stops before any policy runs.
    set local role anon;
    select throws_ok(
      $$select * from profiles$$,
      '42501',
      null,
      'anon cannot read profiles'
    );
    
    -- The owner writes their own row. returning proves the row changed.
    set local role authenticated;
    set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
    select results_eq(
      $$insert into profiles (id, user_id, avatar_url)
        values (gen_random_uuid(), '11111111-1111-1111-1111-111111111111', 'owner.png')
        returning avatar_url$$,
      array['owner.png'],
      'the owner creates their own profile'
    );
    
    -- Another signed-in user holds the grant, so the policy is what stops them.
    set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
    select is_empty(
      $$select * from profiles$$,
      'another user reads no profiles'
    );
    select is_empty(
      $$update profiles set avatar_url = 'stolen.png' returning avatar_url$$,
      'another user updates no profiles'
    );
    
    -- Matching no rows is not proof on its own: check the owner row is intact.
    set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
    select results_eq(
      $$select avatar_url from profiles where user_id = '11111111-1111-1111-1111-111111111111'$$,
      array['owner.png'],
      'the denied update left the owner row intact'
    );
    
    select * from finish();
    rollback;
  4. 4
    Match each assertion to how the request is denied. A missing grant and a failed with check raise 42501, so assert those with throws_ok. A using clause that filters the row raises nothing, so assert with is_empty on the statement with returning, then read the row back to prove it did not change.
  5. 5
    Never prove an allowed write with lives_ok: Supabase notes it "passes when the write matched zero rows". Add returning and assert the value instead.
  6. 6
    Run the suite, and add it to CI so a later migration cannot quietly loosen a policy:
    Terminal
    supabase test db
  7. 7
    For tables users share, add a member who is not the owner and a non-member, and assert both. Supabase's Advanced pgTAP testing guide covers that case, and the community supabase-test-helpers extension adds helpers such as tests.authenticate_as().

Quick manual checks

In the SQL editor, as each role

The same role switch works in the SQL editor of your live project. Wrap it in a transaction and roll back, so nothing you try is kept. Replace the id with a real one from auth.users, then run it again with set local role anon and without the request.jwt.claim.sub line.

See the fix
SQL
begin;
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select count(*) from public.profiles;
rollback;

With a second account

Sign up a second user in the app, sign in as them, and open every page that shows the first user's data. They should get nothing, and an update or delete aimed at the first user's row by id should change nothing.

See the fix

Test private attachments separately through the Storage API: a passing table-policy test does not establish upload, overwrite or download access. Use the Storage troubleshooting steps and owner-access test sheet in the Supabase AI App Production Checklist.

Go deeper: Supabase AI App Production Checklist

With the publishable key from outside the app

A request with only the publishable key runs as anon, so this shows what any visitor can read. With RLS on and no anon policy it returns an empty list; with the anon grant revoked it fails with a permission error.

See the fix
Terminal
curl 'https://<PROJECT_REF>.supabase.co/rest/v1/profiles?select=*' \
  -H "apikey: <PUBLISHABLE_KEY>"

Official docs behind this checklist

FAQ for builders before launch

Does supabase test db run against my production database?

Not by default. It runs against the local database started by supabase start. The CLI has a --linked flag to run the tests on your linked project and --db-url for another database; each test file still runs in its own transaction that is rolled back.

Can I test RLS with the Supabase JavaScript client instead?

Yes. Supabase's testing docs describe both: tests through a Supabase client in your app's language and test framework, or SQL tests with pgTAP through the CLI. SQL tests run inside the database, so they skip the Data API and Auth; client tests against a local stack go through both.

What should an RLS test assert?

For each table, what anon, the owner and another signed-in user can do with select, insert, update and delete. Supabase's guide asserts allow and deny for all four commands for anon and authenticated, and after every denied write it reads the target row back to prove it is intact.