Supabase RLS Explained: Policies, Roles and Common Mistakes

Q: Does the service role bypass RLS?

Yes. Requests made with a secret key run as the service_role Postgres role, which has the bypass RLS attribute. Keep that key on the server. One exception catches people: if the request also carries a signed-in user's access token, it runs under that user's policies instead.

Supabase Row Level Security, Explained With Real App Examples

Supabase gives the browser a key that talks straight to your database. That's the whole appeal, and it's also why row level security (RLS) matters more in Supabase than in almost any other stack. There's no API server in the middle deciding who gets what. Your policies are the API's permission layer.

Most RLS tutorials stop at auth.uid() = user_id. That policy is fine, and real apps need a lot more: roles, teams, paid content, public pages with private drafts. This guide covers how RLS works, five patterns taken from the kinds of apps we build (a CRM, an LMS, a blog, a store), the mistakes that leak data, and how to test your policies before a user finds the gap for you.

Every SQL example follows the current Supabase RLS documentation.

TL;DR

A policy is a WHERE clause Postgres adds to every query on a table. using filters existing rows; with check validates new or changed rows.
Grants run before policies. Grants decide whether a role can touch the table at all, policies decide which rows. Set both.
Update policies protect rows, not columns. If users can update their own profile and the profile holds their role, they can make themselves admin. Keep roles in a table users can't write, or in app_metadata.
Never trust raw_user_meta_data for authorization. The user controls it, including at sign-up.
Put security definer helpers in a private schema with set search_path = ''.
Wrap auth.uid() in a select, add to authenticated, and index policy columns to keep policies fast.
Test with a real user token, not the dashboard. The SQL editor runs as a role that bypasses RLS.

How RLS Works in Supabase

Every request to the Supabase Data API runs as one of three Postgres roles:

Role	When it's used	RLS applies
`anon`	No signed-in user	Yes
`authenticated`	A signed-in user's access token is present	Yes
`service_role`	Your server, using a secret key	No, it bypasses RLS

Postgres then runs two checks. Grants decide whether the role can select, insert, update or delete on the table at all. Policies decide which rows that operation can see or produce. Supabase's docs point out that on existing projects, new tables in public start with all four privileges granted to anon and authenticated, and adding policies doesn't take those grants back.

Once RLS is enabled on a table, nothing is readable or writable through the API until a policy allows it. That default-deny is the safety net, so enable it on every table in an exposed schema:

alter table public.deals enable row level security;

-- Take back the automatic grants, then give back only what the app needs.
revoke all on table public.deals from anon, authenticated;
grant select, insert, update, delete on table public.deals to authenticated;

The Four Kinds of Policy

Operation	Clause	What it checks
`select`	`using`	Which rows the user can read
`insert`	`with check`	Whether the new row is allowed
`update`	`using` and `with check`	Which rows they can change, and what the changed row may look like
`delete`	`using`	Which rows they can remove

Two details trip people up. An update only works if a select policy also lets the user see the row. And if an update policy has no with check, Postgres uses the using expression for both.

Multiple policies for the same operation combine with OR. If any one of them passes, the row is allowed.

Pattern 1: Rows a User Owns

The starting point for bookmarks, notes, orders and anything else that belongs to one person:

create policy "Customers read their own orders"
on public.orders for select
to authenticated
using ( (select auth.uid()) = user_id );

Notice what's missing: an insert policy. In a store, customers shouldn't create order rows from the browser. Your checkout webhook creates them on the server with a secret key after payment succeeds. The safest write policy is often no policy at all, with the server doing the write.

Pattern 2: Roles Users Can't Change

Almost every business app has roles: admin and rep in a CRM, student and instructor in an LMS, author and editor in a blog. The tempting design is a role column on profiles, plus a policy that lets users edit their own profile:

-- Looks harmless. It isn't.
create policy "Users update their own profile"
on public.profiles for update
to authenticated
using ( (select auth.uid()) = id );

