Lock Down Supabase With Row Level Security

Why the Anon Key Changes Everything

Supabase puts a REST API in front of your Postgres database. Your web app talks to it with the anon key. That key is in your page source, where anyone can copy it. That is by design. The key is not a secret. It is a name tag.

So the real lock is row level security, or RLS. With RLS on, Postgres checks a policy for every row before it hands it over. With RLS off, the API returns every row to anyone holding the anon key, if that role has a grant on the table. And by default, new tables in the public schema come with that grant.

RLS fails quietly. A table with it off does not throw an error. The app works. The screens look right. The data is simply readable by strangers. That is why this guide starts by asking the database what is true, not by reading the code.

Find Every Open Table

Run this in the SQL editor. It lists every table and view in the public schema that the anon role can select from, with whether RLS is on and how many policies it has.

-- Tables and views the public anon key can read, and whether RLS stands guard.
select c.relname,
       c.relkind,                 -- r table, p partitioned, v view, m matview
       c.relrowsecurity as rls_on,
       (select count(*) from pg_policy p where p.polrelid = c.oid) as policies
  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')
   and has_table_privilege('anon', c.oid, 'SELECT')
 order by c.relrowsecurity, c.relname;

Read the result row by row.

• A table with RLS on and no policies. Safe. RLS with no policy lets no one in, except the service role.
• A table with RLS on and some policies. Read the policies. Some may let in more than you think.
• A table with RLS off. Open to the public key. Fix it today.
• A view. Suspect. The RLS column tells you nothing for views. See below.

The tables people forget are the ones nobody thinks of as tables. A backup made before a migration. A staging table from an import. A copy made at 2am to test something. These are often built with create table as select, which makes a table with RLS off.

Enable RLS on Every Table

This block turns RLS on for every table in the public schema that does not have it yet. It is safe to run more than once.

-- Turn RLS on for every table in public that does not have it yet.
do $$
declare
  r record;
begin
  for r in
    select c.oid::regclass as tbl
      from pg_class c
      join pg_namespace n on n.oid = c.relnamespace
     where n.nspname = 'public'
       and c.relkind in ('r', 'p')
       and not c.relrowsecurity
  loop
    execute format('alter table %s enable row level security', r.tbl);
    raise notice 'RLS enabled on %', r.tbl;
  end loop;
end
$$;

Be ready for screens to go empty. A table that had RLS off and no policies will now return nothing to your app. That is the correct first state. Write a policy for each table the app really needs, then add it back one table at a time. Your server code that uses the service role key is not affected, because the service role skips RLS.

Revoke the Anon Role

RLS is the lock. Grants are the door. Most apps need signed in users to read data and nobody else. In that case the anon role should have no grants at all. Take them away, and stop future tables from giving them back.

-- The anon key needs nothing in most apps. Take it all back.
revoke all on all tables    in schema public from anon;
revoke all on all sequences in schema public from anon;

-- And stop new tables from handing it back by default.
alter default privileges for role postgres in schema public
  revoke all on tables from anon;
alter default privileges for role postgres in schema public
  revoke all on sequences from anon;

The last two lines matter as much as the first two. Without them, the next table you create gets the anon grant again. If some tables really are public, such as a list of services, grant select on just those tables to anon and give each one a read only policy for anon.

Write Policies That Say Who Owns a Row

The most common policy says a user can see and change only their own rows. Write one policy per command and name each one for what it does. Scope every policy to authenticated, so it never applies to anon by accident.

create table public.notes (
  id         bigint generated always as identity primary key,
  user_id    uuid not null default auth.uid() references auth.users (id),
  body       text not null,
  created_at timestamptz not null default now()
);
alter table public.notes enable row level security;

create policy notes_select_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 policy notes_delete_own on public.notes
  for delete to authenticated
  using ((select auth.uid()) = user_id);

create index on public.notes (user_id);

Two small habits help. Wrap auth.uid() in a sub select as shown. Postgres then works it out once per query, not once per row, which keeps big tables fast. And index the column the policy checks.

Remember that permissive policies add up with OR. If a table has two select policies, a row is visible if either one allows it. A narrow policy next to a wide one restricts nothing.

Policies for Teams and Accounts

Most business apps are not one user per row. A shop has an owner and staff who all need to see the same jobs. The usual pattern is a members table that links users to accounts, and policies that check it.

create table public.members (
  account_id uuid not null,
  user_id    uuid not null references auth.users (id),
  role       text not null check (role in ('owner', 'staff')),
  primary key (account_id, user_id)
);
alter table public.members enable row level security;

create policy members_see_own on public.members
  for select to authenticated
  using ((select auth.uid()) = user_id);

create policy jobs_account_read on public.jobs
  for select to authenticated
  using (account_id in (
    select m.account_id from public.members m
     where m.user_id = (select auth.uid())
  ));

The members table has its own policy, so a user can only see which accounts they belong to. The jobs policy then lets a user read any job in an account they are a member of. Index account_id on every table that uses this check, and the user_id column on members.

If staff should see less than the owner, do not add a second permissive policy. It will be joined with OR and restrict nothing. Put the role check inside the one policy, or add a policy marked as restrictive, which is joined with AND.

Test as a Real User

A policy that reads right is not a policy that works. Prove it. In the SQL editor you run as postgres, which skips RLS. So switch to the role your app uses and set the same claims a real sign in would set. Do it inside a transaction and roll back, so nothing sticks.

begin;
  set local role authenticated;
  set local request.jwt.claims =
    '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';

  select auth.uid();                 -- should print the id above
  select count(*) from public.notes; -- only this user's rows
rollback;

begin;
  set local role anon;
  set local request.jwt.claims = '{"role":"anon"}';
  select count(*) from public.notes; -- expect permission denied
rollback;

Use a real user id from your auth users table. Run the count for one user of every role your app has, such as owner, staff and customer. Write down the numbers before and after any change. A change that empties a working screen is as bad as the hole it closes, and it will get rolled back in a hurry.

Keep It Locked as the App Grows

Locking the database once is not the job. Keeping it locked is. Every migration can open a new hole. A new table, a new view or a new helper function each arrives with defaults that favor access, not safety.

Make three habits part of every migration. Turn RLS on in the same file that creates a table, right after the create line. Never leave it for a later tidy up, because later is the window. Run the open table query after the migration lands, on the real database, not on your laptop. And run the as a user test for any table whose policy changed.

Treat any row with RLS off in that query as an outage, even if nobody has found it yet.

What the Finishing Pieces Contain

With the steps above, every table has RLS on, anon has no way in and you know how to prove a policy. The finishing pieces cover the holes that survive all of that. They hold a five part audit script for functions, views and stacked policies, the exact fixes for each, a write test that proves users cannot change or plant each other's rows, and the checklist we run after every migration. Enter your email to open them. Next, read about Supabase calls that fail silently.