Supabase RLS check: copy-paste SQL to find exposed tables

1. Run the audit queries

Open Supabase → SQL Editor, paste, and run. Each query is read-only.

-- 1) Tables in the public schema without RLS
select tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by rowsecurity, tablename;

-- 2) Every policy, who it applies to, and its condition
select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

-- 3) Policies that let everyone through (USING true / WITH CHECK true)
select tablename, policyname, cmd, roles
from pg_policies
where schemaname = 'public'
  and (qual = 'true' or with_check = 'true');

-- 4) Storage buckets that serve every file publicly
select id, name, public from storage.buckets order by public desc;

-- 5) Views that may bypass RLS (Postgres 15+: want security_invoker=true)
select c.relname as view, c.reloptions
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'v';

-- 6) SECURITY DEFINER functions callable through the API (run as their owner)
select p.proname
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;

2. Read the results

Query 1: any rowsecurity = false table is reachable with your anon key. Query 3: any row here lets everyone through; fine for truly public content, dangerous for personal data. Query 4: public = true buckets serve files to anyone with the link. Query 5: views without security_invoker=true run with the owner's rights. Query 6: SECURITY DEFINER functions run as their owner; make sure they check permissions.

3. Apply owner-only policies

Template for a table where each row belongs to one user (replace notes and user_id):

alter table public.notes enable row level security;

create policy "Owners can read" on public.notes
  for select to authenticated
  using ((select auth.uid()) = user_id);

create policy "Owners can insert" on public.notes
  for insert to authenticated
  with check ((select auth.uid()) = user_id);

create policy "Owners can update" on public.notes
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy "Owners can delete" on public.notes
  for delete to authenticated
  using ((select auth.uid()) = user_id);

4. Run Supabase's Security Advisor

Dashboard → Advisors → Security Advisor lints your database for issues such as RLS disabled on public tables and SECURITY DEFINER views. Re-run it after every schema change.

5. Test from the outside

Logged out, in a private window, use your app with the browser Network tab open. Any /rest/v1/ response containing other users' data is a leak. Repeat while logged in as a second test user.