-- RLS Review (rls-review.pages.dev) — read-only.
-- It reads your table settings and access rules (policies). It never reads your rows.
-- Paste into Supabase → SQL Editor → Run, then copy the single result cell.
with t as (
  select c.oid, c.relname as name, c.relkind as kind, c.relrowsecurity as rls,
         coalesce((select json_agg(a.attname order by a.attnum) from pg_attribute a
                   where a.attrelid = c.oid and a.attnum > 0 and not a.attisdropped), '[]'::json) as columns,
         has_table_privilege('anon', c.oid, 'SELECT') as anon_r,
         has_table_privilege('anon', c.oid, 'INSERT,UPDATE,DELETE') as anon_w,
         has_table_privilege('authenticated', c.oid, 'SELECT') as auth_r,
         has_table_privilege('authenticated', c.oid, 'INSERT,UPDATE,DELETE') as auth_w,
         exists (select 1 from unnest(coalesce(c.reloptions, '{}'::text[])) o
                 where o ~* '^security_invoker=(true|on|1|yes)$') as invoker
  from pg_class c join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public' and c.relkind in ('r', 'p', 'v', 'm')
)
select json_build_object(
  'tool', 'rls-review', 'v', 1,
  'tables', (select coalesce(json_agg(json_build_object(
      'name', name, 'kind', kind, 'rls', rls, 'columns', columns,
      'anon_r', anon_r, 'anon_w', anon_w, 'auth_r', auth_r, 'auth_w', auth_w, 'invoker', invoker)), '[]'::json) from t),
  'policies', (select coalesce(json_agg(json_build_object(
      'schema', schemaname, 'table', tablename, 'name', policyname, 'permissive', permissive,
      'roles', roles, 'cmd', cmd, 'using', qual, 'check', with_check)), '[]'::json)
      from pg_policies where schemaname in ('public', 'storage')),
  'functions', (select coalesce(json_agg(json_build_object(
      'name', p.proname, 'args', pg_get_function_identity_arguments(p.oid),
      'anon_x', has_function_privilege('anon', p.oid, 'EXECUTE'),
      'auth_x', has_function_privilege('authenticated', p.oid, 'EXECUTE'))), '[]'::json)
      from pg_proc p join pg_namespace n on n.oid = p.pronamespace
      where n.nspname = 'public' and p.prokind = 'f' and p.prosecdef),
  'buckets', (select coalesce(json_agg(json_build_object('name', name, 'public', public)), '[]'::json)
      from storage.buckets)
)::text as rls_review;