Levandor

CRM RLS Write-Auth Convention

4 min read

The canonical rule for writing a new CRM write feature’s RLS policy: gate on the ROLE only (to authenticated using (true) with check (...)). The real operator gate lives upstream in Cloudflare Access ZTNA, not in Postgres — so a Postgres auth.uid() claim-check is the wrong tool for “is this a legitimate operator?“. This is the answer @crm gave @positionmanager when scoping the pmv2_wallets.size_scale policy for the crypto-lane-ops-tab.

For Agents — the rule

A new CRM write feature’s RLS predicate = using (true) for the authenticated role (add a with check (...) only to constrain values, e.g. a range clamp — not to check identity). Do not gate the operator on auth.uid() / claim-checks; that is not how the operator gate works here.

Why the operator gate is upstream, not in Postgres

The admin CRM sits behind Cloudflare Access ZTNA. Only an authenticated operator ever receives a Supabase token at all: the cf-access-auth edge function verifies the CF Access JWT and exchanges it for a Supabase access token (role = "authenticated"). See security and web/CLAUDE.md. Consequences:

  • If a request carries a valid Supabase JWT, an operator already passed ZTNA — the authenticated role is the operator gate.
  • Postgres claim-checks are NOT used for the operator gate. Re-checking identity in RLS would be redundant with the edge and only adds failure modes.
  • Therefore every CRM write policy is role-scoped: to authenticated using (true), plus service_role for all for backend workers.

The canonical migration 20260426100000_enforce_authenticated_rls.sql applies this authenticated-role model to ~every table. The newest write feature at time of writing, tender_qualification, follows it too.

The one exception — ownership, not the operator gate

A few tables additionally scope rows by auth.uid() = person.id — e.g. file, and the per-user notification / notification_preference / push_subscription tables. That is row ownership (“you may only see/edit your rows”), not the operator gate. Do not confuse the two:

ConcernMechanism
Is this a legitimate operator?Upstream — CF Access ZTNA (the authenticated role)
Which rows may this operator touch?using (true) for shared data; auth.uid() = person.id only where rows are per-person-owned

Don't over-lock a shared-data write

Defaulting a new write policy to an auth.uid() predicate on a shared table will silently deny legitimate operators (the JWT sub is person.id, which won’t match shared rows). Use using (true) unless the table is genuinely per-person-owned.

Defense-in-depth for secret columns (the pmv2_wallets.size_scale case)

using (true) opens the row, so protect sensitive columns at the grant layer, not by narrowing the RLS predicate. The crypto-lane-ops-tab size_scale write is the worked example — pmv2_wallets holds enc_private_key / key_nonce (armed-wallet secrets):

  1. The CRM write is a bare .update().eq() with NO .select() → it needs no SELECT grant on the base table.
  2. A column-scoped grant update (size_scale) on pmv2_wallets to authenticated (update-only, no select) means the authenticated role can write that one column and cannot read any column of the base table.
  3. The CRM reads wallet data only through a safe view (crypto_wallets), never the base table.

Net: the RLS predicate stays using (true) (operator gate = upstream), while the secret columns remain unreadable via the authenticated role. Role-level RLS for authorization, column-level grants + a read-view for secret confidentiality.

  • crypto-lane-ops-tab — the write feature this convention was scoped for (pmv2_wallets.size_scale)
  • security — CF Access ZTNA → cf-access-auth → Supabase JWT (sub = person.id); the upstream operator gate
  • infra-gotchas — records that RLS tables are authenticated-role USING(true) + service_role FOR ALL (the same model, from the DB-plumbing side)
  • vuln-disclosure-tracker — reading another team’s RLS table needs for select to authenticated using(true) (the read-side sibling of this rule)
  • disclosure-on-demand-analysis — a prior narrow-surface write (one INSERT, authenticated insert policy, no update — worker owns state via service_role)
  • position_manager — owns pmv2_wallets; applies the RLS policy + column grant (Andras gates it)

Graph View

Backlinks