Update: thanks to [@sunnatullah] for catching two gaps in the first version of this script. (1) Checks 2 and 3 could pass even if User B never got a profile row, so they proved nothing. (2) Owner-only RLS filters rows, not columns, so with a table-wide UPDATE grant a user can edit sensitive columns on their own row. Both are fixed below.
An RLS policy can look perfect and still leak. When AI helps write your migrations, a policy can also quietly go missing. The only way to know your database is locked down is to try to break it.
This post shows a two-user test you can run in a minute: create User A and User B, sign in as each, and check that A can’t read or edit B’s data. It works on any table. I’ll use a 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.
Supabase also supports SQL tests with pgTAP, which is great if you like writing SQL. Most JavaScript developers just want a short, readable script they can drop into their repo, so that’s what this is.
What the test checks
Assume a profiles table with RLS enabled, owner-only policies (auth.uid() = id), and a signup trigger that creates one row per user. The script checks that:
- Setup: both users actually have a profile row. If not, the other results mean nothing, so the script stops.
- User A can read their own row.
- User A can’t read User B’s row.
- User A can’t update User B’s row, and B’s row is unchanged afterwards.
- User A can’t edit a protected column on their own row (this needs the column grants below).
- User A can still edit the columns you allow, so check 5 isn’t passing just because everything is blocked.
- A logged-out (anon) client can’t read the table. When you add a table, copy these checks for it. Row policies don’t protect columns This is the gap that’s easy to miss. RLS decides which rows a user can touch. It doesn’t decide which columns. If authenticated has a table-wide UPDATE grant, owner-only policies stop A from editing B, but A can still edit their own row, including any column like is_admin, role, plan or email. The fix is a column-level grant. Allow only the columns users should change: revoke update on public.profiles from authenticated; grant update (full_name, avatar_url) on public.profiles to authenticated; After this, updating full_name still works, and updating anything else fails with 42501 permission denied for table profiles. Make sure your client only sends the allowed columns in updates. The script below tests this using email as the protected column. Change PROTECTED_COLUMN to whatever sensitive column your table has. The script Install the Supabase client and save the code as test-rls.mjs. It needs Node 20.6+ for the --env-file flag. npm install @supabase/supabase-js Security note: this script uses your SUPABASE_SERVICE_ROLE_KEY to create and delete test users. Never put that key in client-side code, and don’t run this against a live production database. Keep it in a local .env file or an isolated CI test project. import { createClient } from '@supabase/supabase-js';
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 PROTECTED_COLUMN = 'email'; // a column users must NOT be able to edit
const ALLOWED_COLUMN = 'full_name'; // a column users may edit
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 {
// Setup: both users must have a profile row, or the checks below prove nothing
const aRow = await admin.from('profiles').select('').eq('id', a.id).single();
const bBefore = await admin.from('profiles').select('').eq('id', b.id).single();
check('Setup: A and B both have profile rows', !!aRow.data && !!bBefore.data);
if (!aRow.data || !bBefore.data) {
throw new Error('Signup trigger did not create a profile row. Fix that before trusting any result.');
}
// 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, and B is unchanged afterwards
const upd = await a.client
.from('profiles')
.update({ [ALLOWED_COLUMN]: 'hacked' })
.eq('id', b.id)
.select();
const bAfter = await admin.from('profiles').select('*').eq('id', b.id).single();
check(
'A cannot update B',
(upd.data ?? []).length === 0 &&
bAfter.data?.[ALLOWED_COLUMN] === bBefore.data[ALLOWED_COLUMN]
);
// 4. A cannot edit a protected column on their own row (expects permission denied)
const prot = await a.client
.from('profiles')
.update({ [PROTECTED_COLUMN]: '[email protected]' })
.eq('id', a.id)
.select();
check('A cannot edit protected column', prot.error?.code === '42501');
// 5. A can still edit an allowed column (so check 4 isn't passing because everything is blocked)
const ok = await a.client
.from('profiles')
.update({ [ALLOWED_COLUMN]: 'Alice' })
.eq('id', a.id)
.select();
check('A can edit allowed column', !ok.error && (ok.data ?? []).length === 1);
// 6. 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);
} catch (e) {
console.error(e.message);
failed++;
} finally {
// cleanup: deleting the auth user also removes the profile (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:
node --env-file=.env test-rls.mjs
Check 4 asserts the exact error code 42501, not just “any error”. That matters: if you point it at a column that doesn’t exist, you get a different error, and the check correctly fails instead of passing for the wrong reason.
Reading the results
• Setup fails: your signup trigger didn’t create the profile row. Fix that first.
• Check 1 fails (A can’t read own row): you likely have a missing grant. A 42501 here usually means no GRANT SELECT ON public.profiles TO authenticated.
• Check 2 or 3 fails (A can touch B): your policy is too permissive. Look for USING (true), a policy that only says to authenticated with no auth.uid() owner check, or a missing WITH CHECK on updates.
• Check 4 fails (A edited a protected column): you have a table-wide UPDATE grant. Use the column-level grant above.
• Check 5 fails: your column grant is too tight, or you revoked update entirely.
• Check 6 fails (anon can read): you’re granting table access to anon, or a policy targets it. Revoke it and keep policies on authenticated.
Verify the test can fail. Temporarily loosen a policy to USING (true) and run the script. Check 3 should fail. A security test that can’t fail isn’t testing anything.
A quick version in the SQL editor
To poke at a policy without a script, you can impersonate a user inside a transaction in the Supabase SQL editor:
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, but a script you run after every migration is what catches regressions.
Run it after every schema change
Policies break quietly: a new table, a changed grant, a migration that drops a policy. Put the script in CI or your pre-deploy checklist, and extend it whenever you add a table.
Want this set up already?
My Express + Supabase starter includes a two-user RLS test like this one, plus auth, a signup trigger, and explicit grants for Supabase’s newer defaults. Disclosure: I’m the author, and it’s a paid starter, not a security audit. The tests cover the profiles table, so any tables you add need their own policies and tests.
https://shaameer.gumroad.com/l/SupabaseExpressjsProductionBackend/Launch10
The script above is free to copy either way. If it catches something in your project, I’d like to hear what.
Top comments (5)
Nice script. Two gaps I'd close before trusting a green run.
Checks 2 and 3 also pass when B never got a profile row. If the signup trigger fails for B,
other.datais[]andbRow.datais null, sonull?.full_name !== 'hacked'is true. Check 1 only proves A's row exists. One line fixes it:The bigger one is A updating their own row. Owner-only policies stop A touching B, but if profiles has something like
is_admin,roleorplan, A can just set it on themselves. RLS only filters rows, not columns. I ran it on plain Postgres 17 with a stubauth.uid()and your policy shape (USING + WITH CHECKauth.uid() = id, table-wide UPDATE grant like Supabase's default):Fix is a column grant:
After that the
full_nameupdate still works and theis_adminone fails with 42501 "permission denied for table profiles". Worth adding as check 5, expecting an error froma.client.from('profiles').update({ is_admin: true }).eq('id', a.id).Both good catches, thank you. You’re right: checks 2 and 3 can pass when B never got a profile, and a table-wide UPDATE lets A edit their own is_admin/role/plan. I’m updating the post with a B-exists precondition and a column-grant check that asserts 42501 so it can’t pass for the wrong reason. Will credit you in the update.
Really useful post. When you test your own app you're always logged in as yourself, so of course it works. That's why these leaks are so easy to miss. The column part was new to me. I thought owner-only RLS was enough, but it doesn't stop someone changing is_admin or plan on their own row. Storage has its own rules too, so a locked table doesn't mean the files are safe. Do you run this on every migration or only before a release?
Thanks, and good point on Storage. Policies on storage.objects are separate, so a locked-down table says nothing about files. Worth its own test.
On CI: honestly I run it manually after each schema change right now. The better habit is wiring it into CI so every migration runs it. That’s the next thing I’d set up.
Makes sense, and running it by hand after every schema change is already better than most setups. When you wire it in, the supabase/setup-cli GitHub Action can run supabase start on the runner, so the two-user script gets a fresh local stack on every PR and never touches your real project's keys.