That policy decides which rows a user can update. It says nothing about which columns. Any signed-in user can open the browser console and run:

await supabase.from('profiles').update({ role: 'admin' }).eq('id', user.id)

The row is theirs, so the policy passes. If your admin checks read profiles.role, that user is now an admin. It's one of the most serious holes we find when reviewing Supabase projects, because the app's own UI never exposes the field and nobody thinks to try it.

Supabase's docs recommend RLS plus a dedicated table for roles over column-level tricks. Users can read their own role, and only the server can write it:

create table public.user_roles (
  user_id uuid primary key references auth.users (id) on delete cascade,
  role text not null default 'member' check (role in ('member', 'admin'))
);

alter table public.user_roles enable row level security;
revoke all on table public.user_roles from anon, authenticated;
grant select on table public.user_roles to authenticated;

create policy "Users read their own role"
on public.user_roles for select
to authenticated
using ( (select auth.uid()) = user_id );

-- No insert, update or delete policies. Role changes go through your server.

Then check roles through a helper in a schema the API doesn't expose:

create schema if not exists private;

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

revoke execute on function private.is_admin() from public;
grant usage on schema private to authenticated;
grant execute on function private.is_admin() to authenticated;

security definer lets the function read user_roles without triggering its policies. set search_path = '' stops a caller from pointing an unqualified table name at an object they control, which is why every name inside is schema-qualified. And keeping it out of public means nobody can call it directly over the API.

If you'd rather keep role on profiles, the quick fix is column privileges, so users can only update the columns they should:

revoke update on table public.profiles from authenticated;
grant update (full_name, avatar_url) on table public.profiles to authenticated;

A third option is storing the role in raw_app_meta_data, which users can't modify, and reading it with auth.jwt() -> 'app_metadata' ->> 'role'. The trade-off: a JWT isn't refreshed instantly, so a revoked admin keeps access until their token expires.

Don't Take the Role From Sign-Up Data

The same hole shows up one step earlier, in the trigger that creates a profile for each new user:

-- Dangerous: the client decides the role.
coalesce(new.raw_user_meta_data ->> 'role', 'student')

raw_user_meta_data is whatever the client passed to supabase.auth.signUp({ options: { data } }). Anyone can sign up with data: { role: 'admin' }. Supabase's docs are explicit that user metadata can be modified by the user and isn't a place for authorization data. Assign the default role in the trigger and ignore what the client sent:

create function private.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, full_name)
  values (new.id, new.raw_user_meta_data ->> 'full_name');

  insert into public.user_roles (user_id, role)
  values (new.id, 'member');

  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function private.handle_new_user();

Pattern 3: Shared Team Data

A CRM is a shared workspace: everyone on the team sees the same companies, contacts and deals. The shortcut is a helper that checks whether the user has a profile. But if sign-up creates a profile for everyone, anyone who registers is on the team, and your public sign-up page becomes a door into every customer record.

Membership has to come from an invite or an admin. For multi-team apps, use a membership table that only your server writes to (the helper below lives in the private schema from Pattern 2):

create table public.team_members (
  team_id uuid not null references public.teams (id) on delete cascade,
  user_id uuid not null references auth.users (id) on delete cascade,
  role text not null default 'member' check (role in ('member', 'admin')),
  primary key (team_id, user_id)
);

-- The primary key only indexes team_id. Policies filter on user_id.
create index team_members_user_id_idx on public.team_members (user_id);

create function private.user_team_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select team_id from public.team_members
  where user_id = (select auth.uid())
$$;

create policy "Team members read deals"
on public.deals for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

create policy "Team members add deals to their team"
on public.deals for insert
to authenticated
with check ( team_id in (select private.user_team_ids()) );

create policy "Team members update their team's deals"
on public.deals for update
to authenticated
using ( team_id in (select private.user_team_ids()) )
with check ( team_id in (select private.user_team_ids()) );

