Test RLS with a second account before you ship

Updated Oct 31, 20267 min read

To test RLS with a second account, create a throwaway user B, sign in as B and ask for user A's rows. The only passing answer is zero rows. A policy that checks "is someone signed in" passes every signed-out test and still shows B everything A owns.

Why a signed-out test misses this leak

Most RLS checks ask one question: can a stranger with the public key read the table? That catches tables with RLS off. It misses the policy that lets every signed-in user read every row.

These three policies all look like protection. Each one only checks that a request comes from somebody who is signed in:

• to authenticated using (true)
• using (auth.role() = 'authenticated')
• using (auth.uid() is not null)

None of them asks whose row it is. A signed-out request gets nothing, so the outside test passes. User B signs in and gets everything.

Getting the authenticated role is easy. Supabase's Auth settings include "Allow new users to sign up". While it is on, anyone with your public key can call the signup API and get an account, even if your app has no signup page. Supabase also says an anonymous user assumes the authenticated role, so if you turn on anonymous sign-ins, a visitor gets that role without an email at all.

The fix: check ownership in every SELECT policy

A read policy for private data has to compare the row's owner with the user making the request. In Supabase that is auth.uid(), which returns the ID of the signed-in user. Drop the login-only policy first, or it keeps winning.

create policy "Logged in users can read notes"
  on public.notes for select
  to authenticated
  using (true);

drop policy "Logged in users can read notes"
  on public.notes;

create policy "Users read own notes"
  on public.notes for select
  to authenticated
  using ((select auth.uid()) = user_id);

Some data really is shared, such as a public profile or a team's projects. Decide that per table and write it down. For team data, check membership in the policy. Our guide on multi-tenant data isolation shows that pattern, and common Supabase RLS mistakes covers the other traps, like trusting user_metadata.

If the clause never mentions the row's owner, every account can read it.

Test RLS with a second account in the browser

You need two accounts you own, A and B, and at least one row for each. An empty table returns zero rows whatever the policy says, so seed both first.

The quickest check uses your app as it is:

1. Sign in as A in a normal window and open a page that shows A's data. Copy the record ID from the URL or the page.
2. Open a private window, sign in as B and open the same URL with A's ID.
3. Open DevTools, Network tab, and filter by rest/v1. Click the request for that table and read the response.

A page that shows an error but a response that holds A's row is still a leak. The browser gets the data and only the UI hides it. That swap-the-ID test also catches IDOR bugs in API routes, which happen in your own server code rather than in RLS.

Count what user B can see with curl

The browser test checks one page. A count checks the whole table. Right-click the request in the Network tab and use "Copy as cURL" to get B's token, or sign in with curl as shown in how to test Supabase RLS from outside.

URL="https://YOUR_PROJECT_REF.supabase.co"
KEY="YOUR_PUBLISHABLE_KEY"
TOKEN_B="USER_B_ACCESS_TOKEN"

# 1. A's rows, asked for by B: must be 0
curl -s -o /dev/null -D - \
  "$URL/rest/v1/notes?select=id&user_id=eq.USER_A_ID" \
  -H "apikey: $KEY" -H "Authorization: Bearer $TOKEN_B" \
  -H "Prefer: count=exact" | grep -i content-range

# 2. Every row B can see: must equal B's own rows
curl -s -o /dev/null -D - "$URL/rest/v1/notes?select=id" \
  -H "apikey: $KEY" -H "Authorization: Bearer $TOKEN_B" \
  -H "Prefer: count=exact" | grep -i content-range

Prefer: count=exact makes PostgREST, the API layer Supabase uses, return a Content-Range header. The number after the slash is the row count B can see. For request 1 it must be 0. For request 2 it must match the rows you seeded for B.

Lock it in with a pgTAP test as user B

A manual test passes once. A test file runs after every migration, which is when an AI agent tends to add a new policy. Supabase's docs switch identity inside a test with set local role and set local request.jwt.claim.sub. Create the file with supabase test new notes_second_account and run supabase test db:

begin;
select plan(3);

insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', 'a@example.com'),
  ('22222222-2222-2222-2222-222222222222', 'b@example.com');
insert into notes (user_id, body) values
  ('11111111-1111-1111-1111-111111111111', 'A private'),
  ('22222222-2222-2222-2222-222222222222', 'B private');

set local role authenticated;

-- user B
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
select is_empty(
  $$ select * from notes
     where user_id = '11111111-1111-1111-1111-111111111111' $$,
  'user B sees none of user A''s notes'
);
select results_eq(
  $$ select count(*)::int from notes $$,
  array[1],
  'user B sees only their own note'
);

-- user A, so an empty result is not a false pass
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$ select body from notes $$,
  array['A private'],
  'user A still reads their own note'
);

select * from finish();
rollback;

Adjust the column names to your table. If the inserts fail, check grants and the table's required columns first. The guide to enabling RLS when you create tables shows the grants this test expects.

How to run the second-account test, step by step

Do this once per table that holds user data. Write down each table and the result.

1. Create user B

   Add a user in the dashboard under Authentication, or sign up through your app with a throwaway email you control.

2. Seed one row each

   Create one row as A and one as B through the app. Note A's user ID and one of A's row IDs.

3. Swap the ID as B

   In a private window, sign in as B and open A's record URL. Read the API response in DevTools, not just the page.

4. Count with B's token

   Run both curl requests. A's rows must be 0, and B's total must equal B's own rows.

5. Fix and lock it in

   Drop login-only SELECT policies, add owner checks, then add the pgTAP file and run supabase test db after every migration.

Repeat the count after each deploy that touches the database. A new policy from a later prompt can undo the fix.

Fix prompt you can paste

Give this to Cursor or Claude Code. It asks for file and line output so you can check each claim before you accept changes.

Audit every SQL migration in supabase/migrations/ and any other SQL in this repo.
List every SELECT (or FOR ALL) policy on tables that hold per-user data.
For each one, report the file, line, table and USING expression. Flag it if it only checks that a user is signed in, for example:
- TO authenticated USING (true)
- USING (auth.role() = 'authenticated')
- USING (auth.uid() IS NOT NULL) with no owner or membership check
Also flag tables that have both an owner policy and a login-only policy. Permissive policies are combined with OR, so the wide one wins.
Then fix it in a NEW migration:
1. DROP the login-only policies.
2. Add owner-scoped SELECT policies: TO authenticated USING ((select auth.uid()) = user_id), or a membership check for team data.
3. Ask me before treating any table as intentionally shared.
4. Add supabase/tests/<table>_second_account.test.sql. It signs in as user B with set local request.jwt.claim.sub, asserts B sees 0 of user A's rows, and asserts A still sees A's own row.
Run supabase test db and show me the output. Do not edit applied migrations.

Read the diff. If the agent makes a test pass by loosening a policy, reject it and ask again.

What Leakcade checks

Leakcade reads the SQL migrations in your repo. One check looks for exactly this bug: a SELECT policy that only checks login, on a table that has an owner column or an ownership policy elsewhere. A hit comes with the table, the policy and a fix prompt like the one above.

We tested the check on 58 public repos and read every hit by hand. It flagged 3 tables. One was a clear leak, one was likely, and one was a public profile table meant to be readable, so tables with public in the name are now skipped. A repo scan only sees migrations, so policies you added by clicking in the dashboard still need the second-account test.

Further reading

1. Supabase docs: Row Level Security
2. Supabase docs: Auth general configuration
3. Supabase docs: Anonymous sign-ins
4. PostgreSQL docs: Row Security Policies
5. PostgREST docs: Pagination and count

FAQ