Supabase fix recipes: what to do after a scanner flags RLS, a leaked service_role key or a public bucket #53368
Replies: 3 comments 2 replies
|
Your three recipes cover the loud failures. The leak I see most often in AI-generated apps passes all three quietly: RLS is on, policies exist, the anon-key check comes back clean — and every signed-in user can read every other tenant's rows, because the policy that applies to the Scanners miss it because the two questions they ask have safe answers: "can Find them instead by reading the predicates, not the boolean: select n.nspname as schema, c.relname as table, p.polname,
pg_get_expr(p.polqual, p.polrelid) as using_expr,
pg_get_expr(p.polwithcheck, p.polrelid) as check_expr,
(select string_agg(r.rolname, ', ')
from unnest(p.polroles) role
join pg_roles r on r.oid = role) as roles
from pg_policy p
join pg_class c on c.oid = p.polrelid
join pg_namespace n on n.oid = c.relnamespace
where n.nspname not in ('pg_catalog', 'information_schema');Any permissive row whose Then confirm it as the role, not with the key you happened to have: run the read as What breaks if you paste the fix without thinking: the tightened predicate One more failure mode worth a recipe of its own: RLS enabled with no policies at all denies by default, but the app sees that as empty lists and failed saves, not permission errors — exactly the shape that turned a routine migration into an auth outage in #53118. Your recipes end with "what breaks"; that one starts with "nothing looks broken", which is harder to debug. |
|
@lokkky-explore — taking you up on the two gaps you listed, since both are procedures rather than recipes and that's the part worth sharing. 1. A create policy "own rows" on public.notes
for select using (auth.uid() = user_id); -- reads scoped
-- but the same table's insert policy (or a single for-all policy) lacks with check:
create policy "own rows w" on public.notes
for insert with check (auth.uid() = user_id); -- this line is the fixWithout the 2. Readers-inventory, as a procedure. The warning exists in every "what can break" section; the procedure version is a query list you run before tightening the predicate, because the readers you forget are not app queries: -- views that read the table (their owner's privileges, RLS of the base table still applies
-- unless the view is security_barrier-less and the invoker is the owner)
select n.nspname as view_schema, v.relname as view_name, pg_get_viewdef(v.oid) as def
from pg_class v
join pg_depend d on d.refobjid = v.oid and d.refclassid = 'pg_class'::regclass
join pg_class t on t.oid = d.objid
join pg_namespace n on n.oid = v.relnamespace
where t.relname = 'notes' and d.classid = 'pg_rewrite'::regclass;
-- functions (esp. SECURITY DEFINER) whose body reads the table — these keep reading
-- with the owner's rights after the policy tightens, so behaviour change is silent here
select n.nspname, p.proname, pg_get_functiondef(p.oid)
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where prosecdef
and pg_get_functiondef(p.oid) ilike '%notes%';Plus two non-catalog steps: enumerate the Happy to open a PR for the write-side one in the repo's recipe style if you'd rather it come with tests — the two-user insert/update cross-tenant case drops straight into the existing suite shape. |
|
@lokkky-explore — correction to the query in my comment above: I ran it end-to-end in a PGlite fixture this time, and two lines of it are wrong as posted. If anyone copied it, this is the fix. 1. and exists (select 1 from pg_roles r
where r.oid = any (p.polroles)
and r.rolname in ('authenticated', 'public'));
2. and p.polcmd in ('r', '*')With those three lines replaced, the query behaves: on a fixture with The fixture is the thing worth keeping — it's a runnable harness now (two users, real JWTs' claims set the way PostgREST sets them, leak proven as a cross-user read, then the same read asserted clean after the fix). If it's useful for the repo's test suite I'll send it as a PR; it drops straight in as a case file plus a script. |
Uh oh!
There was an error while loading. Please reload this page.
If you built on Supabase with Lovable, Bolt, Cursor or Claude Code and a scanner (Supabase's Security Advisor, or one of the community ones) just told you something is open, this repo is the "now what" step:
https://github.com/lokkky-explore/supabase-fix-recipes
Three recipes so far, each in plain English:
sb_secret_key versus a legacyeyJ…service_role JWT (the legacy one can't be revoked on its own)storage.foldername, and switching to signed URLsEach one ends with what breaks if you paste the fix without thinking. Turning RLS on with no policies returns empty lists and failed saves in your own app, and making a bucket private kills every public link you already stored. That's the part I've seen AI-built apps trip on most.
Free, CC BY 4.0. Issues welcome if a recipe is wrong or a finding is missing. Disclosure: I run a small studio that also does these fixes as a paid service; the README says so once at the bottom.
All reactions