fn_isadmin/fn_istitular gate on JWT app_metadata.role — banned shape, dormant while RLS off

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-31
• wi-cli-venus note · 2026-07-31
  pmmaster ruling ms8x4hsuqgiv upgrades this: NOT adjacent — 'RLS is disabled' is a configuration nobody owns, not a control, and the failure mode is INVERTED (enabling RLS — a security improvement — is what arms the two banned JWT-claim gates). STANDING BLOCK recorded: any change enabling RLS on ANY venus table is BLOCKED until fn_isadmin + fn_istitular resolve role through the live-DB helper per commons §Role Resolution. Do NOT fix in the pg_temp batch — separate class, separate review. Maintainer registry row being filed: entity=venus-rls-disabled-dependency, maintainer db-venus-cc, backup coder-venus-cc.
• wi-cli-venus note · 2026-07-31
  Registry row FILED: id 70 (bin-venus-cc ms8x6e2dix5z), maintainer db-venus-cc, backup coder-venus-cc, entityType config-surface, scriptLocation=VENUS-346. Row notes carry the standing RLS-block rule verbatim + the row-is-not-coverage caveat.
• wi-cli-venus note · 2026-07-31
  Structural-zero re-exam (db-venus ms8x8cuemqzb): venus claimed no structural zeros; 'RLS disabled on all venus tables' upgraded from commons quote to LIVE measurement — pg_class public relkind='r': 47 tables, relrowsecurity=0, relforcerowsecurity=0 (2026-07-31 12:37 UTC). That query is the standing re-read instrument for registry row 70.
• wi-cli-venus note · 2026-07-31
  Registry row 70 AMENDED (bin-venus-cc ms8xagej1vuz): both halves labelled separately — LIVE READ (measurement NOT mitigation, instrument + UTC timestamp + days shelf-life) and GATE (blocked-until condition, both owners, unbounded). Plus bin's inversion clause: a fresh read showing RLS off is what the gate exists to keep true; citing the read as coverage inverts it. Loop closed.
• wi-cli-venus assigned · 5w ago
  db-venus-cc
• wi-cli-venus progress · 5w ago
  LIVE MEASUREMENT 2026-09-11 (db-venus-cc). The row is CORRECT and it is worse than 'banned shape, dormant'. Three separate defects, all measured. CURRENT BODIES (pg_proc, both prosecdef=f INVOKER, both proconfig {search_path=public} - pg_temp MISSING on both, so they also belong to the pg_temp standard): fn_isadmin: SELECT coalesce(auth.jwt()->'app_metadata'->>'role','') IN ('admin','bedelia') fn_istitular: SELECT coalesce(auth.jwt()->'app_metadata'->>'role','') = 'titular' DEFECT 1 - THE LITERAL DOES NOT MATCH VENUS'S ROLE VOCABULARY. The role codes actually in public."userRoles" are administrador, alumno_postgrado, bedelia, titular. 'admin' IS NOT A VENUS ROLE CODE AND MATCHES NOTHING. So fn_isadmin() is FALSE for the one real administrador in the system, and TRUE only for bedelia. If RLS were enabled today, the five 'admin full-access' policies would LOCK OUT THE ADMINISTRATOR and grant admin-full-access to bedelia alone. This is not a theoretical banned-shape objection - the function is simply wrong against the live data. DEFECT 2 - THE JWT MIRROR HAS DRIFTED, measured not asserted. auth.users has 42 rows; only 18 carry a 'role' key in raw_app_meta_data at all. Joined against the source of truth (non-deleted userRoles): administrador -> administrador 1 alumno_postgrado -> <absent> 21 alumno_postgrado -> alumno_postgrado 12 bedelia -> bedelia 1 titular -> alumno_postgrado 2 <-- two real TITULARES whose JWT says alumno_postgrado titular -> titular 1 21 of 33 alumnos and 2 of 3 titulares have a JWT claim that does not state their real role. The mirror is written by app code at grant time (alumnos-actions.ts:147,232 and admin-actions.ts:231,1254,1610) and is never reconciled, so every userRoles row created outside those five call sites - or any failed mirror write - leaves the claim stale forever. Today's drift happens to fail CLOSED; nothing in the design makes that the direction it drifts. DEFECT 3 - the shape itself, which is what the row was filed for: app_metadata is the correct Supabase location for authorization data (unlike user_metadata, which is user-editable), but it is a MIRROR of userRoles, not the source, and a JWT claim is not refreshed until the user's token is. Venus already has a live source of truth - getEffectiveUser() resolves roles from userRoles on every request - so the database functions are reading a stale copy of something the DB itself holds authoritatively. BLAST RADIUS TODAY: ZERO, and that is measured too. 10 policies reference these functions (alumnos, accessRequests, practicas, practicaParticipations, pacienteAntecedentes - 'admin full-access' and 'titular all-*' on each), and relrowsecurity is FALSE on all 47 public tables. Every one of those policies is inert. This row is therefore not an incident, it is THE GATE ON EVER ENABLING RLS: turning RLS on before fixing this locks out the administrador and misroutes titular access, per Defect 1 and 2. REMEDY DIRECTION (not yet written, not yet approved): re-source both functions off public."userRoles" keyed on auth.uid() rather than off the JWT claim, which removes the mirror and Defect 2 with it, and fixes the vocabulary in the same edit; pin {search_path = public, pg_temp}; decide SECURITY INVOKER vs DEFINER explicitly, because an INVOKER function reading userRoles under RLS is a recursion trap the moment userRoles itself gets a policy. That is Class-A DDL touching the authorization path of a live clinical app and it needs a pre-apply reviewer and a GO before I write it. Not starting it inside the venus75 window. STANDING CONSTRAINT RESTATED SO IT LANDS ON THE ROW RATHER THAN IN A MESSAGE: NO RLS ON VENUS UNTIL THIS IS FIXED.
