Migrating an app that only ever used the anon key

A very common Supabase app looks like this: the browser holds the publishable (anon) key, queries tables directly, and every table has row-level security switched off because that is the state a table is created in and turning it on made things stop working.

It ships, it works, and it is completely public. The key is in the JavaScript bundle, the REST endpoint is on the internet, and RLS is the only thing that was ever going to stop a stranger reading users.

This is how to fix it without a weekend of downtime.

First, find out how bad it is

select c.relname as table_name,
       c.relrowsecurity as rls_enabled,
       (select count(*) from pg_catalog.pg_policy p where p.polrelid = c.oid) as policies
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by rls_enabled, c.relname;

Any row with rls_enabled = false is world-readable through the REST API right now. Any row with rls_enabled = true and policies = 0 denies everyone, which is safe and probably unfinished.

Confirm it from outside, so nobody argues about it:

curl "https://<ref>.supabase.co/rest/v1/<table>?select=*&limit=1" \
  -H "apikey: <the anon key from your bundle>"

If that returns data, so would anyone else's terminal.

Order the work by damage

1. Tables with personal data: users, profiles, messages, anything with an email.
2. Tables with money: orders, subscriptions, invoices.
3. Tables that are genuinely public: published posts, public listings. These still need RLS on, with a using (true) or a published_at is not null policy, so "public" is a decision written in SQL instead of an accident.
4. Everything else.

Do one table per migration. A single migration that switches on RLS everywhere will break several features at once, and you will not know which policy is wrong.

For each table

Write down the access rule in words. "A user reads and edits their own rows; support reads all; nobody deletes." Then translate it.

Add the ownership column if it is missing. Plenty of tables in an anon-key-only app have no user_id, because nothing needed one. Backfill it before enabling RLS:

alter table public.notes add column user_id uuid references auth.users(id);
-- backfill from whatever links them today
update public.notes n set user_id = p.id from public.profiles p where n.author_email = p.email;
-- then, once nothing is null
alter table public.notes alter column user_id set not null;

A null owner is a row no policy can match, which means a row that disappears from the app the moment RLS goes on.

Enable and police, in one migration.

alter table public.notes enable row level security;
alter table public.notes force row level security;

create policy "notes: read own"   on public.notes for select to authenticated
  using ((select auth.uid()) = user_id);
create policy "notes: insert own" on public.notes for insert to authenticated
  with check ((select auth.uid()) = user_id);
create policy "notes: update own" on public.notes for update to authenticated
  using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id);

create index if not exists notes_user_id_idx on public.notes (user_id);

Then fix what breaks. The failures are informative:

Symptom	Cause
Empty list where rows exist	No policy for select, or the request ran as anon
Insert fails	Missing with check, or the client is not setting user_id
Update silently affects nothing	using matches no rows
Everything empty for everyone	RLS on, zero policies

The one to watch for is the silent one. const { data } = await db.from(...) without checking error turns "the policy blocked this" into an empty array, which renders as "no results". Route reads through a helper that throws (this repo's unwrap()) so a blocked query is loud.

Move privileged reads to the server

Anon-key-only apps usually have a few queries that genuinely need to cross tenancy: an admin dashboard, a metrics page, a nightly job. Those move to server code with the admin client and their own authorisation check:

const rows = await adminQuery("admin dashboard: cross-tenant revenue totals", (db) =>
  db.from("orders").select("amount_cents, created_at"),
);

Not because RLS cannot express them, but because "read everything" is a different operation from "read mine", and putting it behind a named, justified call site makes it auditable.

Guard the page with requireRole("admin") above it. The admin client bypasses policies, so your check is the only one left.

Do not skip the anonymous case

to authenticated policies mean a signed-out visitor sees nothing. That is usually right, and it is occasionally a regression: a marketing page that read posts with the anon key now renders empty. Give genuinely public data an explicit to anon, authenticated policy with a real predicate.

Ship it safely

• Roll table by table, deploying between each.
• Keep an explain analyze handy: policies add predicates, and a table that was fast unfiltered can be slow filtered without an index.
• After each deploy, run the outside-in curl again. It should return [] or an error, not rows.
• When every table is done, bun run verify fails the build if a future migration adds a table without RLS. That is the ratchet that stops this from happening again.

Checking your work

• The audit query shows rls_enabled = true for every table in public.
• No table has RLS on and zero policies.
• Two accounts in two browsers see only their own data.
• The anon-key curl returns nothing useful for every table.
• Every adminQuery in the codebase has a reason you would defend in review.