How to test Supabase RLS with two users (script included) By Shaameer

An RLS policy can look perfect and still leak. Especially with AI writing half our migrations these days, broken permissions are the silent killer of MVPs.

The only way to know if your database is actually locked down is to try to break it. This post shows a two-user integration test you can run in a minute: create User A and User B, sign in as each, and verify that A can’t read or edit B’s data.

It works on any table. I’ll use a standard profiles table with owner-only policies as the example.

Why “it works in my app” proves nothing

When you test your own app, you are always logged in as the same user, looking at your own rows. Of course it works. A leak only shows up when a different user requests your data, and that never happens while you’re building.

While Supabase offers native SQL testing via pgTAP, most JavaScript developers just want a fast, readable script they can drop straight into their repo without writing SQL unit tests. The test is simple: spin up two real users, and have one try to touch the other.

What the test checks

Assume a profiles table with RLS enabled, owner-only policies (auth.uid() = id), and a standard signup trigger that creates a row per user. The test guarantees that:

const url = process.env.SUPABASE_URL;

const anonKey = process.env.SUPABASE_ANON_KEY;

const serviceKey = process.env.SUPABASE_SERVICE_ROLE_KEY; // Local/CI test only

const admin = createClient(url, serviceKey, { auth: { persistSession: false } });

async function makeUser(label) {

  const email = `rls-test-${label}-${Date.now()}@example.com`;

  const password = 'Test-password-123!';

const { data, error } = await admin.auth.admin.createUser({

    email,

    password,

    email_confirm: true,

  });

  if (error) throw error;

const client = createClient(url, anonKey, { auth: { persistSession: false } });

  const { error: signInError } = await client.auth.signInWithPassword({ email, password });

  if (signInError) throw signInError;

return { id: data.user.id, client };

}

let failed = 0;

function check(name, ok) {

  console.log(ok ? `PASS  ${name}` : `FAIL  ${name}`);

  if (!ok) failed++;

}

const a = await makeUser('a');

const b = await makeUser('b');

try {

  // 1. A can read their own row

  const own = await a.client.from('profiles').select('*').eq('id', a.id);

  check('A can read own profile', !own.error && own.data.length === 1);

// 2. A cannot read B's row

  const other = await a.client.from('profiles').select('*').eq('id', b.id);

  check('A cannot read B', !other.error && other.data.length === 0);

// 3. A cannot update B's row

  const upd = await a.client

    .from('profiles')

    .update({ full_name: 'hacked' })

    .eq('id', b.id)

    .select();

  const bRow = await admin.from('profiles').select('full_name').eq('id', b.id).single();

check(

'A cannot update B',

    (upd.data ?? []).length === 0 && bRow.data?.full_name !== 'hacked'

  );

// 4. Logged-out client cannot read the table

  const anon = createClient(url, anonKey, { auth: { persistSession: false } });

  const anonRead = await anon.from('profiles').select('*');

  check('anon cannot read profiles', !!anonRead.error || (anonRead.data ?? []).length === 0);

} finally {

  // Cleanup: deleting the auth user also removes the profile via ON DELETE CASCADE

  await admin.auth.admin.deleteUser(a.id);

  await admin.auth.admin.deleteUser(b.id);

}

console.log(failed === 0 ? '\nAll checks passed' : `\n${failed} check(s) failed`);

process.exit(failed === 0 ? 0 : 1);

Add your keys to a local .env file and run:

Bash

node --env-file=.env test-rls.mjs Reading the results

• Check 1 fails (A can’t read own row): You likely have a missing table grant, or your signup trigger failed to create the profile row. A 42501 permission denied error here usually means you are missing GRANT SELECT ON public.profiles TO authenticated;.

• Check 2 or 3 fails (A can touch B): Your policy is too permissive. Check for accidental USING (true) rules, policies granted to authenticated without an auth.uid() owner check, or a missing WITH CHECK clause on updates.

• Check 4 fails (anon can read): You are granting table access to the anon role or have a policy targeting it. Revoke public/anon grants and restrict policies strictly to authenticated.

Pro tip: Verify your test works by temporarily loosening a policy to USING (true) and running the script. Check 2 should immediately fail. A security test that can't fail isn't testing anything.

A quick alternative in the SQL editor

If you just want to debug a policy quickly without running a Node script, you can impersonate a user inside a transaction block in the Supabase SQL Editor: SQL

begin; set local role authenticated;

  select set_config('request.jwt.claims', '{"sub": ""}', true);

-- Should return only User A's row

  select id from public.profiles;

rollback;

Replace with a real user ID from your Auth dashboard. It’s fast for ad-hoc debugging, though an automated script you run after every migration is what actually prevents regressions.

Run it after every schema change

The real value of this script is running it every time your schema changes. Policies break quietly—a new table, a modified grant, or an AI-generated migration that accidentally drops a constraint. Drop this script into your CI pipeline or pre-deploy checklist, and expand it every time you ship a new table.

Want this architecture ready out of the box?

If you want this exact two-user RLS testing harness pre-baked into a production-ready Express + Supabase starter—complete with authentication, signup triggers, and explicit secure grants for Supabase’s latest defaults—you can grab my starter kit below. 👉

https://shaameer.gumroad.com/l/SupabaseExpressjsProductionBackend/Launch10

(Full disclosure: I'm the author—it's a paid boilerplate designed to save you setup hours, not a formal security audit.)