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

Developer Shaameer published a two-user integration test script that verifies Supabase Row Level Security policies by creating two real users, signing in as each, and confirming that one cannot read or update the other's rows, nor can a logged-out client read the table. The script uses the service-role key only for user creation and cleanup, runs via node --env-file=.env test-rls.mjs, and exits non-zero on failure, offering a faster alternative to pgTAP for JavaScript developers.

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 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.