RLS is on, but anyone can still write to your Supabase tables

-- Supabase exposure self-check
-- by Asad Ali, auditmyapp.dev
--
-- Read-only. Run it on YOUR OWN project:
--   Supabase dashboard > SQL Editor > New query > paste this > Run
--
-- It only reads Postgres's own system catalogs. It changes nothing.
-- If you expose schemas other than "public" through the API, add them
-- to the exposed_schemas list just below.

with exposed_schemas(name) as (
  values ('public')
),

rls_off as (
  select
    n.nspname || '.' || c.relname as object,
    exists (
      select 1 from pg_depend d
      where d.classid = 'pg_class'::regclass and d.objid = c.oid and d.deptype = 'e'
    ) as from_extension,
    has_table_privilege('anon', c.oid, 'SELECT') as a_r,
    has_table_privilege('anon', c.oid, 'INSERT') as a_i,
    has_table_privilege('anon', c.oid, 'UPDATE') as a_u,
    has_table_privilege('anon', c.oid, 'DELETE') as a_d,
    has_table_privilege('authenticated', c.oid, 'SELECT') as u_r,
    has_table_privilege('authenticated', c.oid, 'INSERT') as u_i,
    has_table_privilege('authenticated', c.oid, 'UPDATE') as u_u,
    has_table_privilege('authenticated', c.oid, 'DELETE') as u_d
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where c.relkind in ('r', 'p')
    and n.nspname in (select name from exposed_schemas)
    and not c.relrowsecurity
),

findings as (

  -- 1. Tables with row-level security switched off
  select
    case
      when from_extension then 'REVIEW'
      when a_i or a_u or a_d then 'CRITICAL'
      when a_r or u_i or u_u or u_d then 'HIGH'
      else 'REVIEW'
    end as severity,
    case when from_extension
      then 'RLS is off (table belongs to an extension)'
      else 'RLS is off'
    end as finding,
    object,
    coalesce(nullif(concat_ws(' | ',
      'signed out: ' || nullif(concat_ws(', ',
        case when a_r then 'read' end, case when a_i then 'insert' end,
        case when a_u then 'update' end, case when a_d then 'delete' end), ''),
      'signed in: ' || nullif(concat_ws(', ',
        case when u_r then 'read' end, case when u_i then 'insert' end,
        case when u_u then 'update' end, case when u_d then 'delete' end), '')
    ), ''), 'no API access granted') as who_can_do_what,
    'With RLS off, every row is open to whoever the table is granted to.' as what_it_means
  from rls_off

  union all

  -- 2. Policies whose condition is simply true
  select
    case
      when p.cmd <> 'SELECT' and p.roles && array['anon', 'public']::name[] then 'CRITICAL'
      when p.cmd <> 'SELECT' then 'HIGH'
      else 'REVIEW'
    end,
    'Policy allows every row',
    p.schemaname || '.' || p.tablename,
    array_to_string(p.roles, ', ') || ': ' || lower(p.cmd) || ' (policy "' || p.policyname || '")',
    'The condition is just true. Fine for data meant to be public, a hole for anything else.'
  from pg_policies p
  where p.schemaname in (select name from exposed_schemas)
    and p.roles && array['anon', 'authenticated', 'public']::name[]
    and (btrim(coalesce(p.qual, '')) = 'true' or btrim(coalesce(p.with_check, '')) = 'true')

  union all

  -- 3. Views and materialized views signed-out users can read, that ignore RLS
  select
    'REVIEW',
    case c.relkind when 'm' then 'Materialized view, no RLS possible' else 'View ignores RLS' end,
    n.nspname || '.' || c.relname,
    'signed out: read',
    case c.relkind
      when 'm' then 'Materialized views cannot have RLS, so anyone who can read it sees every row in it.'
      else 'It runs with its owner''s rights, so the underlying tables'' policies do not apply. Check what it returns.'
    end
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where c.relkind in ('v', 'm')
    and n.nspname in (select name from exposed_schemas)
    and has_table_privilege('anon', c.oid, 'SELECT')
    and not exists (
      select 1 from unnest(coalesce(c.reloptions, '{}'::text[])) o
      where lower(o) in ('security_invoker=true', 'security_invoker=on',
                         'security_invoker=1', 'security_invoker=yes')
    )

  union all

  -- 4. SECURITY DEFINER functions signed-out users can call
  select
    'REVIEW',
    'Function bypasses RLS',
    n.nspname || '.' || p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')',
    'signed out: call',
    'It runs with its owner''s rights, so RLS does not apply inside it. Check what it reads and writes.'
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  where n.nspname in (select name from exposed_schemas)
    and p.prosecdef
    and has_function_privilege('anon', p.oid, 'EXECUTE')

  union all

  -- 5. Storage policies that let signed-out users write files
  select
    'HIGH',
    'Signed-out users can write files',
    'storage.objects',
    array_to_string(p.roles, ', ') || ': ' || lower(p.cmd) || ' (policy "' || p.policyname || '")',
    'Anyone can '
      || case p.cmd
           when 'INSERT' then 'upload'
           when 'UPDATE' then 'change'
           when 'DELETE' then 'delete'
           else 'upload, change or delete'
         end
      || ' files where: ' || coalesce(nullif(btrim(coalesce(p.with_check, p.qual)), ''), 'anywhere')
  from pg_policies p
  where p.schemaname = 'storage'
    and p.tablename = 'objects'
    and p.cmd <> 'SELECT'
    and p.roles && array['anon', 'public']::name[]
)

select severity, finding, object, who_can_do_what, what_it_means
from findings
order by
  case severity when 'CRITICAL' then 1 when 'HIGH' then 2 else 3 end,
  object;

alter table public.orders enable row level security;

create policy "owners read their orders"
  on public.orders for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy "users edit" on public.profiles;

create policy "users edit their own profile"
  on public.profiles for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

alter view public.order_summary set (security_invoker = true);

revoke execute on function public.admin_stats() from public, anon;
grant execute on function public.admin_stats() to authenticated;

create policy "users upload to their own folder"
  on storage.objects for insert to authenticated
  with check (
    bucket_id = 'photos'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );