Audit your Supabase project from the SQL editor
Five catalog queries that show what your public key can reach, and how to read each result.
Paste five queries into the Supabase SQL editor and you will know more about your project than any compliance certificate tells you. None of them reads your data. They read the Postgres catalog, so they are safe to run on production.
I'm Denys, a Forward Deployed Engineer, and I fix a lot of Supabase apps. This article was written with AI assistance and checked by me. I ran all five on throwaway local Postgres databases (14.17 and 17.10) with test tables before writing this, and the expected hits came back.
What are you looking for?
Four table settings decide what the public key gets back. I made one table of three fake orders on a local stack (CLI 2.109.1, Postgres 17.6, PostgREST 14.14), sent the same request with the publishable key, and changed one setting at a time:
- RLS off: all 3 rows, and an insert returned
201 Created. - RLS on, no policy:
[]. - RLS on, policy
using (true): all 3 rows. - No grant for
anon:42501 permission denied.
Each query below hunts for one of those, plus the two doors that skip policies completely, views and functions.
1. Tables with RLS off
select c.relname
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind in ('r', 'p') and not c.relrowsecurity;
Any row here means the public key can read the table, and write to it too. Turn RLS on and add a policy in the same step. If you only turn it on, your own app gets [] as well.
2. Policies that let everybody in
select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
and (qual = 'true' or with_check = 'true'
or qual ilike '%is not null%' or with_check ilike '%is not null%');
Postgres stores the policy text normalised, for example (auth.uid() IS NOT NULL), so the match ignores case. It is a quick filter, not a proof: read the full pg_policies output once.
true is fine on a menu and wrong on orders. auth.uid() is not null means any signed-in user, which on a public sign-up page means anyone. Replace both with a check on the row's owner or company.
3. Grants nobody asked for
select grantee, table_name, privilege_type
from information_schema.role_table_grants
where table_schema = 'public' and grantee in ('anon', 'authenticated')
order by table_name;
Grants and policies are two layers, and adding a policy takes no grant back. On one project the supabase/postgres 17.6.1 image had given anon and authenticated TRUNCATE, REFERENCES, TRIGGER and MAINTAIN on every table in public. No policy applies to TRUNCATE. The Data API has no TRUNCATE endpoint, so it is not a one-request wipe, but no client role needs it. Revoke first, then grant back only what the app uses:
revoke all on public.orders from anon, authenticated;
grant select, insert, update on public.orders to authenticated;
Why do these land on your side?
Supabase's shared responsibility model lists access management for the database and its tables as always yours. The platform half is audited. I read its SOC 2 Type 2 and ISO 27001 status on 23 October 2026, and put it next to the settings you control in my answer to is Supabase secure. The short version is that the certificates do not transfer to a table you left open.
4. Views that skip RLS
select c.relname, c.reloptions
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
and c.relkind = 'v'
and not coalesce(c.reloptions @> array['security_invoker=true'], false);
A view runs with its owner's rights, and in Supabase the owner is usually postgres. A view over a protected table can hand out every row the policies hold back. On Postgres 15 and later:
alter view public.customer_summary set (security_invoker = true);
5. SECURITY DEFINER functions
select p.proname, p.proconfig
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;
Every function in public can be called through /rpc by any role with EXECUTE, and RLS does not apply to functions. A proconfig of null means the function has no search_path set. I give each one set search_path = '' and grant EXECUTE only to the role that needs it.
One gotcha before you revoke. Postgres checks EXECUTE on every function used by every policy that applies to the role, even in a branch that never runs. When anon lost EXECUTE on an is_admin() helper, every anonymous read failed with 42501. Check which policies call the function first.
What SQL cannot show you
The secret key bypasses every policy, so search your built JavaScript for it and rotate it if it ever shipped. Storage buckets and their policies are separate from all of the above. And no query knows which rows belong to whom. For that, make two users in two companies and try to read company 2's records as user 1.
One date matters for the grants query. From 30 October 2026 Supabase stops giving new tables in existing projects Data API grants automatically, so a forgotten table returns 42501 instead of its rows. Existing tables keep what they have, so an empty result in query 3 is still yours to earn.
If one of these queries returns a row you cannot explain, paste it in the comments and I will tell you how I would read it.
Comments
Join the discussionNo comments yet. Be the first to comment.