Supabase grants & RLS self-check

-- 1) Which public tables and views exist, is RLS on, and how many policies do they have?
--    Views/materialized views can't have RLS of their own and run with the view owner's
--    rights unless created WITH (security_invoker = true) — review any that anon can read.
select
  c.relname                         as name,
  case c.relkind when 'r' then 'table' when 'p' then 'partitioned table'
                 when 'v' then 'view' when 'm' then 'materialized view'
                 when 'f' then 'foreign table' end as kind,
  c.relrowsecurity                  as rls_enabled,
  c.relforcerowsecurity             as rls_forced,
  (select count(*) from pg_policies p
     where p.schemaname = 'public' and p.tablename = c.relname) as policy_count
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','f')
order by rls_enabled, kind, name;

-- 2) What can anon / authenticated / service_role actually do on each public table?
select table_name, grantee,
       string_agg(privilege_type, ', ' order by privilege_type) as privileges
from information_schema.role_table_grants
where table_schema = 'public'
  and grantee in ('anon', 'authenticated', 'service_role')
group by table_name, grantee
order by table_name, grantee;

-- 3) RED FLAGS: tables/views reachable by anon/authenticated with RLS OFF
select distinct g.table_name, g.grantee
from information_schema.role_table_grants g
join pg_class c on c.relname = g.table_name
join pg_namespace n on n.oid = c.relnamespace and n.nspname = g.table_schema
where g.table_schema = 'public'
  and g.grantee in ('anon', 'authenticated')
  and c.relrowsecurity = false
order by g.table_name;

-- 4) Review policies. Watch for "using (true)" on anon/authenticated.
select tablename, policyname, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, policyname;

-- Safe per-table fix for a NEW table your app uses through supabase-js / the Data API.
-- Replace your_table and user_id. Run grants + RLS + policies together, in ONE migration.

-- 1) Enable Row Level Security
alter table public.your_table enable row level security;

-- 2) Grant anon/authenticated ONLY the privileges you add a policy for below.
--    Here: select + insert, matching the two policies. Many tables need NOTHING for anon.
grant select, insert on public.your_table to authenticated;
-- grant select on public.your_table to anon;  -- only for genuinely public data, AND add a "for select to anon" policy

-- 3) Server code (Edge Functions / your backend using the service_role key) needs its own
--    grant after the change. The line below is Supabase's changelog example (all four
--    privileges). service_role bypasses RLS, so DELETE any privilege your server code
--    doesn't use (e.g. read-only server code needs just: grant select ... to service_role;).
grant select, insert, update, delete on public.your_table to service_role;

-- 4) Only if the id is serial/bigserial (not "generated … as identity"): inserting
--    roles also need the sequence. Check the name with: select pg_get_serial_sequence('public.your_table', 'id');
-- grant usage, select on sequence public.your_table_id_seq to authenticated, service_role;

-- 5) Owner-scoped policies (one per privilege granted in step 2)
create policy "owner can read own rows"
  on public.your_table for select to authenticated
  using ((select auth.uid()) = user_id);

create policy "owner can insert own rows"
  on public.your_table for insert to authenticated
  with check ((select auth.uid()) = user_id);

-- Need update/delete from the browser? Add the grant AND a matching policy together, e.g.:
-- grant update on public.your_table to authenticated;
-- create policy "owner can update own rows" on public.your_table for update to authenticated
--   using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id);

-- Server-only table (admin, logs, payments, secrets): grant to service_role ONLY.
-- No anon/authenticated grants, so the browser can't reach it at all.
alter table public.your_private_table enable row level security;
-- All four privileges shown; remove any privilege your server code doesn't use (service_role bypasses RLS).
grant select, insert, update, delete on public.your_private_table to service_role;
-- (serial/bigserial id only) grant usage, select on sequence public.your_private_table_id_seq to service_role;

You are fixing Supabase Data API access for Supabase's Oct 30, 2026 change
(new tables in the public schema need an explicit GRANT before the Data API —
supabase-js, REST, GraphQL — can reach them). Work table by table. Rules:

1. NEVER run "grant ... on all tables in schema public ..." or
   "alter default privileges ... grant ... to anon". No bulk grants.
2. For EACH table my app reads/writes through supabase-js, write one migration that:
   a) runs: alter table public.<table> enable row level security;
   b) grants anon/authenticated ONLY the privileges that have a matching RLS policy
      (usually authenticated; anon only for data that is genuinely public);
   c) adds RLS policies scoped to the owner, e.g. using ((select auth.uid()) = user_id).
      No "using (true)" policies for insert/update/delete.
3. If server code (Edge Functions, API routes, cron jobs) uses the service_role key on a
   table, grant service_role only the privileges that code actually uses. The full form
   (Supabase's own example) is
   grant select, insert, update, delete on public.<table> to service_role;
   — drop any privilege the server code doesn't need (service_role bypasses RLS).
   Without a grant, new tables return 42501 even for the service role.
4. Tables the browser never needs (admin, logs, payments, secrets): grant NOTHING to
   anon/authenticated; enable RLS; grant only service_role, and access them only from
   server/edge functions.
5. If a table uses a serial/bigserial id (not an identity column), also grant
   usage, select on its sequence to each role that inserts rows.
6. Never put the service_role or sb_secret_ key in client code or VITE_/NEXT_PUBLIC_ env vars.
7. Code that connects to Postgres directly (psql, an ORM, a connection string) is not
   affected by this change; it only concerns the Data API.
8. Before writing code, list every table with: name, roles needing access, privileges,
   and the policy you will add. Wait for my confirmation, then generate the SQL.

From now on, whenever you create a new table in the Supabase public schema, include
in the SAME migration: (1) alter table ... enable row level security; (2) explicit
per-table GRANTs only to the roles that need them — anon/authenticated only for
privileges that have a matching RLS policy, and service_role if server/edge code uses
the table; (3) owner-scoped RLS policies; (4) for serial/bigserial ids, a matching
"grant usage, select on sequence" for the roles that insert.
Never use bulk "on all tables in schema public" grants. If you hit PostgREST error
42501 "permission denied", add a narrow per-table grant for that one table and role
— do not widen grants for the whole schema.