How to write an owner-scoped RLS policy

Postgres 15 · Supabase✓ code tested against a real database

TL;DR

Store user_id on the table and compare it to (select auth.uid()) in one policy per operation: USING for SELECT/DELETE, WITH CHECK for INSERT, and both for UPDATE. Full set below.

01 The rule

Owner-scoped means: a row belongs to one user, and only that user can touch it. Store the owner as user_id and compare it to (select auth.uid()) — the id of the signed-in caller — in every policy. Wrapping it in a subselect lets Postgres cache the value per statement, a real speed win at scale.

02 The full policy set

One policy per operation. Note where each uses USING vs WITH CHECK:

migration.sql

alter table notes enable row level security;

-- read: filter to rows you own
create policy "select own" on notes for select
  using (user_id = (select auth.uid()));

-- create: the new row must belong to you
create policy "insert own" on notes for insert
  with check (user_id = (select auth.uid()));

-- update: you can only touch your rows (USING) and can't reassign them (WITH CHECK)
create policy "update own" on notes for update
  using (user_id = (select auth.uid()))
  with check (user_id = (select auth.uid()));

-- delete: you can only delete your rows
create policy "delete own" on notes for delete
  using (user_id = (select auth.uid()));

02b Why UPDATE needs both

USING decides which rows you may update; WITH CHECK validates the row after your change. Without WITH CHECK, a user could update their own row and set user_id to someone else's — handing it away. Both clauses close that.

03 Prove it

✓ owner reads / updates / deletes their own rows

✗ owner sees 0 rows for another user

✗ owner cannot reassign a row to another user_id

See how to test tenant isolation to turn that into an automated check.

◆ FREE · MIT · npm + GitHub Action

Catch this before it ships

airlock-rls is a CI gate that fails your build when a table ships exposed or a policy is permissive — the same class of bug, caught on the pull request instead of in prod.

npx airlock-rls→

Or start from nextjs-supabase-starter — auth + a table with RLS + an isolation test, so a fresh table is safe by default.

FREE PDF

Grab the Supabase RLS cheat sheet

The golden rules, the footguns that leak in prod, correct policy snippets, and the isolation test — on one page.

FAQ

Can I use one policy for all operations?

You can write a policy FOR ALL, but you lose the USING/WITH CHECK distinction that matters for writes. A policy per operation is clearer and safer — especially so UPDATE gets both clauses.

Where does user_id come from on insert?

Set a column default of (select auth.uid()) so the database fills it, or set it explicitly from the signed-in user on the client. Either way the WITH CHECK guarantees it matches the caller.