Supabase RLS Policy Examples (Copy and Test)

To make users see only their own rows in Supabase, enable Row Level Security (RLS) on every table in the public schema, then add one policy per action that compares the row’s user_id to auth.uid(). Reads, edits and deletes use a USING clause, inserts use WITH CHECK, and updates use both. Then test each policy while signed in as two different users before you launch. Below are five copy-ready patterns, the reason inserts break after you turn RLS on, a test routine, and a prompt you can give your AI builder.

These examples cover the common cases. They are not a full security audit of your app.

What RLS does in one paragraph

Your app talks to Supabase from the browser with a publishable key that anyone can read. Supabase’s API key docs say plainly that this key “only reaches what Row Level Security allows”. RLS is a rule attached to a table that Postgres adds to every query automatically, like an invisible WHERE clause for reads and a check on new or changed rows for writes. Supabase’s docs warn that a table in an exposed schema without RLS is readable and writable by any role with a grant on it. That is why the dashboard flags “RLS disabled in public” as an error: anyone with your project URL could read, edit and delete that table. If you are fixing that warning right now, start with our guide to the RLS not enabled warning.

The 5 policies most AI-built apps need

Two things before the policies. First, policies decide which rows an action touches, but table grants decide whether a role may run the action at all. Supabase notes that on existing projects new tables in public start with every privilege granted to anon and authenticated, and adding policies does not take those back. So each table below revokes everything and grants back only what the app needs. Second, every example wraps the function as (select auth.uid()), which Supabase recommends so Postgres runs it once per query instead of once per row, and indexes the column the policy filters on.

All the SQL below runs in the SQL Editor in your Supabase dashboard, or as a migration.

1. Users read only their own rows

Plain English: a signed-in user can see a note only if its user_id is their own ID.

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

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 index notes_user_id_idx on public.notes (user_id);

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

When nobody is signed in, auth.uid() returns null, so the comparison is never true. Here anon also has no grant at all, so a signed-out request is refused before any policy runs.

2. Users add rows only as themselves

Plain English: a new row is accepted only if its user_id matches the person inserting it.

create policy "Users can add notes as themselves"
on public.notes for insert
to authenticated
with check ( (select auth.uid()) = user_id );

3. Users edit and delete only their own rows

Plain English: you can change or remove a row only if it is yours, and you cannot hand it to someone else by changing user_id.

create policy "Users can edit their own notes"
on public.notes for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

create policy "Users can delete their own notes"
on public.notes for delete
to authenticated
using ( (select auth.uid()) = user_id );

USING picks which existing rows can be updated, and WITH CHECK checks what the row looks like afterwards. Supabase also notes that an update needs a matching select policy to work, which pattern 1 provides.

4. Anyone can read published rows

Plain English: visitors, signed in or not, can read posts marked as published. Authors can also see their own drafts.

