Supabase RLS Explained: Policies, Roles, and Common Security Mistakes

Supabase gives the browser a key that talks straight to your database. There is no API server in the middle deciding who gets what, which means your Row Level Security (RLS) policies are your application's actual permission layer.

Most tutorials stop at auth.uid() = user_id. Real apps need roles, team tenancy, paid content access, and public pages with private drafts. Here is how RLS works in practice, five common architectural patterns, security mistakes to avoid, and how to test your rules before shipping.

How RLS Works in Supabase

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

• anon: No signed-in user. RLS applies.
• authenticated: A signed-in user's access token is present. RLS applies.
• service_role: Your server, using a secret key. Bypasses RLS.

Postgres runs two checks for each incoming query:

1. Grants: Determine whether a role can run select, insert, update, or delete on the table at all.
2. Policies: Determine which specific rows that operation can read or modify.

On existing Supabase projects, new tables created in public start with all four operations granted to anon and authenticated. Adding policies does not revoke those default grants. Once RLS is enabled, access is default-deny, but you should explicitly clean up grants:

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 Policy Clauses

• select: Uses using to check which rows a user can read.
• insert: Uses with check to validate newly created rows.
• update: Uses using to decide which rows can be updated, and with check to validate what the updated row looks like.
• delete: Uses using to check which rows can be removed.

Two details catch people often:

1. An update statement fails if the user does not also have a select policy that lets them view the target row.
2. If an update policy omits with check, Postgres applies the using expression to both passes.

Multiple policies for the same operation combine with OR. If any single policy passes, the row is allowed.

Pattern 1: User-Owned Rows

For private user data like orders, notes, or bookmarks:

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

Notice there is no insert policy here. In a store, customers should not insert orders directly from the client. An order row should be created by a server-side webhook running with the service role after payment succeeds. The safest write policy is often having no client write policy at all.

Pattern 2: Immutable Roles

Many applications have roles: admin, editor, student, instructor. The naive approach puts a role column directly on profiles with an update policy allowing users to 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 );

This policy restricts rows, not columns. Any signed-in user can run this from their browser console:

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

Because the row belongs to the user, the update succeeds. If your authorization logic relies on profiles.role, they are now an admin.

Instead, separate roles into a dedicated table that users cannot write to:

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.

To check role status in other policies, write a helper function inside a private schema:

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 allows the function to read user_roles without triggering infinite loops or needing broad read access. set search_path = '' stops search path hijacking, requiring fully qualified schema names inside the function.

If you must keep role on profiles, restrict permissions down to the column level:

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

Never Trust Sign-Up Metadata for Roles

Avoid assigning roles in triggers from user metadata:

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

raw_user_meta_data is supplied directly by the client during supabase.auth.signUp(). Anyone can send { options: { data: { role: 'admin' } } }. Always hardcode default roles in auth triggers:

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 Tenancy

Checking whether a user has an account is not enough for multi-tenant apps. If public sign-up is open and any signed-in user can access rows, anyone can view private workspace data.

Use an explicit team membership table:

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()) );

Using a security definer helper function here also avoids infinite recursion detected in policy errors when tables cross-reference each other.

Pattern 4: Access Gated by Enrollment

In an LMS or membership site, catalog metadata might be open, but paid lessons require validation:

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())
  )
);

Ensure enrollments can only be inserted by your server webhook, not by client-side calls.

Pattern 5: Public Reads with Private Drafts

For publishing platforms where anonymous users can see live content, but creators can only see or edit their own drafts:

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 clause prevents authors from setting their own post to published without proper review steps or permissions.

Keeping Policies Fast

Postgres evaluates policies across candidate rows. If written poorly, queries slow down drastically:

1. Wrap subqueries: Write (select auth.uid()) instead of auth.uid(). Wrapping it in a select makes Postgres evaluate it once per query instead of recalculating on every row.
2. Index foreign keys: Policies filter against user_id, team_id, or author_id. Ensure every column checked in a policy is indexed.
3. Specify the role with to: Using to authenticated tells Postgres to skip running the policy altogether for anon queries.

Testing Policies Manually and in CI

The Supabase Dashboard SQL editor runs queries as the superuser role, bypassing all RLS rules. A query working there does not mean your users can run it.

To test policies in SQL, wrap your test in a transaction that sets the active role and JWT context, then rolls back:

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;

If the update returns UPDATE 1, your policy allows users to overwrite restricted columns.

---

This is a shorter version of a guide I wrote on thefrontkit. The full post has the complete walkthrough.