-- V1-2.1 M4 sheet_access_hardening. Contract: -- docs/development/V1-2_ANATOMICAL_SHEETS.md §5 (rights, catalogue access, -- MFA, AR-1 B), §12.1 (defects), §12.3 (M4), §14.2 (historical sheet). -- -- M4 and M5 (20261028110000_sheet_review_workflow.sql) are applied together: -- M4 removes every unsafe write path of the Step 4 lifecycle (personal -- creation, fork, visibility changes, publication without review, direct -- table writes, service-role table privileges); M5 adds the reviewed -- workflow. M4 alone leaves no path that publishes a version. -- -- What M4 establishes: -- * explicit catalogue rights (`anatomical_catalog_grants`): three -- capabilities and the provisional catalogue access, written by the -- platform owner only, from an `aal2` session that still exists; -- * predicates kept apart so no check tests the wrong account: -- `grant_active(uid, kind)` for stored actors, `caller_has(kind)` for the -- calling session (adds `has_mfa()`); -- * the catalogue reader predicate (official, non-deleted sheet with a -- published or deprecated version, and catalogue access) on sheets, -- versions, asset rows and Storage objects; capability holders with -- `aal2` read every official sheet; nobody else reads anything; -- * AR-1 B: every platform role change needs an `aal2` session checked -- against auth.sessions, a verified TOTP factor on a grant's target, a -- race-free last-owner guard; `service_role` loses its table privileges -- on profiles, profile_roles and every catalogue table (F12, F17); -- * immutability: deprecated versions are never deleted (F13), asset rows -- never change (F16), the historical equine sheet is frozen (§14.2). -- No row is changed by this migration. -- --------------------------------------------------------------------------- -- Assurance level of the calling session (contract §5.6, §5.7.8) -- --------------------------------------------------------------------------- -- The `aal` claim of the token PostgREST verified. An absent, null or -- differently cased claim is not aal2. create or replace function private.has_mfa() returns boolean language sql stable security definer set search_path = '' as $$ select coalesce((select auth.jwt()) ->> 'aal', '') = 'aal2'; $$; comment on function private.has_mfa() is 'true when the verified token of the request carries aal = aal2 (exact). Policies and catalogue RPCs.'; -- For the platform role and catalogue rights RPCs only (rare, administrative): -- the claim, plus the session named by the token still existing at aal2, so a -- token outliving a sign-out or a factor removal (GoTrue downgrades the -- session row) is refused at once instead of for up to one token lifetime. create or replace function private.has_aal2_session() returns boolean language plpgsql stable security definer set search_path = '' as $$ declare v_uid uuid := (select auth.uid()); v_session text := (select auth.jwt()) ->> 'session_id'; begin if v_uid is null or not private.has_mfa() or v_session is null or v_session !~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' then return false; end if; return exists ( select 1 from auth.sessions s where s.id = v_session::uuid and s.user_id = v_uid and s.aal = 'aal2' and (s.not_after is null or s.not_after > now()) ); end; $$; comment on function private.has_aal2_session() is 'has_mfa() and the token''s session_id names a live auth.sessions row of the caller at aal2. Role and rights RPCs only.'; -- Active profile and confirmed email: the precondition of every right (§5.1). create or replace function private.is_confirmed_active_account(p_user_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from public.profiles p join auth.users u on u.id = p.id where p.id = p_user_id and p.active and u.email_confirmed_at is not null ); $$; -- A verified TOTP factor (contract §5.2: a capability or a platform role is -- never handed to an account whose first factor a password holder could still -- enrol). create or replace function private.has_verified_totp(p_user_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from auth.mfa_factors f where f.user_id = p_user_id and f.factor_type = 'totp' and f.status = 'verified' ); $$; -- --------------------------------------------------------------------------- -- Catalogue rights (contract §5.2, §5.3) -- --------------------------------------------------------------------------- create type public.catalog_grant_kind as enum ( 'catalog_reader', 'sheet_editor', 'sheet_reviewer', 'sheet_publisher' ); create table public.anatomical_catalog_grants ( id uuid primary key default gen_random_uuid(), -- restrict: the review trail names grant holders; an account that held a -- right is removed by an operator decision, never by a cascade. user_id uuid not null references public.profiles (id) on delete restrict, grant_kind public.catalog_grant_kind not null, reason text not null constraint anatomical_catalog_grants_reason_length check (char_length(reason) between 1 and 1000), verification_note text constraint anatomical_catalog_grants_note_length check (char_length(verification_note) between 1 and 1000), granted_by uuid not null references public.profiles (id) on delete restrict, granted_at timestamptz not null default now(), expires_at timestamptz, revoked_by uuid references public.profiles (id) on delete restrict, revoked_at timestamptz, revocation_reason text constraint anatomical_catalog_grants_revocation_reason_length check (char_length(revocation_reason) between 1 and 1000), constraint anatomical_catalog_grants_note_required check (grant_kind = 'catalog_reader' or verification_note is not null), constraint anatomical_catalog_grants_expiry_after_grant check (expires_at is null or expires_at > granted_at), constraint anatomical_catalog_grants_revocation_consistent check ((revoked_at is null and revoked_by is null and revocation_reason is null) or (revoked_at is not null and revoked_by is not null and revocation_reason is not null)) ); comment on table public.anatomical_catalog_grants is 'Catalogue rights (contract §5.2): capabilities sheet_editor / sheet_reviewer / sheet_publisher and the provisional catalog_reader. Written by grant_catalog_right / revoke_catalog_right only; a row is revoked once, never otherwise updated or deleted; a renewal is a new row.'; create index anatomical_catalog_grants_user_kind_idx on public.anatomical_catalog_grants (user_id, grant_kind) where revoked_at is null; -- Rows are append-only apart from the single revocation, for every role. create or replace function private.guard_catalog_grant() returns trigger language plpgsql set search_path = '' as $$ begin if tg_op = 'DELETE' then raise exception 'catalog grants are never deleted' using errcode = '42501'; end if; if old.revoked_at is not null or (new.id, new.user_id, new.grant_kind, new.reason, new.verification_note, new.granted_by, new.granted_at, new.expires_at) is distinct from (old.id, old.user_id, old.grant_kind, old.reason, old.verification_note, old.granted_by, old.granted_at, old.expires_at) then raise exception 'a catalog grant changes only by its revocation' using errcode = '42501'; end if; return new; end; $$; create trigger anatomical_catalog_grants_guard before update or delete on public.anatomical_catalog_grants for each row execute function private.guard_catalog_grant(); -- The grant and its eligibility, without any assurance level: for stored -- actors (acknowledgers, approvers) and as the base of caller_has. Read at -- every request, so an expiry, a revocation, a deactivation or a lost -- platform role takes effect at once, whatever an older token says. create or replace function private.grant_active(p_user_id uuid, p_kind public.catalog_grant_kind) returns boolean language sql stable security definer set search_path = '' as $$ select p_user_id is not null and private.is_confirmed_active_account(p_user_id) and exists ( select 1 from public.anatomical_catalog_grants g where g.user_id = p_user_id and g.grant_kind = p_kind and g.revoked_at is null and (g.expires_at is null or g.expires_at > now()) ) -- Editor and publisher need an active owner or admin role (O1); losing -- it suspends the capability without touching the grant row. and (p_kind not in ('sheet_editor', 'sheet_publisher') or private.is_platform_admin(p_user_id)); $$; -- The calling session holds the capability, at aal2. create or replace function private.caller_has(p_kind public.catalog_grant_kind) returns boolean language sql stable security definer set search_path = '' as $$ select private.has_mfa() and private.grant_active((select auth.uid()), p_kind); $$; -- Editor, reviewer or publisher, at aal2: reads drafts, rounds and history. create or replace function private.caller_is_catalog_staff() returns boolean language sql stable security definer set search_path = '' as $$ select private.has_mfa() and ( private.grant_active((select auth.uid()), 'sheet_editor') or private.grant_active((select auth.uid()), 'sheet_reviewer') or private.grant_active((select auth.uid()), 'sheet_publisher') ); $$; -- Provisional catalogue access (O21), fail-closed: a catalog_reader grant or -- any capability; no assurance level; platform roles give nothing. create or replace function private.can_read_official_catalog(p_user_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select private.grant_active(p_user_id, 'catalog_reader') or private.grant_active(p_user_id, 'sheet_editor') or private.grant_active(p_user_id, 'sheet_reviewer') or private.grant_active(p_user_id, 'sheet_publisher'); $$; comment on function private.can_read_official_catalog(uuid) is 'Provisional catalogue access (contract §5.3, O21): active catalog_reader grant or capability, active profile, confirmed email. Replaced by the entitlements of plan Step 11.'; -- The fixed id of the historical equine sheet (contract §14.2, the core's -- alias registry pins the same id). create or replace function private.historical_sheet_id() returns uuid language sql immutable set search_path = '' as $$ select '503dc301-a8b1-50d4-9e44-0919b2fc2916'::uuid; $$; -- A sheet of the catalogue: official, not deleted, with at least one -- published or deprecated version (archived sheets included, O8). A sheet -- that only has a draft is invisible to readers. create or replace function private.is_catalog_sheet(p_sheet_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from public.anatomical_sheets s where s.id = p_sheet_id and s.is_official and s.status <> 'deleted' and exists ( select 1 from public.anatomical_sheet_versions v where v.sheet_id = s.id and v.status in ('published', 'deprecated') ) ); $$; -- The reader row predicate (§5.3), the same on sheets, versions, asset rows -- and objects. create or replace function private.can_read_catalog_sheet(p_user_id uuid, p_sheet_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select private.can_read_official_catalog(p_user_id) and private.is_catalog_sheet(p_sheet_id); $$; -- Capability holders at aal2 read every non-deleted official sheet. create or replace function private.caller_can_see_official_sheet(p_sheet_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select private.caller_is_catalog_staff() and exists ( select 1 from public.anatomical_sheets s where s.id = p_sheet_id and s.is_official and s.status <> 'deleted' ); $$; -- An asset row referenced by a published or deprecated version of its sheet. create or replace function private.asset_in_catalog_version(p_asset_id uuid, p_sheet_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from public.anatomical_sheet_versions v where v.sheet_id = p_sheet_id and v.status in ('published', 'deprecated') and p_asset_id::text = any (private.sheet_document_asset_ids(v.content)) ); $$; -- Official sheets only, edited through the sheet_editor capability at aal2 -- (F14). Personal sheets are closed in V1-2 (D5): no branch for them. The -- user argument must be the caller: the capability is the session's. create or replace function private.can_edit_sheet(p_user_id uuid, p_sheet_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select p_user_id is not null and p_user_id = (select auth.uid()) and private.caller_has('sheet_editor') and exists ( select 1 from public.anatomical_sheets s where s.id = p_sheet_id and s.is_official and s.status <> 'deleted' ); $$; comment on function private.can_edit_sheet(uuid, uuid) is 'The caller (p_user_id = auth.uid()) holds sheet_editor at aal2 and the sheet is official and not deleted (contract §5.2, F14).'; -- Every catalogue write requires an active account with a confirmed email (F5). create or replace function private.require_actor() returns uuid language plpgsql stable set search_path = '' as $$ declare v_actor uuid := auth.uid(); begin if v_actor is null or not private.is_confirmed_active_account(v_actor) then raise exception 'rights.account' using errcode = '42501'; end if; return v_actor; end; $$; -- --------------------------------------------------------------------------- -- Write budget per actor for catalogue writes (V1-1 D15 pattern, contract -- §6.2, §13): fixed UTC windows, 300 audited writes an hour, 2 000 a day. -- Taken last in each RPC, after the entity locks. -- --------------------------------------------------------------------------- create table private.catalog_write_budgets ( user_id uuid primary key references public.profiles (id) on delete cascade, hour_start timestamptz not null, hour_count integer not null, day_start timestamptz not null, day_count integer not null ); comment on table private.catalog_write_budgets is 'Audited catalogue writes per account in the current UTC hour and day. Written only by private.consume_catalog_write_budget.'; create or replace function private.consume_catalog_write_budget(p_user_id uuid) returns void language plpgsql security definer set search_path = '' as $$ declare v_hour timestamptz := date_trunc('hour', now(), 'UTC'); v_day timestamptz := date_trunc('day', now(), 'UTC'); v_hour_count integer; v_day_count integer; begin insert into private.catalog_write_budgets as b (user_id, hour_start, hour_count, day_start, day_count) values (p_user_id, v_hour, 1, v_day, 1) on conflict (user_id) do update set hour_count = case when b.hour_start = excluded.hour_start then b.hour_count + 1 else 1 end, hour_start = excluded.hour_start, day_count = case when b.day_start = excluded.day_start then b.day_count + 1 else 1 end, day_start = excluded.day_start returning b.hour_count, b.day_count into v_hour_count, v_day_count; if v_hour_count > 300 or v_day_count > 2000 then raise exception 'rate_limited' using errcode = 'P0001'; end if; end; $$; -- --------------------------------------------------------------------------- -- Rights RPCs (contract §5.2): the platform owner, from a live aal2 session -- --------------------------------------------------------------------------- -- The caller is an active, confirmed platform owner with a live aal2 session. -- The assurance check comes first: a password-only session gets the same -- answer whatever its role (no oracle on who is owner). create or replace function private.require_rights_owner() returns uuid language plpgsql stable security definer set search_path = '' as $$ declare v_actor uuid := auth.uid(); begin if v_actor is null then raise exception 'rights.account' using errcode = '42501'; end if; if not private.has_aal2_session() then raise exception 'rights.mfa' using errcode = '42501'; end if; if not (private.is_confirmed_active_account(v_actor) and private.is_platform_owner(v_actor)) then raise exception 'rights.owner' using errcode = '42501'; end if; return v_actor; end; $$; create or replace function public.grant_catalog_right( user_id uuid, kind public.catalog_grant_kind, reason text, verification_note text default null, expires_at timestamptz default null ) returns uuid language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_column declare v_actor uuid := private.require_rights_owner(); v_reason text := btrim(grant_catalog_right.reason); v_note text := nullif(btrim(grant_catalog_right.verification_note), ''); v_id uuid; begin if grant_catalog_right.kind is null or v_reason is null or char_length(v_reason) not between 1 and 1000 or char_length(v_note) > 1000 or (grant_catalog_right.kind <> 'catalog_reader' and v_note is null) or grant_catalog_right.expires_at <= now() then raise exception 'invalid_input' using errcode = 'P0001'; end if; -- An owner may grant itself any capability, `sheet_reviewer` included (M6, -- owner decision 2026-10-28): the separation of duties is a per-round rule, -- so no author of a round acknowledges, approves or counts at publication. -- Serialises the grants of one account: one active row per kind. perform 1 from public.profiles p where p.id = grant_catalog_right.user_id for no key update; if not found then raise exception 'grant.target_invalid' using errcode = 'P0001'; end if; if not private.is_confirmed_active_account(grant_catalog_right.user_id) or (grant_catalog_right.kind <> 'catalog_reader' and not private.has_verified_totp(grant_catalog_right.user_id)) or (grant_catalog_right.kind in ('sheet_editor', 'sheet_publisher') and not private.is_platform_admin(grant_catalog_right.user_id)) then raise exception 'grant.ineligible' using errcode = 'P0001'; end if; if exists ( select 1 from public.anatomical_catalog_grants g where g.user_id = grant_catalog_right.user_id and g.grant_kind = grant_catalog_right.kind and g.revoked_at is null and (g.expires_at is null or g.expires_at > now()) ) then raise exception 'grant.active' using errcode = 'P0001'; end if; perform private.consume_catalog_write_budget(v_actor); insert into public.anatomical_catalog_grants ( user_id, grant_kind, reason, verification_note, granted_by, expires_at ) values ( grant_catalog_right.user_id, grant_catalog_right.kind, v_reason, v_note, v_actor, grant_catalog_right.expires_at ) returning id into v_id; perform private.log_event( v_actor, 'catalog_grant.granted', 'catalog_grant', v_id, grant_catalog_right.user_id, null, jsonb_build_object('kind', grant_catalog_right.kind, 'reason', v_reason, 'verification_note', v_note, 'expires_at', grant_catalog_right.expires_at) ); return v_id; end; $$; create or replace function public.revoke_catalog_right(grant_id uuid, reason text) returns uuid language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_column declare v_actor uuid := private.require_rights_owner(); v_reason text := btrim(revoke_catalog_right.reason); v_row public.anatomical_catalog_grants; begin if v_reason is null or char_length(v_reason) not between 1 and 1000 then raise exception 'invalid_input' using errcode = 'P0001'; end if; select g.* into v_row from public.anatomical_catalog_grants g where g.id = revoke_catalog_right.grant_id for update; if not found then raise exception 'not_found' using errcode = '42501'; end if; if v_row.revoked_at is not null then raise exception 'grant.revoked' using errcode = 'P0001'; end if; perform private.consume_catalog_write_budget(v_actor); update public.anatomical_catalog_grants g set revoked_at = now(), revoked_by = v_actor, revocation_reason = v_reason where g.id = v_row.id; perform private.log_event( v_actor, 'catalog_grant.revoked', 'catalog_grant', v_row.id, v_row.user_id, null, jsonb_build_object('kind', v_row.grant_kind, 'reason', v_reason) ); return v_row.id; end; $$; -- The rights screen (contract §5.2, §11.1 /app/admin/access): every grant with -- its holder, state and reasons. create or replace function public.list_catalog_grants() returns jsonb language plpgsql stable security definer set search_path = '' as $$ begin perform private.require_rights_owner(); return coalesce(( select jsonb_agg(jsonb_build_object( 'id', g.id, 'user_id', g.user_id, 'display_name', p.display_name, 'email', u.email, 'kind', g.grant_kind, 'reason', g.reason, 'verification_note', g.verification_note, 'granted_by', g.granted_by, 'granted_by_name', gp.display_name, 'granted_at', g.granted_at, 'expires_at', g.expires_at, 'revoked_at', g.revoked_at, 'revoked_by', g.revoked_by, 'revocation_reason', g.revocation_reason, 'state', case when g.revoked_at is not null then 'revoked' when g.expires_at is not null and g.expires_at <= now() then 'expired' when g.expires_at is not null and g.expires_at <= now() + interval '30 days' then 'expiring' else 'active' end, 'effective', private.grant_active(g.user_id, g.grant_kind) ) order by g.granted_at desc, g.id) from public.anatomical_catalog_grants g join public.profiles p on p.id = g.user_id join auth.users u on u.id = g.user_id left join public.profiles gp on gp.id = g.granted_by ), '[]'::jsonb); end; $$; -- Finds the account a right is granted to, by e-mail (owner only). create or replace function public.lookup_catalog_account(email text) returns jsonb language plpgsql stable security definer set search_path = '' as $$ declare v_result jsonb; begin perform private.require_rights_owner(); if lookup_catalog_account.email is null or char_length(lookup_catalog_account.email) not between 3 and 320 then raise exception 'invalid_input' using errcode = 'P0001'; end if; select jsonb_build_object( 'user_id', p.id, 'display_name', p.display_name, 'active', p.active, 'email_confirmed', u.email_confirmed_at is not null, 'verified_totp', private.has_verified_totp(p.id), 'platform_roles', coalesce(( select jsonb_agg(r.role order by r.role) from public.profile_roles r where r.user_id = p.id and r.revoked_at is null ), '[]'::jsonb) ) into v_result from auth.users u join public.profiles p on p.id = u.id where lower(u.email) = lower(lookup_catalog_account.email); if v_result is null then raise exception 'not_found' using errcode = '42501'; end if; return v_result; end; $$; comment on function public.grant_catalog_right(uuid, public.catalog_grant_kind, text, text, timestamptz) is 'Platform owner, live aal2 session: grants a catalogue right with a reason (and a verification note for capabilities); eligibility checked; one active row per account and kind. Audited.'; comment on function public.revoke_catalog_right(uuid, text) is 'Platform owner, live aal2 session: revokes a grant once, reason required. Audited.'; -- --------------------------------------------------------------------------- -- AR-1 B: platform role changes (contract §5.7) -- --------------------------------------------------------------------------- -- One transaction-scoped lock serialises every role change, so the owner -- count of the last-owner guard cannot be read by two writers at once (F18). create or replace function private.lock_platform_roles() returns void language sql volatile set search_path = '' as $$ select pg_advisory_xact_lock(hashtextextended('faunosteo.platform_roles', 0)); $$; -- System write path. A non-null actor must be the calling session's account -- at aal2 with a live session; a null actor (bootstrap, recovery) is accepted -- only when the request carries no JWT at all, so neither an API session nor -- the service role reaches the operator path (§5.7.5). create or replace function private.check_role_actor(p_actor uuid) returns void language plpgsql stable security definer set search_path = '' as $$ begin if p_actor is null then if (select auth.role()) is not null or (select auth.uid()) is not null then raise exception 'operator_path_only' using errcode = '42501'; end if; elsif p_actor is distinct from (select auth.uid()) or not private.has_aal2_session() then raise exception 'mfa_required' using errcode = '42501'; end if; end; $$; create or replace function private.assign_platform_role( p_actor uuid, p_target uuid, p_role public.platform_role ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_id uuid; begin perform private.check_role_actor(p_actor); perform private.lock_platform_roles(); if not exists (select 1 from public.profiles where id = p_target and active) then raise exception 'target_invalid' using errcode = 'P0001'; end if; -- Through a session, the target already holds a verified factor. The -- operator path is the bootstrap of §5.7.1, which checks it out of band. if p_actor is not null and not private.has_verified_totp(p_target) then raise exception 'target_factor_missing' using errcode = 'P0001'; end if; if exists ( select 1 from public.profile_roles where user_id = p_target and role = p_role and revoked_at is null ) then raise exception 'role_active' using errcode = 'P0001'; end if; insert into public.profile_roles (user_id, role, granted_by) values (p_target, p_role, p_actor) returning id into v_id; perform private.log_event( p_actor, 'platform_role.granted', 'profile_role', v_id, p_target, null, jsonb_build_object('role', p_role) ); return v_id; end; $$; create or replace function private.withdraw_platform_role( p_actor uuid, p_target uuid, p_role public.platform_role, p_reason text ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_id uuid; begin perform private.check_role_actor(p_actor); perform private.lock_platform_roles(); if not exists (select 1 from public.profiles where id = p_target and active) then raise exception 'target_invalid' using errcode = 'P0001'; end if; select id into v_id from public.profile_roles where user_id = p_target and role = p_role and revoked_at is null; if v_id is null then raise exception 'role_inactive' using errcode = 'P0001'; end if; -- Lock-out guard, under the lock: another active owner (active profile, the -- has_platform_role semantics) must remain once this row is revoked. if p_role = 'owner' and not exists ( select 1 from public.profile_roles r join public.profiles p on p.id = r.user_id where r.role = 'owner' and r.revoked_at is null and p.active and r.id <> v_id ) then raise exception 'last_owner' using errcode = 'P0001'; end if; update public.profile_roles set revoked_at = now(), revoked_by = p_actor, revoke_reason = p_reason where id = v_id; perform private.log_event( p_actor, 'platform_role.revoked', 'profile_role', v_id, p_target, null, jsonb_build_object('role', p_role, 'reason', p_reason) ); return v_id; end; $$; -- Order of checks (§5.7), every one before any write: authenticated, live -- aal2 session (the same answer whatever the caller's role), not self, -- can_manage_platform_role, then the operation itself in the private writer. create or replace function public.grant_platform_role( target_user_id uuid, role public.platform_role ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_actor uuid := auth.uid(); begin if v_actor is null then raise exception 'authentication_required' using errcode = '42501'; end if; if not private.has_aal2_session() then raise exception 'mfa_required' using errcode = '42501'; end if; perform private.lock_platform_roles(); if v_actor = target_user_id then raise exception 'self_target' using errcode = '42501'; end if; if not private.can_manage_platform_role(v_actor, role) then raise exception 'forbidden' using errcode = '42501'; end if; return private.assign_platform_role(v_actor, target_user_id, role); end; $$; create or replace function public.revoke_platform_role( target_user_id uuid, role public.platform_role, reason text default null ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_actor uuid := auth.uid(); begin if v_actor is null then raise exception 'authentication_required' using errcode = '42501'; end if; if not private.has_aal2_session() then raise exception 'mfa_required' using errcode = '42501'; end if; perform private.lock_platform_roles(); if v_actor = target_user_id then raise exception 'self_target' using errcode = '42501'; end if; if not private.can_manage_platform_role(v_actor, role) then raise exception 'forbidden' using errcode = '42501'; end if; if char_length(reason) > 500 then raise exception 'invalid_input' using errcode = 'P0001'; end if; return private.withdraw_platform_role(v_actor, target_user_id, role, reason); end; $$; comment on function private.assign_platform_role(uuid, uuid, public.platform_role) is 'System write path. Non-null actor = the caller at aal2 (live session), target with a verified TOTP factor. Null actor = operator bootstrap or recovery from a privileged SQL session without JWT (contract §5.7.1, §5.7.5).'; comment on function public.grant_platform_role(uuid, public.platform_role) is 'Grants a platform role to another user from a live aal2 session (AR-1 B). owner: any role; admin: moderator/support. Audited.'; comment on function public.revoke_platform_role(uuid, public.platform_role, text) is 'Revokes an active platform role of another user from a live aal2 session (AR-1 B); one active owner always kept, race-free. Audited.'; -- --------------------------------------------------------------------------- -- Immutability guards (every role) -- --------------------------------------------------------------------------- -- A version that was ever published is never deleted (F13); the historical -- sheet's versions are never deleted (§14.2). create or replace function private.guard_sheet_version_delete() returns trigger language plpgsql security definer set search_path = '' as $$ declare v_dependents text[]; begin if old.status in ('published', 'deprecated') then raise exception 'version % was published: it is never deleted', old.id using errcode = '55000'; end if; if old.sheet_id = private.historical_sheet_id() then raise exception 'sheet.historical_read_only' using errcode = '42501'; end if; v_dependents := private.sheet_version_dependents(old.id); if coalesce(array_length(v_dependents, 1), 0) > 0 then raise exception 'version % still has dependents: %', old.id, v_dependents using errcode = '55000'; end if; return old; end; $$; -- Asset rows are content-addressed and immutable (F16): only uploaded_at -- moves, once, from null to a time. The historical sheet's rows are never -- deleted. create or replace function private.guard_sheet_asset() returns trigger language plpgsql set search_path = '' as $$ begin if tg_op = 'DELETE' then if old.sheet_id = private.historical_sheet_id() then raise exception 'sheet.historical_read_only' using errcode = '42501'; end if; return old; end if; if (new.id, new.sheet_id, new.sha256, new.byte_size, new.content_type, new.file_name, new.created_at) is distinct from (old.id, old.sheet_id, old.sha256, old.byte_size, old.content_type, old.file_name, old.created_at) or (old.uploaded_at is not null and new.uploaded_at is distinct from old.uploaded_at) then raise exception 'sheet asset rows are immutable' using errcode = '42501'; end if; return new; end; $$; create trigger anatomical_sheet_assets_guard before update or delete on public.anatomical_sheet_assets for each row execute function private.guard_sheet_asset(); -- The historical equine sheet row (§14.2): never deleted, and nothing but its -- version counter (the converging seed inserts its single draft) and -- updated_at ever changes. create or replace function private.guard_historical_sheet() returns trigger language plpgsql set search_path = '' as $$ declare v_new public.anatomical_sheets; begin if old.id <> private.historical_sheet_id() then return case when tg_op = 'DELETE' then old else new end; end if; if tg_op = 'DELETE' then raise exception 'sheet.historical_read_only' using errcode = '42501'; end if; v_new := new; v_new.last_version_number := old.last_version_number; v_new.updated_at := old.updated_at; if v_new is distinct from old then raise exception 'sheet.historical_read_only' using errcode = '42501'; end if; return new; end; $$; create trigger anatomical_sheets_guard_historical before update or delete on public.anatomical_sheets for each row execute function private.guard_historical_sheet(); -- --------------------------------------------------------------------------- -- Closed paths (D5: no personal sheet, fork or sharing in V1-2; F1, F3, F6; -- the unreviewed publication of Step 4, F2, F4, F11, whose reviewed -- replacement comes with M5) -- --------------------------------------------------------------------------- drop function public.create_anatomical_sheet(text, text, text, jsonb, text, public.sheet_visibility, boolean); drop function public.fork_anatomical_sheet(uuid, text, text, public.sheet_visibility); drop function public.set_sheet_visibility(uuid, public.sheet_visibility); drop function public.publish_sheet_version(uuid); drop function private.copy_sheet_assets(uuid, uuid, uuid, jsonb); -- --------------------------------------------------------------------------- -- Policies: the reader predicate and the staff branch (F8, F15); no write -- policy (F9) -- --------------------------------------------------------------------------- drop policy anatomical_sheets_select_usable_or_editable on public.anatomical_sheets; drop policy anatomical_sheets_update_editor on public.anatomical_sheets; drop policy anatomical_sheet_versions_select on public.anatomical_sheet_versions; drop policy anatomical_sheet_versions_update_own_draft on public.anatomical_sheet_versions; drop policy anatomical_sheet_assets_select on public.anatomical_sheet_assets; create policy anatomical_sheets_select_catalog on public.anatomical_sheets for select to authenticated using ( private.can_read_catalog_sheet((select auth.uid()), id) or private.caller_can_see_official_sheet(id) ); create policy anatomical_sheet_versions_select_catalog on public.anatomical_sheet_versions for select to authenticated using ( (status in ('published', 'deprecated') and private.can_read_catalog_sheet((select auth.uid()), sheet_id)) or private.caller_can_see_official_sheet(sheet_id) ); create policy anatomical_sheet_assets_select_catalog on public.anatomical_sheet_assets for select to authenticated using ( (uploaded_at is not null and private.can_read_catalog_sheet((select auth.uid()), sheet_id) and private.asset_in_catalog_version(id, sheet_id)) or private.caller_can_see_official_sheet(sheet_id) ); -- Storage read (§9.4): readers, the drawings of published or deprecated -- versions of catalogue sheets; capability holders at aal2, every uploaded -- drawing of an official sheet; nobody, the object of a pending row. create or replace function private.can_read_sheet_asset_object(p_user_id uuid, p_object_name text) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from public.anatomical_sheet_assets a where a.sha256 = left(p_object_name, 64) and p_object_name = a.sha256 || '.svg' and a.uploaded_at is not null and ( (p_user_id = (select auth.uid()) and private.caller_can_see_official_sheet(a.sheet_id)) or (private.can_read_catalog_sheet(p_user_id, a.sheet_id) and private.asset_in_catalog_version(a.id, a.sheet_id)) ) ); $$; comment on function private.can_read_sheet_asset_object(uuid, text) is 'Storage read predicate for bucket anatomical-sheet-assets (contract §9.4): confirmed object of a published or deprecated version of a catalogue sheet for readers, of any official sheet for capability holders at aal2.'; -- The visibility branch is gone from every predicate (§5.3). drop function private.can_use_sheet(uuid, uuid); create policy anatomical_catalog_grants_select on public.anatomical_catalog_grants for select to authenticated using ( user_id = (select auth.uid()) or (private.has_mfa() and private.is_platform_owner((select auth.uid()))) or (grant_kind <> 'catalog_reader' and private.caller_is_catalog_staff()) ); -- --------------------------------------------------------------------------- -- Privileges. Supabase default privileges give ALL on public tables to anon, -- authenticated and service_role: every catalogue and identity table starts -- from zero for the three (F12, F17). The service key keeps the RPCs granted -- to it (worker, upload confirmation, operator read) and stays a GoTrue admin -- credential no table privilege can bound (contract §5.7.5, §13). -- --------------------------------------------------------------------------- revoke all on public.species from public, anon, authenticated, service_role; revoke all on public.anatomical_sheets from public, anon, authenticated, service_role; revoke all on public.anatomical_sheet_versions from public, anon, authenticated, service_role; revoke all on public.anatomical_sheet_assets from public, anon, authenticated, service_role; revoke all on public.anatomical_catalog_grants from public, anon, authenticated, service_role; revoke all on public.profile_roles from service_role; revoke all on public.profiles from service_role; grant select on public.species to authenticated; -- Reader columns (§5.3): no last_version_number, updated_at, ownership, -- provenance or deletion stamp; the admin space reads those through its RPCs. grant select (id, species_id, name, slug, description, is_official, status, current_version_id, archived_at, created_at) on public.anatomical_sheets to authenticated; grant select (id, sheet_id, version_number, status, document_schema_version, content, change_note, published_at, deprecated_at, created_at) on public.anatomical_sheet_versions to authenticated; grant select (id, sheet_id, sha256, byte_size, content_type, file_name, uploaded_at, created_at) on public.anatomical_sheet_assets to authenticated; grant select on public.anatomical_catalog_grants to authenticated; alter table public.anatomical_catalog_grants enable row level security; revoke all on function public.grant_platform_role(uuid, public.platform_role) from public, anon, service_role; revoke all on function public.revoke_platform_role(uuid, public.platform_role, text) from public, anon, service_role; grant execute on function public.grant_platform_role(uuid, public.platform_role) to authenticated; grant execute on function public.revoke_platform_role(uuid, public.platform_role, text) to authenticated; revoke all on function public.grant_catalog_right(uuid, public.catalog_grant_kind, text, text, timestamptz) from public, anon, service_role; revoke all on function public.revoke_catalog_right(uuid, text) from public, anon, service_role; revoke all on function public.list_catalog_grants() from public, anon, service_role; revoke all on function public.lookup_catalog_account(text) from public, anon, service_role; grant execute on function public.grant_catalog_right(uuid, public.catalog_grant_kind, text, text, timestamptz) to authenticated; grant execute on function public.revoke_catalog_right(uuid, text) to authenticated; grant execute on function public.list_catalog_grants() to authenticated; grant execute on function public.lookup_catalog_account(text) to authenticated; -- Private helpers: EXECUTE for nobody, except the predicates the policies -- name, granted to authenticated. revoke all on function private.has_mfa() from public, anon, authenticated, service_role; revoke all on function private.has_aal2_session() from public, anon, authenticated, service_role; revoke all on function private.is_confirmed_active_account(uuid) from public, anon, authenticated, service_role; revoke all on function private.has_verified_totp(uuid) from public, anon, authenticated, service_role; revoke all on function private.guard_catalog_grant() from public, anon, authenticated, service_role; revoke all on function private.grant_active(uuid, public.catalog_grant_kind) from public, anon, authenticated, service_role; revoke all on function private.caller_has(public.catalog_grant_kind) from public, anon, authenticated, service_role; revoke all on function private.caller_is_catalog_staff() from public, anon, authenticated, service_role; revoke all on function private.can_read_official_catalog(uuid) from public, anon, authenticated, service_role; revoke all on function private.historical_sheet_id() from public, anon, authenticated, service_role; revoke all on function private.is_catalog_sheet(uuid) from public, anon, authenticated, service_role; revoke all on function private.can_read_catalog_sheet(uuid, uuid) from public, anon, authenticated, service_role; revoke all on function private.caller_can_see_official_sheet(uuid) from public, anon, authenticated, service_role; revoke all on function private.asset_in_catalog_version(uuid, uuid) from public, anon, authenticated, service_role; revoke all on function private.can_edit_sheet(uuid, uuid) from public, anon, authenticated, service_role; revoke all on function private.require_actor() from public, anon, authenticated, service_role; revoke all on function private.consume_catalog_write_budget(uuid) from public, anon, authenticated, service_role; revoke all on function private.require_rights_owner() from public, anon, authenticated, service_role; revoke all on function private.lock_platform_roles() from public, anon, authenticated, service_role; revoke all on function private.check_role_actor(uuid) from public, anon, authenticated, service_role; revoke all on function private.assign_platform_role(uuid, uuid, public.platform_role) from public, anon, authenticated, service_role; revoke all on function private.withdraw_platform_role(uuid, uuid, public.platform_role, text) from public, anon, authenticated, service_role; revoke all on function private.guard_sheet_version_delete() from public, anon, authenticated, service_role; revoke all on function private.guard_sheet_asset() from public, anon, authenticated, service_role; revoke all on function private.guard_historical_sheet() from public, anon, authenticated, service_role; revoke all on function private.can_read_sheet_asset_object(uuid, text) from public, anon, authenticated, service_role; revoke all on private.catalog_write_budgets from public, anon, authenticated, service_role; grant execute on function private.has_mfa() to authenticated; grant execute on function private.is_platform_owner(uuid) to authenticated; grant execute on function private.caller_is_catalog_staff() to authenticated; grant execute on function private.can_read_catalog_sheet(uuid, uuid) to authenticated; grant execute on function private.caller_can_see_official_sheet(uuid) to authenticated; grant execute on function private.asset_in_catalog_version(uuid, uuid) to authenticated; grant execute on function private.can_read_sheet_asset_object(uuid, text) to authenticated;