create table public.posts (
  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.posts enable row level security;

revoke all on table public.posts from anon, authenticated;
grant select on table public.posts to anon;
grant select, insert, update, delete on table public.posts to authenticated;

create index posts_user_id_idx on public.posts (user_id);

create policy "Anyone can read published posts"
on public.posts for select
to anon, authenticated
using ( published = true );

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

create policy "Authors can add posts as themselves"
on public.posts for insert
to authenticated
with check ( (select auth.uid()) = user_id );

Give the posts table update and delete policies by copying pattern 3. Only use a public read policy for data that is meant to be public.

5. Team members share rows

Plain English: anyone on a team can read and manage that team’s projects, and nobody else can.

create table public.teams (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

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,
  primary key (team_id, user_id)
);

create table public.projects (
  id bigint generated always as identity primary key,
  team_id uuid not null references public.teams (id) on delete cascade,
  name text not null
);

alter table public.teams enable row level security;
alter table public.team_members enable row level security;
alter table public.projects enable row level security;

revoke all on table public.teams, public.team_members, public.projects from anon, authenticated;
grant select on table public.teams, public.team_members to authenticated;
grant select, insert, update, delete on table public.projects to authenticated;

create index team_members_user_id_idx on public.team_members (user_id);
create index projects_team_id_idx on public.projects (team_id);

create schema if not exists private;

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

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

create policy "Members can see their teams"
on public.teams for select
to authenticated
using ( id in (select private.user_team_ids()) );

create policy "Members can see who is on their teams"
on public.team_members for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

create policy "Members can read team projects"
on public.projects for select
to authenticated
using ( team_id in (select private.user_team_ids()) );

create policy "Members can add team projects"
on public.projects for insert
to authenticated
with check ( team_id in (select private.user_team_ids()) );

create policy "Members can edit team projects"
on public.projects for update
to authenticated
using ( team_id in (select private.user_team_ids()) )
with check ( team_id in (select private.user_team_ids()) );

create policy "Members can delete team projects"
on public.projects for delete
to authenticated
using ( team_id in (select private.user_team_ids()) );

The helper function reads the membership table as its owner, which avoids the “infinite recursion detected in policy” error you get when two policies read each other’s tables. It lives in a private schema on purpose: Supabase warns never to put a security definer function in a schema exposed to the API. This version leaves adding and removing members to your server code. Write those policies separately once you decide who may invite people.

Why inserts fail after enabling RLS

Three causes cover most “new row violates row-level security policy” errors:

1. No insert policy, or a WITH CHECK that does not match. With RLS on and no policy, nothing gets in. If your app sends a user_id that is not the signed-in user’s ID, or sends none and the column has no default, the check fails.
2. No default for user_id. The tables above use default auth.uid(), the same pattern Supabase shows in its own table docs, so the app can leave user_id out and the database fills in the right value.
3. A missing grant. Supabase says a missing grant raises error 42501 before any policy runs, and the same code appears for a WITH CHECK violation. Check the grants before you rewrite the policy.

If saves fail silently in a Lovable app, our guide to Lovable and Supabase data not saving walks through the app side.

How to test: two accounts, SQL editor impersonation

Create two test accounts, A and B. Sign in as A, add a few rows, then sign in as B in a private window and confirm B sees none of them and cannot edit them.

Supabase Studio also lets you impersonate a user in the Table Editor and SQL Editor, so you can run a query as a specific user and check what RLS lets them see. To do the same in plain SQL, run this in the SQL Editor (replace the ID with a real user ID from your Authentication page):

begin;
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

select * from public.notes;  -- should list only this user's notes

rollback;

The rollback undoes anything the test changed. For a routine you can rerun on every change, Supabase recommends pgTAP test files run with supabase test db.

Prompting your AI builder to write RLS safely

AI builders often write one broad policy or skip the grants. Paste this and fill in the brackets:

Write a Supabase SQL migration for the table [table name] with columns [list]. Enable row level security. Revoke all privileges from anon and authenticated, then grant only [actions] to [roles]. Write one policy per action (select, insert, update, delete), never “for all”. Compare user_id to (select auth.uid()), use USING for select, update and delete, WITH CHECK for insert and update, and give user_id a default of auth.uid(). Add an index on every column a policy filters on. Do not use the service role or secret key anywhere in client code. Then give me SQL to test the policies as two different users.

Read the result against the patterns above before you run it.

Never ship the service role key

The service_role key, and the newer secret key that replaces it, bypass every RLS policy. Supabase’s docs say never to put one in a browser, a shipped app or source control. If your AI builder “fixed” a permissions error by switching to that key in frontend code, your policies no longer protect anything. Move that call to a server or an Edge Function and rotate the key. Whether you build with Lovable or another AI builder, the publishable key is the only Supabase key that belongs in frontend code.

FAQ

What RLS policy do I need so users only see their own rows?

A select policy for the authenticated role with using ( (select auth.uid()) = user_id ), plus RLS enabled on the table. Add matching insert, update and delete policies so users can only write their own rows too.

What does “RLS disabled in public” mean in Supabase?

It means a table in the public schema, which the Supabase API exposes, has Row Level Security turned off. Supabase rates it an error because anyone with your project URL can read, edit and delete the data. Run alter table public.your_table enable row level security; and then add policies.

Why does my insert fail after I enable RLS?

Usually there is no insert policy, the WITH CHECK does not match the user_id being sent, or the role lacks an insert grant. Give user_id a default of auth.uid() and check grants first.

How do I test an RLS policy?

Sign in as two different test users and confirm neither sees the other’s rows. In Supabase, use user impersonation in the SQL Editor, or set local role authenticated with a user ID inside a transaction, as shown above.

Should I ever use the service role key in the browser?

No. It bypasses all RLS, so anyone who finds it in your page code gets full access to your data. Use it only on a server or in an Edge Function.