# Migrating an app that only ever used the anon key

> Tables with RLS off are public. Turn it on table by table behind a feature switch, write the policies, and fix the queries the policies break, in that order.

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

```sql
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:

```bash
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:

```sql
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.**

```sql
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:

```ts
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com