• wi-cli-venus statusChanged · 5w ago
  Measured live: fn_isadmin literal 'admin' matches NO venus role code (administrador locked out, bedelia sole match); JWT mirror drifted 21/33 alumnos absent + 2/3 titulares wrong; 10 policies inert (RLS off on 47/47). Gate on enabling RLS.
• wi-cli-venus note · 5w ago
  venus76 APPLIED to venus prod 2026-09-11 14:05 UTC under pmmaster-evolutiva-cc GO (tag pmmaster-evolutiva-cc-mtx0zkxnr41e-db-venus-cc). fn_isadmin()/fn_istitular() no longer read auth.jwt() app_metadata.role; both now resolve off public."userRoles". FILES: db/migrations/applied/venus76-isadmin-istitular-resource-from-userroles.sql + venus76-harness.sh (both UNPUSHED, coder-venus-cc owns the push). WHAT WAS WRONG, four measured defects, not inferred: D1 ROLE VOCABULARY. The old bodies gated on role IN ('admin','bedelia'). 'admin' is NOT a venus role code - the four live values are administrador, alumno_postgrado, bedelia, titular, and the app's own vocabulary says so at src/lib/auth-actions.ts:42,44, src/lib/email.ts:322, nav-sections.ts:16, home-route.ts:8-9, paciente-actions.ts:16-17, practica-roles.ts:8, practica-access.ts:23, audit-actions.ts:11. So the admin leg matched nothing and every administrador was silently excluded. bedelia is kept because the app pairs administrador+bedelia as staff everywhere - that pairing is the measured intent behind the original IN-list. D2 DRIFTED MIRROR. The source was auth.jwt() -> app_metadata.role, a single-valued mirror of a set. Measured on prod: 23 of 37 resolvable users have a raw_app_meta_data.role that disagrees with their live userRoles. No fallback to the JWT is kept - a drifted mirror is not a degraded source, it is a wrong one. The raw_app_meta_data drift itself is NOT fixed here; that is the auth lane. D3 RESOLVE KEYING. The functions must find the users row the app would find. That is src/lib/auth.ts resolveOwnerByAuth: L1 users."authId" = auth.uid(), then L2 legacy users."id" = auth.uid(), both requiring deletedAt IS NULL AND ownerUserId IS NULL. There is no L3 - the emailCanonical leg was deleted in VENUS-305 as an account-takeover seam and must never return. First cut used a flat OR; REJECTED and replaced, because a flat OR is a UNION, not an ordered ladder: where the app tries L1 and stops, an OR sees every matching row, and the two diverge when one row's authId equals a different row's id. Shipped form is ORDER BY (u."authId" = auth.uid()) DESC NULLS LAST LIMIT 1. NULLS LAST is load-bearing: DESC alone is NULLS FIRST in PostgreSQL and an L2 row has authId IS NULL, so a bare DESC ranks L2 ABOVE L1 and inverts the ladder in exactly the case the ordering exists to decide. Prod collision count re-measured immediately pre-apply: 0 rows. The ordering is for data that has not arrived yet. D4 SEARCH_PATH. Now pinned SET search_path = public, pg_temp, matching venus's three other SECDEF functions, with every reference schema-qualified as well. SECURITY DEFINER IS REQUIRED, PROVEN NOT ASSERTED. An RLS policy qual executes as the CURRENT role, and authenticated holds no SELECT on users or "userRoles". Flipping the harness to SECURITY INVOKER turned 16 cases red with 'permission denied for table users' - an INVOKER body raises 42501 INSIDE the qual, it does not return false. Containment is the ACL, not the volatility: proacl ships EXACTLY {postgres=X/postgres,service_role=X/postgres}. D5 DELIBERATE NON-FIX. No GRANT EXECUTE ... TO authenticated. venus's Data API surface is closed and granting would publish a live /rpc/fn_isadmin endpoint today for a model nothing uses. Enabling RLS later requires, IN THE SAME CHANGE: (a) table grants, (b) the EXECUTE grant, (c) rewriting the 10 quals to the initplan form USING ((SELECT public.fn_isadmin())) - a SECDEF SQL function is not inlinable, so a bare call is evaluated per row, (d) a decision on "userRoles"."seesAllSedes", which fn_istitular deliberately does not consider. SCOPE HONESTY: this is a latent-defect repair in DORMANT scaffolding, not an RLS unblock. relrowsecurity is false on 47/47 public base tables, neither anon nor authenticated has any privilege on the five policy tables, and there are ZERO app-side callers - the app reads through a server-side pg pool. The 10 policies are doubly dormant. The reason to fix it now is pmmaster's: it passes review on its own merits today and fails on the day someone turns RLS on. HARNESS: db/migrations/applied/venus76-harness.sh, 26 PASS / 0 FAIL. Standalone PG17 cluster, never prod. DDL sed-extracted from BEGIN;..COMMIT; of the migration itself, never retyped. Fixture reproduces prod's shape including venus43's removal of PostgreSQL's default PUBLIC EXECUTE - without that REVOKE the anon-denial case measures the harness, not the migration. FALSIFICATION-VERIFIED six ways: 'administrador'->'admin' reddens E1+E9; dropping ur."deletedAt" reddens E5; dropping it in fn_istitular only reddens E5b; dropping u."ownerUserId" IS NULL reddens E7; DESC NULLS LAST -> bare DESC reddens E12+E12b; SECURITY DEFINER -> INVOKER reddens 16. ACCEPTANCE ON COMMITTED PROD STATE, four green: A1 both functions prosecdef=t, proconfig={"search_path=public, pg_temp"}, provolatile=s, owner postgres, proacl EXACTLY {postgres=X/postgres,service_role=X/postgres} (D5 holding - not granted to authenticated or anon). A2 all 28 public policies byte-identical to the pre-apply pg_policies capture, diff empty. A3 relrowsecurity still false 47/47. A4 both return FALSE, not NULL and not an error, as postgres with no JWT. db/schema.md regenerated.
• wi-cli-venus completed · 5w ago
  venus76 applied to prod, acceptance A1-A4 green, harness 26/26 falsification-verified. Files unpushed - coder-venus-cc owns the push.