The helper also avoids a classic failure. If deals checks team_members and team_members checks something that reads back, Postgres raises infinite recursion detected in policy. A security definer function reads the membership table as its owner, which breaks the loop.

For a single-company app, the simpler version works too: new accounts start with a status like invited, an admin activates them, and the team helper checks for active. That only holds if users can't change their own status, which is Pattern 2 again.

Pattern 4: Content Gated by Enrollment

In an LMS, the course catalog is public-ish, but lesson content is what people pay for. A policy like using (true) for signed-in users hands every lesson and video URL to anyone with an account.

Gate lessons by free preview or enrollment, and give instructors a separate policy for their own courses, including drafts. The helper again lives in the private schema:

create function private.enrolled_course_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select course_id from public.enrollments
  where user_id = (select auth.uid())
    and status in ('active', 'completed')
$$;

create policy "Students read published lessons they can access"
on public.lessons for select
to authenticated
using (
  status = 'published'
  and (
    free_preview
    or module_id in (
      select m.id from public.modules m
      where m.course_id in (select private.enrolled_course_ids())
    )
  )
);

create policy "Instructors read lessons in their own courses"
on public.lessons for select
to authenticated
using (
  module_id in (
    select m.id from public.modules m
    join public.courses c on c.id = m.course_id
    where c.instructor_id = (select auth.uid())
  )
);

Then look hard at who writes enrollments. If students can insert their own enrollment row, they can join a paid course with one API call. Create enrollments on the server after checkout. The same goes for progress: a student who can update their own enrollment can set status = 'completed' and claim a certificate, so derive completion from lesson completion records instead.

Video URLs deserve their own thought. RLS protects the database row, but a public video URL works for anyone who has it. Use signed playback URLs from your video host for paid content.

Pattern 5: Public Pages, Private Drafts

A blog needs anonymous visitors to read published posts while authors work on drafts only they can see:

grant select on table public.posts to anon, authenticated;

create policy "Anyone reads published posts"
on public.posts for select
to anon, authenticated
using ( status = 'published' );

create policy "Authors read their own posts"
on public.posts for select
to authenticated
using ( (select auth.uid()) = author_id );

create policy "Authors edit their own unpublished posts"
on public.posts for update
to authenticated
using ( (select auth.uid()) = author_id and status <> 'published' )
with check ( (select auth.uid()) = author_id and status <> 'published' );

The with check is what stops an author from publishing their own post or handing it to another author. Editors get their own update policy based on a role check from Pattern 2.

Public inserts need the same care. If anonymous visitors can submit comments, the insert policy should force the safe state, such as with check ( status = 'pending' ), so nobody can post a pre-approved comment by setting the field themselves.

Mistakes That Leak Data

Letting users update columns that grant power. Roles, team status, plan tier, credits, is_verified. If it changes what someone can do, users shouldn't be able to write it.
Reading authorization from raw_user_meta_data. At sign-up or in a policy. Use app_metadata or a table only the server writes.
Treating "signed in" as "allowed". using (true) for authenticated, or a helper that returns true for any user with a profile, is effectively public if sign-up is open.
Policies with no to clause. They apply to every role, including anon. Name the role on every policy.
Views over protected tables. Views run with their creator's privileges by default, so they skip RLS. On Postgres 15 and later, create them with (security_invoker = true).
The secret key in the browser. It bypasses RLS completely, so it belongs on the server only. The reverse gotcha: a server client built with a secret key but carrying a user's access token runs under that user's policies, not as service_role.
security definer functions in public. They're callable over the API with the owner's privileges. Keep them in a private schema with a pinned search_path.
Storage buckets without policies. Files in Supabase Storage are governed by policies on storage.objects. Scope each bucket the way you'd scope a table.

Keeping Policies Fast

Postgres evaluates a policy for each candidate row, so slow policies make slow queries. Supabase's docs list three rules that matter most:

