Supabase RLS policy examples

How policies work, in 60 seconds

• RLS is enabled per table: alter table public.x enable row level security;. Once on, nothing is allowed until a policy allows it.
• A policy applies to one command (select, insert, update, delete, or all) and to one or more roles (anon for signed-out requests, authenticated for signed-in ones).
• using (...) decides which existing rows are visible or can be targeted. with check (...) validates rows being inserted or the new version of updated rows.
• Multiple permissive policies for the same command are combined with OR. If any one allows a row, it is allowed.
• auth.uid() returns the signed-in user's ID from their token, or null for anon requests. Write it as (select auth.uid()) so Postgres evaluates it once per query.
• The service_role or secret key bypasses RLS, so server code is unaffected by these policies.

Grants still matter too: a role needs a table privilege (for example grant select ... to authenticated) before any policy is even considered. The examples below set both.

Example 1: Users can only access their own rows

The most common pattern: a table where each row belongs to one user, identified by a user_id column referencing auth.users.

create table public.notes (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  body text not null,
  created_at timestamptz not null default now()
);

alter table public.notes enable row level security;
revoke all on table public.notes from anon, authenticated;
grant select, insert, update, delete on table public.notes to authenticated;

create policy "notes select own" on public.notes
  for select to authenticated using ((select auth.uid()) = user_id);
create policy "notes insert own" on public.notes
  for insert to authenticated with check ((select auth.uid()) = user_id);
create policy "notes update own" on public.notes
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);
create policy "notes delete own" on public.notes
  for delete to authenticated using ((select auth.uid()) = user_id);

create index on public.notes (user_id);

Why each piece is there: the default auth.uid() means the client does not have to send user_id. The with check on insert stops a user from creating rows owned by someone else. The with check on update stops them from moving their row to another user. The index keeps the policy fast as the table grows.

Example 2: A profiles table keyed by the user ID

Profiles usually use the auth user ID as the primary key. Users read and edit their own profile, and you may want others to see a few public fields.

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

create policy "profiles select own" on public.profiles
  for select to authenticated using ((select auth.uid()) = id);
create policy "profiles update own" on public.profiles
  for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

Do not give users an insert policy here if a trigger creates the profile on signup; the trigger function runs with its owner's privileges.

Watch out for privilege columns. If profiles has is_admin, plan or credits, the update policy above lets users set those on their own row. RLS works on rows, not columns. Either move such fields into a separate table only the server writes, or restrict which columns can be updated:

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

If other users need to see public profile fields, expose them through a separate table or a view with security_invoker = true that selects only safe columns, rather than opening select on the whole profiles table.

Example 3: Public read, owner write

Blog posts, listings or comments that anyone may read but only the author may change:

alter table public.posts enable row level security;
revoke all on table public.posts from anon, authenticated;
grant select on table public.posts to anon, authenticated;
grant insert, update, delete on table public.posts to authenticated;

create policy "posts public read" on public.posts
  for select to anon, authenticated using (published = true or (select auth.uid()) = author_id);
create policy "posts author insert" on public.posts
  for insert to authenticated with check ((select auth.uid()) = author_id);
create policy "posts author update" on public.posts
  for update to authenticated
  using ((select auth.uid()) = author_id)
  with check ((select auth.uid()) = author_id);
create policy "posts author delete" on public.posts
  for delete to authenticated using ((select auth.uid()) = author_id);

Drafts stay private because the read policy only shows unpublished rows to their author.

Example 4: Team or organization access

When rows belong to a team and users can be members of several teams, keep membership in its own table and check it in the policy. A security definer helper function avoids recursive policy lookups on the 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)
);
alter table public.team_members enable row level security;
create policy "members see own memberships" on public.team_members
  for select to authenticated using ((select auth.uid()) = user_id);

create or replace function public.is_team_member(t uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.team_members
    where team_id = t and user_id = (select auth.uid())
  );
$$;

alter table public.projects enable row level security;
create policy "team members read projects" on public.projects
  for select to authenticated using ((select public.is_team_member(team_id)));
create policy "team members write projects" on public.projects
  for insert to authenticated with check ((select public.is_team_member(team_id)));

Note set search_path = '' on the function: Supabase's advisor flags security definer functions with a mutable search path, and fully qualifying names (public.team_members) is the fix. Who can add rows to team_members is the real security boundary here, so handle invites in an Edge Function or with a tightly scoped policy rather than letting users insert themselves into any team.

Example 5: Insert-only from the public (contact forms, waitlists)

Signed-out visitors can submit, but nobody can read submissions from the browser:

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

create policy "anyone can join" on public.waitlist
  for insert to anon, authenticated
  with check (char_length(email) between 3 and 320);

With no select policy, the browser cannot read the list. Note that supabase-js insert() does not return the row unless you call .select(), which would need a select policy, so keep the client call as a plain insert. Read the list from the dashboard or with server code. Add CAPTCHA or an Edge Function in front if bots become a problem.

Example 6: Admin access

Avoid reading admin status from user_metadata, which users can edit themselves; Supabase's advisor has a lint specifically for policies that reference it. Use a table only the server writes, or a custom claim set via an Auth hook:

create table public.admins (user_id uuid primary key references auth.users (id));
alter table public.admins enable row level security; -- no policies: browser can't read or write it

create or replace function public.is_admin()
returns boolean language sql security definer set search_path = '' stable as $$
  select exists (select 1 from public.admins where user_id = (select auth.uid()));
$$;

create policy "admins read all orders" on public.orders
  for select to authenticated using ((select public.is_admin()));

Mistakes we see in generated policies

• using (true) on a table of personal data, often added to "fix" an empty page.
• Policies to authenticated using (true) when anyone can sign up, which means any stranger can read everything after creating an account.
• Update policies without with check, letting users reassign rows.
• Checking auth.jwt() -> 'user_metadata' ->> 'role' for authorization.
• A for all policy that was meant to be read-only.
• Forgetting new tables entirely. Re-run the advisor after every change.

How to test your policies

The SQL Editor bypasses RLS, so test as real roles. You can simulate a user inside a transaction:

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated"}';
select count(*) from public.notes;  -- should only count that user's notes
rollback;

For anon, use set local role anon; and expect zero rows from private tables. For a durable safety net, write pgTAP tests and run them with supabase test db. And check from the outside: RowLocked's free scan uses your app's public key to test which tables answer anonymous requests.

Frequently asked questions