-- Rowpass grants audit for a Supabase project
-- Lists every table in the public schema with the four privileges the Data API uses,
-- per API role, next to its row level security state. Read-only: it changes nothing.
-- Run it in the SQL Editor. Free to copy, change and share.
select
  c.relname as table_name,
  concat_ws(', ',
    case when has_table_privilege('anon', c.oid, 'select') then 'select' end,
    case when has_table_privilege('anon', c.oid, 'insert') then 'insert' end,
    case when has_table_privilege('anon', c.oid, 'update') then 'update' end,
    case when has_table_privilege('anon', c.oid, 'delete') then 'delete' end) as anon,
  concat_ws(', ',
    case when has_table_privilege('authenticated', c.oid, 'select') then 'select' end,
    case when has_table_privilege('authenticated', c.oid, 'insert') then 'insert' end,
    case when has_table_privilege('authenticated', c.oid, 'update') then 'update' end,
    case when has_table_privilege('authenticated', c.oid, 'delete') then 'delete' end) as authenticated,
  concat_ws(', ',
    case when has_table_privilege('service_role', c.oid, 'select') then 'select' end,
    case when has_table_privilege('service_role', c.oid, 'insert') then 'insert' end,
    case when has_table_privilege('service_role', c.oid, 'update') then 'update' end,
    case when has_table_privilege('service_role', c.oid, 'delete') then 'delete' end) as service_role,
  c.relrowsecurity as rls_on,
  (select count(*) from pg_policy p where p.polrelid = c.oid) as policies,
  case
    when not (has_table_privilege('anon', c.oid, 'select, insert, update, delete')
           or has_table_privilege('authenticated', c.oid, 'select, insert, update, delete'))
      then 'closed to the browser: anon and authenticated get 42501'
    when not c.relrowsecurity
      then 'open: granted and row level security is off'
    when not exists (select 1 from pg_policy p where p.polrelid = c.oid)
      then 'granted, RLS on, no policy: requests return no rows'
    else 'granted, RLS on: read the policies'
  end as reading
from pg_class c
where c.relnamespace = 'public'::regnamespace
  and c.relkind in ('r', 'p')
order by c.relname;