-- rls_audit.sql
-- Read-only audit of what the public (anon) key can actually reach.
-- Safe to run on production: every statement is a SELECT against the catalog.
--
-- Run the whole file and read the sections in order. Section 1 is the one that
-- finds real leaks; the rest explain them.
--
-- Clickflame - https://clickflame.com

\echo ''
\echo '=== 1. Relations the anon role can SELECT, and what guards them ==='
\echo '    verdict OPEN   -> readable by the published key right now'
\echo '    verdict LOCKED -> RLS on with no policy, fails closed, safe'
\echo '    verdict POLICY -> read the policies in section 3'
\echo ''

select c.relname                                  as relation,
       case c.relkind
         when 'r' then 'table'
         when 'p' then 'partitioned'
         when 'v' then 'view'
         when 'm' then 'matview'
       end                                        as kind,
       c.relrowsecurity                           as rls_on,
       (select count(*) from pg_policy p
         where p.polrelid = c.oid)                as policies,
       case
         when c.relkind in ('v','m')
              and coalesce((c.reloptions::text like '%security_invoker=on%'), false) = false
           then 'OPEN (view bypasses RLS)'
         when c.relkind in ('v','m') then 'POLICY (invoker, inherits base RLS)'
         when c.relrowsecurity = false then 'OPEN (RLS off)'
         when (select count(*) from pg_policy p where p.polrelid = c.oid) = 0
           then 'LOCKED'
         else 'POLICY'
       end                                        as verdict
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where n.nspname = 'public'
   and c.relkind in ('r','v','m','p')
   and has_table_privilege('anon', c.oid, 'SELECT')
 order by verdict, c.relname;

\echo ''
\echo '=== 2. SECURITY DEFINER functions the anon role can EXECUTE ==='
\echo '    trigger-returning functions are excluded: PostgREST will not expose them'
\echo '    fix needs BOTH: revoke ... from public AND revoke ... from anon'
\echo ''

select p.proname                                  as function,
       pg_get_function_identity_arguments(p.oid)  as args,
       p.proacl                                   as acl
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
 where n.nspname = 'public'
   and p.prosecdef
   and p.prokind = 'f'
   and p.prorettype <> 'trigger'::regtype
   and has_function_privilege('anon', p.oid, 'EXECUTE')
 order by p.proname;

\echo ''
\echo '=== 3. Every policy, by table ==='
\echo '    PERMISSIVE policies combine with OR: tightening one of several'
\echo '    tightens nothing. A policy meant to restrict must be RESTRICTIVE.'
\echo ''

select tablename,
       policyname,
       permissive,
       cmd,
       roles::text                                as granted_to
  from pg_policies
 where schemaname = 'public'
 order by tablename, cmd, policyname;

\echo ''
\echo '=== 4. Tables carrying more than one PERMISSIVE policy for one command ==='
\echo '    these are the tables where a restriction can be silently OR-ed away'
\echo ''

select tablename, cmd, count(*) as permissive_policies,
       string_agg(policyname, ', ' order by policyname) as names
  from pg_policies
 where schemaname = 'public'
   and permissive = 'PERMISSIVE'
 group by tablename, cmd
having count(*) > 1
 order by count(*) desc, tablename;

\echo ''
\echo '=== 5. Surviving FOR ALL policies ==='
\echo '    after a per-command split migration this must come back EMPTY.'
\echo '    a non-empty result here is the inverse check that catches a sweep'
\echo '    which matched on policy NAME instead of policy SHAPE.'
\echo ''

select tablename, policyname, permissive, roles::text as granted_to
  from pg_policies
 where schemaname = 'public'
   and cmd = 'ALL'
 order by tablename;

\echo ''
\echo '=== 6. Views without security_invoker ==='
\echo '    each of these runs as its OWNER and ignores RLS on its base tables'
\echo '    fix: alter view <name> set (security_invoker = on);'
\echo ''

select c.relname as view_name,
       pg_get_userbyid(c.relowner) as owner,
       has_table_privilege('anon', c.oid, 'SELECT') as anon_can_select
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where n.nspname = 'public'
   and c.relkind in ('v','m')
   and coalesce((c.reloptions::text like '%security_invoker=on%'), false) = false
 order by anon_can_select desc, c.relname;

\echo ''
\echo '=== done. Section 1 verdict OPEN is the finding. Everything else is why. ==='
\echo '=== Now prove the fix as a real user, not by re-reading the migration:  ==='
\echo '===   set local role authenticated;                                     ==='
\echo "===   set local request.jwt.claims = '{\"sub\":\"<uid>\",\"role\":\"authenticated\"}';"
\echo '===   select count(*) from public.<table>;                              ==='
\echo ''