Supabase RLS Examples for Users, Teams, and Admins

Example tables

create table public.projects (
  id bigint generated always as identity primary key,
  owner_id uuid not null references auth.users(id),
  name text not null
);

create table public.project_members (
  project_id bigint not null references public.projects(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  member_role text not null check (member_role in ('member', 'admin')),
  primary key (project_id, user_id)
);

alter table public.projects enable row level security;
alter table public.project_members enable row level security;

Start with user-owned rows

A direct owner column is the simplest policy shape. The SELECT policy decides which existing rows are visible. The INSERT policy checks the proposed row, so a user cannot create a project for another owner.

Owner SELECT and INSERT policies

create policy "owners can read projects"
on public.projects
for select
to authenticated
using ((select auth.uid()) = owner_id);

create policy "owners can create projects"
on public.projects
for insert
to authenticated
with check ((select auth.uid()) = owner_id);

The SELECT wrapper lets PostgreSQL evaluate auth.uid() as an initialization plan instead of invoking it separately for every candidate row.

Add team membership with an EXISTS policy

Team access belongs in a membership table with a unique key on project_id and user_id. The policy can admit either the owner or a matching member. Indexes supporting those columns matter once the tables grow.

Owner-or-member read access

create policy "members can read projects"
on public.projects
for select
to authenticated
using (
  owner_id = (select auth.uid())
  or exists (
    select 1
    from public.project_members pm
    where pm.project_id = projects.id
      and pm.user_id = (select auth.uid())
  )
);

Protect both old and new values on UPDATE

USING controls which existing rows can be targeted. WITH CHECK controls whether the resulting row is allowed. Using both prevents an owner from updating a visible row and changing owner_id to another user.

Owner update policy

create policy "owners can update projects"
on public.projects
for update
to authenticated
using ((select auth.uid()) = owner_id)
with check ((select auth.uid()) = owner_id);

Keep administrative access server-side

Do not solve administration by placing the service role key in a browser. It can bypass RLS. Put privileged operations behind trusted server code, verify the caller there, and keep the key outside generated client bundles and logs.

For app-level administrators who should still pass through RLS, store a reviewed role claim or membership record and write a policy for that narrow capability. Avoid mutable user metadata that users can edit themselves.

Test denial, not only success

Create two test users and seed one owned project for each. Confirm user A cannot read, update, or delete user B's project; cannot insert a row owned by B; and gains exactly the intended access after a membership row is added.

Inspect the underlying tables through the Supabase connection guide, but test browser-facing behavior with authenticated Supabase clients so the same JWT claims are present as in the application.