Row Level Security, simplified

Row Level Security (RLS) is a Postgres feature that decides, row by row, what each request may read or change. In a Supabase project it's what makes it safe to query the database straight from a browser or mobile app with the public anon key.

The mental model

• Every request through the Data API runs as a Postgres role. No user token: anon. A signed-in user's token: authenticated. The service_role key: service_role, which skips RLS entirely.
• When RLS is enabled on a table, a row is invisible and untouchable unless a policy allows it.
• A policy is a SQL condition attached to a table, for one or more commands (select, insert, update, delete), for one or more roles.
• auth.uid() returns the signed-in user's id from their token, so policies can compare it to a column.

That's most of it. Turning RLS on with no policies gives "no access" (through the API, an empty result); each policy you add opens a specific door.

using and with check

• using (...) decides which existing rows the command can see. It applies to select, update and delete.
• with check (...) decides which new or changed rows are allowed. It applies to insert and update.

An update uses both: using to pick the rows you may change, with check to validate what they look like afterwards.

Four policies that cover most apps

A recipe app where users own their recipes, and published recipes are public:

create table public.recipes (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  title text not null,
  published boolean not null default false
);

alter table public.recipes enable row level security;
create index recipes_user_id_idx on public.recipes (user_id);

-- anyone can read published recipes; owners also see their drafts
create policy "Read published or own recipes"
on public.recipes for select
to anon, authenticated
using ( published or (select auth.uid()) = user_id );

-- signed-in users can add recipes only as themselves
create policy "Create own recipes"
on public.recipes for insert
to authenticated
with check ( (select auth.uid()) = user_id );

-- owners can edit, and can't hand a recipe to someone else
create policy "Edit own recipes"
on public.recipes for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

-- owners can delete
create policy "Delete own recipes"
on public.recipes for delete
to authenticated
using ( (select auth.uid()) = user_id );

From the app nothing changes. The same call returns different rows for different users:

const { data } = await supabase.from('recipes').select('id, title, published')

Things that trip people up

• RLS is per table. A table created with SQL has RLS off until you run enable row level security. With it off, anyone holding the anon key can read and write every row.
• Empty results aren't errors. A select blocked by RLS returns [], not a 403. An insert that fails with check does return an error: new row violates row-level security policy. See select returns an empty array.
• The SQL editor bypasses RLS. Studio connects as postgres, so queries there show every row. Test as a user from your app, or with set local role authenticated as shown in RLS performance best practices.
• Don't use auth.role(). It's deprecated. Use to authenticated on the policy instead. See auth.role() is deprecated.
• Never ship the service_role key to a client. It ignores every policy.

On SupaHost

RLS works exactly as in any Supabase project. Write policies in Studio (Dashboard → your project → Database tab → Open Studio, then the policies screen or the SQL editor), or through migrations. The MCP server's get_advisors tool lists tables in public that have RLS disabled, or enabled with no policies.

Related

• Row Level Security
• RLS performance best practices
• Securing your API
• infinite recursion detected in policy