RLS Testing - Powabase

You wrote a policy. Before you ship it to a thousand users, you want to confirm it does what you think it does. The trick is that PostgREST sets the role and JWT claims for you on real requests. In a SQL session you have to do that setup by hand. This guide shows how to impersonate anon and authenticated (with any auth.uid() you choose) from psql or the Studio SQL Editor, then run your reads and writes against the policy to confirm it accepts the right things and rejects the wrong things. For the policies themselves, see the RLS Cookbook. For the model that determines who gets which role, see RLS Model.

How PostgREST sets the session

When PostgREST receives a request, it does roughly this on the connection before running your query:

SET LOCAL ROLE authenticated;   -- or anon, or service_role
SET LOCAL request.jwt.claims = '{"sub":"<user-uuid>","role":"authenticated","email":"u@x"}';

To mimic that in your own session, run the same two statements. SET LOCAL scopes the change to the current transaction, so wrap your testing in a BEGIN ... ROLLBACK block and you can iterate without polluting other connections.

Test as the anon role

BEGIN;

SET LOCAL ROLE anon;
-- anon has no JWT claims; auth.uid() returns NULL.

-- Try the read you expect anon to be allowed:
SELECT count(*) FROM public.posts WHERE published = true;
-- Expected: returns counts.

-- Try the read you expect anon to be denied:
SELECT count(*) FROM public.posts WHERE published = false;
-- Expected: returns 0 (RLS hides unpublished posts even from a count).

-- Try a write you expect anon to be denied:
INSERT INTO public.posts (title, body) VALUES ('hi', 'oh no');
-- Expected: ERROR: new row violates row-level security policy

ROLLBACK;

The ROLLBACK undoes the SET LOCAL ROLE and any rows you happened to insert that the policy let through. Use it religiously while iterating. Without it, you’ll accidentally leave the session in an unexpected role and the next query will mislead you.

Test as a specific signed-in user

BEGIN;

SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims =
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';

-- Now auth.uid() returns the uuid above.
SELECT auth.uid();
--                  auth.uid
-- --------------------------------------
--  11111111-1111-1111-1111-111111111111

-- Try the user's own-row read:
SELECT * FROM public.todos WHERE owner_id = auth.uid();
-- Expected: their rows.

-- Try a write that should succeed:
INSERT INTO public.todos (owner_id, title)
VALUES (auth.uid(), 'test')
RETURNING *;
-- Expected: row inserted.

-- Try a write that should fail (assigning to another user):
INSERT INTO public.todos (owner_id, title)
VALUES ('22222222-2222-2222-2222-222222222222', 'evil');
-- Expected: ERROR: new row violates row-level security policy

ROLLBACK;

Test custom claims (for `auth.jwt()` policies)

If you wrote a policy that reads auth.jwt() ->> 'app_role', you set that claim in the same JSON blob:

BEGIN;
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims = '{
  "sub":"11111111-1111-1111-1111-111111111111",
  "role":"authenticated",
  "app_role":"admin"
}';

DELETE FROM public.documents WHERE id = 'some-uuid';
-- Should succeed if your policy checks app_role = 'admin'.

ROLLBACK;

For a non-admin claim, switch the value:

SET LOCAL request.jwt.claims = '{
  "sub":"11111111-1111-1111-1111-111111111111",
  "role":"authenticated",
  "app_role":"member"
}';

DELETE FROM public.documents WHERE id = 'some-uuid';
-- Should fail (or no rows deleted if the WHERE clause matches but RLS denies).

Test as service_role

service_role bypasses RLS, so testing as it confirms the row exists at all (independent of policies):

BEGIN;
SET LOCAL ROLE service_role;

SELECT * FROM public.todos WHERE id = 'some-uuid';
-- Returns the row regardless of who owns it.

ROLLBACK;

If service_role returns a row but authenticated doesn’t, the policy is doing its job. If service_role doesn’t return it either, the row doesn’t exist, and you have a different bug.

A small harness for iteration

Put this in a SQL file and re-run it as you tweak policies:

\set ON_ERROR_STOP on

-- Setup: create test users (run once)
INSERT INTO auth.users (id, email)
  VALUES ('11111111-1111-1111-1111-111111111111', 'alice@example.com')
  ON CONFLICT (id) DO NOTHING;
INSERT INTO auth.users (id, email)
  VALUES ('22222222-2222-2222-2222-222222222222', 'bob@example.com')
  ON CONFLICT (id) DO NOTHING;

-- Test as alice
BEGIN;
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims =
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';

-- ...assertions...

ROLLBACK;

-- Test as bob
BEGIN;
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims =
  '{"sub":"22222222-2222-2222-2222-222222222222","role":"authenticated"}';

-- ...assertions...

ROLLBACK;

In the Studio SQL Editor, run each block as a separate snippet. The editor doesn’t preserve session state across “Run” presses, so you re-SET LOCAL every time.

Common things that aren’t what they seem

Querying auth.users directly returns rows because the auth.users table is owned by supabase_auth_admin, not authenticated. Don’t be fooled: policies on tables you create do apply.
RAISE NOTICE 'auth.uid is %', auth.uid(); doesn’t fail if the role/claims aren’t set; it just prints NULL. If you’re getting unexpected RLS denials, add a SELECT auth.uid(), auth.role(); at the top of your test block to confirm what the session thinks it is.
SET ROLE (without LOCAL) persists across the session. If you accidentally use it and later wonder why your policies are denying everything, run RESET ROLE and you’ll be back to the connecting user.
The Studio SQL Editor runs as postgres (the project owner) by default. Without SET LOCAL ROLE, RLS is bypassed entirely and every query looks like it works. Always SET LOCAL ROLE first.

​How PostgREST sets the session

​Test as the anon role

​Test as a specific signed-in user

​Test custom claims (for auth.jwt() policies)

​Test as service_role

​A small harness for iteration

​Common things that aren’t what they seem

​Next steps

RLS Cookbook

RLS Model