-- Shiproof · Supabase self-check
-- Read-only: it reads Postgres's own catalog. It never reads your rows or changes anything.
-- Supabase → SQL Editor → New query → paste → Run.

with tbl as (
  select c.oid, c.relname as name, c.relrowsecurity as rls,
         has_table_privilege('anon', c.oid, 'select') as anon_read,
         has_table_privilege('anon', c.oid, 'insert,update,delete') as anon_write,
         has_table_privilege('authenticated', c.oid, 'select,insert,update,delete') as user_any
  from pg_class c join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public' and c.relkind in ('r', 'p')
),
found as (
  -- 1. Tables with row-level security switched off
  select case when anon_read or anon_write then 'CRITICAL' else 'HIGH' end as severity,
         'table ' || name as object,
         case when anon_read or anon_write
              then 'RLS is off: anyone with your public anon key can '
                   || case when anon_write then 'read and change' else 'read' end || ' every row'
              else 'RLS is off: every signed-in user can reach every row' end as issue
  from tbl
  where not rls and (anon_read or anon_write or user_any)

  union all
  -- 2. Policies that let everyone through
  select case when p.cmd in ('UPDATE', 'DELETE', 'ALL') then 'HIGH' else 'REVIEW' end,
         'table ' || p.tablename || ' · policy "' || p.policyname || '"',
         p.cmd || ' is allowed for '
         || case when p.roles && array['public', 'anon']::name[]
                 then 'anyone, even logged-out visitors'
                 else 'every signed-in user, on every row' end
         || case p.cmd when 'SELECT' then ' (fine only for truly public data)'
                       when 'INSERT' then ' (fine for a waitlist, not for orders or roles)'
                       else '' end
  from pg_policies p
  where p.schemaname = 'public' and p.permissive = 'PERMISSIVE'
    and ((p.cmd = 'INSERT' and p.with_check = 'true') or (p.cmd <> 'INSERT' and p.qual = 'true'))

  union all
  -- 3. Views the anon key can read: they run with the owner's rights and skip RLS
  select 'HIGH',
         case c.relkind when 'm' then 'materialized view ' else 'view ' end || c.relname,
         'skips RLS and the anon key can read it: check what it exposes, or recreate it with security_invoker = true'
  from pg_class c join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public' and c.relkind in ('v', 'm')
    and has_table_privilege('anon', c.oid, 'select')
    and coalesce(array_to_string(c.reloptions, ','), '') !~* 'security_invoker=(true|on|yes|1)'

  union all
  -- 4. SECURITY DEFINER functions anyone can call through /rest/v1/rpc
  select 'REVIEW', 'function ' || p.proname || '()',
         'runs with owner rights, skips RLS, and logged-out visitors can call it: make sure it checks auth.uid()'
  from pg_proc p join pg_namespace n on n.oid = p.pronamespace
  where n.nspname = 'public' and p.prokind = 'f' and p.prosecdef
    and p.prorettype <> 'trigger'::regtype
    and has_function_privilege('anon', p.oid, 'execute')

  union all
  -- 5. Public storage buckets
  select 'REVIEW', 'storage bucket ' || name,
         'public: anyone with a file link can download it (fine for avatars, not for documents or invoices)'
  from storage.buckets
  where public
)
select severity, object, issue
from (
  select * from found
  union all
  select 'OK', '-', 'Nothing open found. Login flows, server functions and payments still need a human review.'
  where not exists (select 1 from found)
) r
order by array_position(array['CRITICAL', 'HIGH', 'REVIEW', 'OK'], severity), object;