Supabase row-level security, explained with real policies

Supabase lets the browser talk to your Postgres database directly. The first time you see that, it sounds dangerous, and without row-level security (RLS) it is. With RLS switched on and good policies in place, it is one of the safest ways to build an app, because the database itself decides who can see and change each row.

Why the anon key is fine to ship

The Supabase anon key sits in your front-end code, so anyone can find it. That's expected. The key only says "this request comes from my app". What the request can actually read or write is decided by RLS policies, using the signed-in user's JWT. If RLS is off on a table, though, anyone with the anon key can read the whole table. So the first rule is simple:

alter table public.reports enable row level security;

With RLS on and no policies, the table returns nothing to anyone. You then add policies to open up exactly what each user should reach.

Policy 1: users see their own rows

create policy "Owners can read their reports"
on public.reports for select
to authenticated
using ( (select auth.uid()) = user_id );

create policy "Owners can insert their reports"
on public.reports for insert
to authenticated
with check ( (select auth.uid()) = user_id );

using filters which existing rows a query can touch. with check validates new or changed rows. An insert policy without with check would let a user create a report in someone else's name. Wrapping auth.uid() in a select lets Postgres evaluate it once per query rather than once per row, which matters on big tables.

Policy 2: roles, like staff or admins

Most real apps have roles. Keep them in a table the user can't edit, and write a small helper function that policies can call.

create function public.has_role(required text)
returns boolean
language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = (select auth.uid()) and role = required
  );
$$;

create policy "Staff can read all reports"
on public.reports for select
to authenticated
using ( public.has_role('staff') );

Policies for the same action are combined with OR, so an owner sees their own reports and staff see everyone's. Never store the role in user_metadata: the user can change that from the browser.

Common mistakes

• Forgetting a table. New tables start with RLS off. Supabase's security advisor flags them, and it's worth checking after every migration.
• Using the service role key in the browser. It bypasses RLS entirely. It belongs only in server code and environment variables that never reach the client.
• Update policies without a check. A user who can update their own row could change user_id to someone else's. Add with check to updates too.
• Missing indexes. Every column used in a policy, like user_id, should be indexed, or every query does a full table scan.

Test policies like code

You can impersonate a user in SQL to check what they would see, before any front end exists:

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "a-user-uuid"}';

select count(*) from public.reports;  -- only this user's rows

rollback;

I write a few of these for each role, covering what they should see and what they shouldn't. When a policy changes, running them again takes seconds.

Checklist

• Enable RLS on every table in an exposed schema
• Use using for reads and with check for writes
• Keep roles in a table users can't edit
• Keep the service role key on the server only
• Index the columns your policies filter on
• Test each role by impersonating it in SQL

Building a portal with different roles and sensitive data? Let's talk.