Wrap functions in a select, as in (select auth.uid()). Postgres then runs the function once per statement instead of once per row. This only works when the result doesn't depend on the row.
Index the columns policies filter on, like user_id, team_id and author_id. A composite primary key only indexes its first column.
Add to authenticated so Postgres skips the policy entirely for anonymous requests.

To see whether RLS is the bottleneck, run the query with explain analyze as the user role in a development database, then compare with RLS off. If the times match, the query is the problem, not the policy.

How to Test Your Policies

The SQL editor in the Supabase dashboard runs as the postgres role by default, which bypasses RLS, so a query that works there proves nothing about what a user can do. Test as the user.

Quick manual check in a development database, impersonating a real user ID:

begin;
set local role authenticated;
set local request.jwt.claims to '{"sub":"USER-UUID-HERE","role":"authenticated"}';

-- Should return only that user's rows.
select id from public.deals;

-- Should update zero rows or fail. If it says UPDATE 1, you have a problem.
update public.user_roles set role = 'admin' where user_id = 'USER-UUID-HERE';

rollback;

Automated tests are better. Supabase supports pgTAP tests in supabase/tests/, run with supabase test db. Its docs recommend one test file per table that asserts both what's allowed and what's denied, for anon and authenticated, across select, insert, update and delete. A policy without a deny test is a policy you're hoping works.

The attacker's test takes five minutes. Sign up as a new user in your app, open the browser console, and try to read another user's rows, update your own role, insert into a table the UI never writes to, and read a paid lesson. If any of those work from the console, they work for everyone.

A Pre-Launch RLS Checklist

RLS enabled on every table in every exposed schema
Grants revoked and re-granted per role, not left at the defaults
Every policy names its role with to
No column a user can update changes what they're allowed to do
No authorization data read from raw_user_meta_data
Sign-up alone doesn't grant access to shared data
Writes that follow payment or approval happen on the server
security definer functions live outside exposed schemas with search_path = ''
Views use security_invoker, or aren't exposed
Storage buckets have policies
The secret key appears nowhere in client code
A deny test exists for every table

For the wider setup (auth, environment variables, storage), our guide to connecting Supabase to a Next.js template covers the rest of the path to production.

Frequently Asked Questions

What is RLS in Supabase?

Row level security is a Postgres feature that Supabase uses to control which rows each user can read or change. Each policy acts like a WHERE clause added to every query on a table, using helpers like auth.uid() to identify the signed-in user. Because the browser talks to the database directly, RLS is what keeps one user's data away from another.

Does the service role bypass RLS?

Yes. Requests made with a secret key run as the service_role Postgres role, which has the bypass RLS attribute. Keep that key on the server. One exception catches people: if the request also carries a signed-in user's access token, it runs under that user's policies instead.

Is it safe to expose the Supabase anon key?

The anon (publishable) key is designed to be public, and it's safe only when every table it can reach has RLS enabled with correct policies and grants. Treat it as if an attacker already has it, because anyone who opens your site does.

Why does my Supabase update affect zero rows?

Usually because the user has an update policy but no select policy that lets them see the row. Postgres needs both. It can also mean the using expression doesn't match the row, or the changed row fails with check.

Can Supabase RLS restrict access to specific columns?

Policies work on rows, not columns. To stop users from reading or updating specific columns, use column privileges with grant and revoke, move the sensitive data to a separate table with its own policies, or expose a view with security_invoker enabled that leaves those columns out.

Do Supabase views respect RLS?

Not by default. Views run with the privileges of the role that created them, which typically bypasses RLS. On Postgres 15 and later, create views with (security_invoker = true) so they apply the underlying tables' policies to the person querying them.

Get a Second Pair of Eyes Before Launch

RLS mistakes don't show up in normal testing because the app's own screens never try the wrong thing. If you're about to launch a Supabase app with roles, teams or paid content, a review of your policies before real users arrive is cheap insurance. Our team builds Next.js and Supabase apps as custom builds, and our full-stack apps give you a starting point for a CRM, LMS, blog, kanban board or store.