Free migration template and grants audit

Download the template (.sql) Download the audit query (.sql)

Plain text, no sign-up. Free to copy, change and share. Linking to this page is welcome.

1. The audit query

Read-only. It lists every table in public with the privileges each API role holds, whether row level security is on, how many policies exist, and a one-line reading.

-- 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;

We ran the query in our test against one table of each kind and it returned these four readings.

2. The migration template

Supabase's advice for anyone who runs their own migrations: bundle the grant statements with the row level security and policy statements in the same migration. The template does that for the three common shapes.

-- Rowpass migration template: one new Supabase table, exposed on purpose
-- Grant, row level security and policies in the same migration as the table,
-- so the table works after every `supabase db reset` and on every new project.
-- Replace your_table and user_id, keep ONE of the three blocks, delete the other two.
-- Free to copy, change and share. Read it before you run it.

create table public.your_table (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id),
  created_at timestamptz not null default now()
);

-- ============================================================
-- A) OWNER ONLY: each signed-in user reads and writes their own rows
-- ============================================================
-- public.your_table: owner only
alter table public.your_table enable row level security;
revoke all on table public.your_table from anon, authenticated;
grant select, insert, update, delete on table public.your_table to authenticated;
grant select, insert, update, delete on table public.your_table to service_role;
drop policy if exists your_table_select_own on public.your_table;
create policy your_table_select_own on public.your_table
  for select to authenticated
  using ( (select auth.uid()) = user_id );
drop policy if exists your_table_insert_own on public.your_table;
create policy your_table_insert_own on public.your_table
  for insert to authenticated
  with check ( (select auth.uid()) = user_id );
drop policy if exists your_table_update_own on public.your_table;
create policy your_table_update_own on public.your_table
  for update to authenticated
  using ( (select auth.uid()) = user_id )
  with check ( (select auth.uid()) = user_id );
drop policy if exists your_table_delete_own on public.your_table;
create policy your_table_delete_own on public.your_table
  for delete to authenticated
  using ( (select auth.uid()) = user_id );

-- ============================================================
-- B) PUBLIC READ: anyone can read, only your server can write
-- ============================================================
-- public.your_table: public read
alter table public.your_table enable row level security;
revoke all on table public.your_table from anon, authenticated;
grant select on table public.your_table to anon;
grant select on table public.your_table to authenticated;
grant select, insert, update, delete on table public.your_table to service_role;
drop policy if exists your_table_read_all on public.your_table;
create policy your_table_read_all on public.your_table
  for select to anon, authenticated
  using ( true );

-- ============================================================
-- C) PRIVATE: only your server (service_role) can reach it
-- ============================================================
-- public.your_table: private
alter table public.your_table enable row level security;
revoke all on table public.your_table from anon, authenticated;
grant select, insert, update, delete on table public.your_table to service_role;
-- no policies: with RLS on and no policy, anon and authenticated read nothing.
-- service_role bypasses RLS. Keep its key on your server only.

-- If the id column is serial or bigserial, inserts also need the sequence:
-- grant usage, select on sequence public.your_table_id_seq to authenticated, service_role;

The three blocks are written by the same code as the tool below, and that SQL is run against Postgres in our tests. For your real tables and column names, paste your schema into the tool: one table is free, the whole schema as one file with a rollback is $9.

Sources

• Supabase changelog: Breaking Change: Tables not exposed to Data and GraphQL API automatically (published 2026-04-28), read 2026-11-10
• Supabase docs: Row Level Security, read 2026-11-10
• Supabase docs: Securing your API, read 2026-11-10
• Our own test: the behaviour the reference pages describe, run against PGlite, read 2026-11-10