advanced · 27 minute study

Check the row before and after a change

A user may be allowed to see an existing row yet still be forbidden to create its proposed replacement. Row reassignment is the classic test of this boundary.

Loading progressEvidence grows with practice

Useful preparation · 2 prerequisites

Use this as a map, not a lock. If these ideas are familiar, continue.

Predict the rows a policy will reveal — One permissive policy added for convenience can widen access despite several careful policies elsewhere. Learn to evaluate the complete policy set.
Make a change an all-or-nothing unit — An order without its lines is not half a successful feature. It is a broken state that later screens, reports and retries must somehow interpret.

First, build a mental model

UPDATE has two snapshots: the existing row it wants to find and the proposed row it wants to store. USING filters the first; WITH CHECK validates the second.

INSERT checks proposed rows with WITH CHECK. UPDATE uses USING on old rows and WITH CHECK on new rows. DELETE has no new row to check.
For ALL and UPDATE policies, omitting WITH CHECK uses the USING expression as the new-row condition. It is incorrect to teach that omission always removes the new-row check.
An explicit WITH CHECK(true) can weaken the new-row condition. Keep ownership and company invariants intentional, and evaluate all applicable policies.
Typical UPDATE/DELETE statements read ID or other columns and need SELECT grants/policies too. Required SELECT checks can also reject proposed rows; ordinary filtered writes can affect zero rows without a policy exception.

Read the codesql · educational example

-- PostgreSQL-only policy sketch; memberships are trusted.
CREATE POLICY member_edit ON public.notes
FOR UPDATE TO authenticated
USING (company_id IN (
  SELECT company_id FROM public.memberships
  WHERE user_id = (SELECT auth.uid())
))
WITH CHECK (company_id IN (
  SELECT company_id FROM public.memberships
  WHERE user_id = (SELECT auth.uid())
));
-- Required SELECT policy / grants are configured separately.

Old row
Alice can find a company A note through both the relevant read policy and update policy.
Proposed row
Changing company_id to B is evaluated against the new row, not the original company A value.
Outcome
A failed new-row check raises an error; an old row filtered out by USING usually means zero affected rows.

Production thinking

Take this beyond the example.

Test old and new company IDs independently, attempted owner reassignment, absent SELECT policy, missing grant and zero-row outcomes. Run direct negative cases under ordinary actors; setup with a privileged role is not the tested actor.

Sources and further reading

Your evidence

unseen is a local learning signal, not a credential or proof of production competence.

Explain the mental model, then explicitly acknowledge it. Opening a page is not evidence.

Supported retries can complete this lesson. Independent evidence and delayed review determine the skill stage.

Next lesson · An RPC is an exposed function, not a permission shortcut →