The Supabase catalogue audit

-- xlogs catalogue audit. READ-ONLY. Reads PostgreSQL system catalogues only.
-- It selects NOTHING from any application table, so running it cannot expose your data.
-- Paste into the Supabase SQL editor (or your own agent) and send the output back to yourself.

-- 1. Tables without row level security. Anything listed here is readable by whoever holds the
--    anon key, which in a browser app is everybody.
select
  n.nspname  as schema,
  c.relname  as table,
  c.relrowsecurity as rls_enabled,
  c.relforcerowsecurity as rls_forced
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
  and n.nspname not in ('pg_catalog','information_schema','auth','storage','extensions','graphql','realtime','vault','supabase_migrations')
order by c.relrowsecurity asc, n.nspname, c.relname;

-- 2. Every policy, with its actual predicate. Look for "true" as a whole predicate, and for
--    predicates that check only that SOMEONE is signed in rather than WHICH someone.
select
  schemaname as schema,
  tablename  as table,
  policyname as policy,
  roles,
  cmd        as command,
  qual       as using_expression,
  with_check as with_check_expression
from pg_catalog.pg_policies
where schemaname not in ('pg_catalog','information_schema','auth','storage','extensions','graphql','realtime','vault','supabase_migrations')
order by schemaname, tablename, policyname;

-- 3. Tables with row level security ON but NO policy. This denies everyone, which is safe but is
--    usually an accident: it is the shape that breaks an app rather than exposes it.
select n.nspname as schema, c.relname as table
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
  and c.relrowsecurity
  and n.nspname not in ('pg_catalog','information_schema','auth','storage','extensions','graphql','realtime','vault','supabase_migrations')
  and not exists (select 1 from pg_catalog.pg_policy p where p.polrelid = c.oid)
order by 1, 2;

-- 4. Storage buckets marked public. This is bucket METADATA, not the files in them.
select id, name, public, file_size_limit, allowed_mime_types
from storage.buckets
order by public desc, name;

-- 5. Functions that run as their owner rather than the caller. SECURITY DEFINER is legitimate and
--    common; it is also how a policy gets bypassed, so each one is worth being able to explain.
select
  n.nspname as schema,
  p.proname as function,
  p.prosecdef as security_definer
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on n.oid = p.pronamespace
where p.prosecdef
  and n.nspname not in ('pg_catalog','information_schema','extensions','graphql','realtime','vault')
order by 1, 2;

-- 6. What the anon and authenticated roles have been granted directly. Table privileges sit
--    UNDERNEATH row level security: a grant here is what makes a policy the only thing in the way.
select table_schema as schema, table_name as table, grantee, privilege_type
from information_schema.role_table_grants
where grantee in ('anon','authenticated')
  and table_schema not in ('pg_catalog','information_schema','auth','storage','extensions','graphql','realtime','vault','supabase_migrations')
order by grantee, table_schema, table_name, privilege_type;