2 · Policy anatomy

The question this chapter answers: what exactly did this policy just promise?

A policy is four decisions written in one line of SQL: which table, which command (select, insert, update, delete), which role (to authenticated), and which condition (using (...), with check (...)). Reading a policy means reading all four. Most RLS surprises are one of the four meaning something other than its author assumed.

The two clauses

using filters rows you can already reach. with check validates rows you are trying to create or modify. Which clause each command uses is fixed by Postgres (CREATE POLICY):

Two default rules complete the picture, and both matter:

• An update or all policy written with only using gets its with check from the same expression. If your condition is "I own this row," you also cannot reassign it to someone else — unless you deliberately wrote different conditions.
• A policy with no to clause applies to public — every role, including anon. Always write the to.

The Supabase guide recommends separate policies per command rather than for all, because a for all policy hides which operation each rule was meant to govern.

How policies combine

This is the single most important sentence in the chapter: multiple permissive policies for the same table and command combine with or. The effective permission is the union of every matching policy — including ones added months apart by different people. Restrictive policies (as restrictive) do the opposite: they combine with each other and with the permissive result using and, acting as gates that narrow whatever the permissive layer allows (Postgres docs).

You do not have to take this on faith. Everything below runs in one transaction against a scratch table, disclosed as scratch, ending in rollback so nothing persists. Run the blocks top to bottom in a single query session.

begin;

create table public.demo_docs (
  id int primary key,
  owner_id uuid not null,
  secret boolean not null default false,
  published boolean not null default false,
  title text not null
);

alter table public.demo_docs enable row level security;
-- No grants yet, no policies yet.

insert into public.demo_docs (id, owner_id, secret, published, title) values
  (1, 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3', true,  false, 'ada private'),
  (2, 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3', false, false, 'ada draft'),
  (3, 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', true,  false, 'bo private'),
  (4, 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', false, false, 'bo draft'),
  (5, 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', false, true,  'bo shared');

The inserts succeed even though RLS is enabled, because the table owner (postgres, in your editor) bypasses its policies — Chapter 1's ownership rule in action.

Impersonating a user safely

Every proof from here on needs to act as a specific user. This is the documented pattern (Supabase testing guide): switch role inside the transaction, then supply the claim auth.uid() reads.

set local role authenticated;
set local request.jwt.claim.sub = 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3';  -- act as ada

Now try to read as ada:

select count(*) from public.demo_docs;

You get an error, permission denied for table demo_docs — before any policy ran. That is the grant check failing, exactly as Chapter 1 promised. Grant read access and try again:

grant select, insert, update on public.demo_docs to authenticated;

select count(*) from public.demo_docs;   -- count: 0

Zero rows, no error: RLS enabled, zero policies, deny-by-default. Now add the obvious policy — users read their own documents:

create policy "docs: own rows" on public.demo_docs
  for select to authenticated
  using ((select auth.uid()) = owner_id);

select count(*) from public.demo_docs;   -- count: 2  (rows 1, 2 — ada's)

Proof 1: the or trap

A teammate ships sharing. Documents flagged published should be readable too — so a second policy appears:

create policy "docs: published rows" on public.demo_docs
  for select to authenticated
  using (published);

select count(*) from public.demo_docs;   -- count: 3  (rows 1, 2, 5)

Row 5 is bo's, and ada can now read it. Both policies still say what their authors intended; their combination permits owner = me or published — a rule nobody wrote down, and the first rule in this table that lets one user read another's row. Watch the union grow when someone debugging adds a shortcut:

create policy "docs: temp debug" on public.demo_docs
  for select to authenticated using (true);

select count(*) from public.demo_docs;   -- count: 5  — everything

A tautology does not look dangerous next to two reasonable policies; it is the whole permission. Delete the debug policy and express the real invariant — nobody ever sees secrets — as a restrictive gate instead:

drop policy "docs: temp debug" on public.demo_docs;

create policy "docs: never secret" on public.demo_docs
  as restrictive for select to authenticated
  using (not secret);

select count(*) from public.demo_docs;   -- count: 2  (rows 2, 5)

(own or published) and not secret: ada's private document vanished from her own view, by design. Permissive policies build the offer; restrictive policies vet it. Use them when a rule must survive future permissive additions — which is exactly the drift Chapter 4 catalogs.

Proof 2: writes are a separate promise

Read isolation says nothing about writes. Ada has insert granted; watch the policy reject a row she could never read back:

insert into public.demo_docs (id, owner_id, title)
values (6, 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', 'forged');
-- ERROR: new row violates row-level security policy for table "demo_docs"

That error is a with check failure. There is no insert policy on this table yet, so every insert fails — the mirror image of the empty-read case. Add the honest version:

create policy "docs: insert own" on public.demo_docs
  for insert to authenticated
  with check ((select auth.uid()) = owner_id);

Proof 3: update needs both directions

With no update policy, ada cannot change anything. Add the natural one — owners update their rows — and test both ways:

create policy "docs: update own" on public.demo_docs
  for update to authenticated
  using ((select auth.uid()) = owner_id);

update public.demo_docs set title = 'x' where owner_id = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';
-- UPDATE 0  — silent: using filtered bo's rows out of sight

update public.demo_docs set owner_id = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4' where id = 2;
-- ERROR 42501 — with check (inherited from using) rejected the new owner

The first shape fails silently — UPDATE 0 is indistinguishable from "row not found," which is why tests must assert on outcomes, not errors alone. The second shows the inherited with check doing real work. End the session cleanly:

rollback;

One caution from the docs: an update also requires a usable select path to work as expected — a table you can update but not read produces confusing results. Design the four commands together.

Check on your project

1. List every policy on your most important table (pg_policies makes this easy — Chapter 5 gives the exact query). For each pair sharing a command, write down what their or permits as a combination.
2. Search your policies for conditions that can be true regardless of the row or caller (true, auth.uid() is not null, a subquery that matches unconditionally). Each one is the union-widener above.
3. Confirm every policy names its role explicitly. Any policy without a to clause covers anon too.
4. Pick one update policy and state which expression governs its with check — explicit, or inherited from using. If you cannot answer instantly, that is the finding.
5. Note one rule in your app that must survive future policy additions, and consider whether it belongs in a restrictive policy.