RLS enablement: alumno write-path policies and caller-role proof

Sub-tickets

Activity

• audit-venus-ca created · 1w ago
  parent=#3690
• db-venus-cc note · 1w ago
  INVENTORY (done=item 1): - Single writer: src/db/pool.ts builds the ONE pg.Pool from process.env.DATABASE_URL at module-eval time. Every write-touching file (grep confirmed 13: admin-actions.ts, alumnos-actions.ts, alumnos-import-actions.ts, auth-actions.ts, comentario-actions.ts, correction-actions.ts, email-outbox.ts, evaluacion-actions.ts, logger-core.ts, practica-actions.ts, rate-limit.ts, support-actions.ts, support-core.ts, soporte-cron/route.ts) goes through this same pool - no second pool, no per-caller role switch. - DATABASE_URL role: postgres.<project-ref> (Supabase pooler form of the postgres role). Confirmed on live DB: rolname=postgres, rolbypassrls=t, rolsuper=f. - Zero PostgREST/Data API usage anywhere in src: grepped for .from("alumnos")/.from('alumnos')/.from("practicas")/etc and supabase.from( across the whole tree - NO MATCHES. The several supabase-js createClient() call sites (auth-actions.ts, persona-actions.ts, callback route, login page) are all session/getUser/getSession auth calls, never table reads/writes. There is no code path today where a write to alumnos/practicas/practicaParticipations/pacienteAntecedentes executes as anon or authenticated. DECISION (item 2): given the inventory shows no caller role other than the bypass role EVER reaches these tables for a write, and no Data API write path exists to design an authenticated-role policy against, the correct 'intended rights' answer is DOCUMENT AS SERVER-ONLY UNDER A BYPASS ROLE (the row's second done-option), not author new INSERT/UPDATE/DELETE policies for a caller that structurally does not exist. Policies written for an untested caller would be unverifiable prose, not a control - same failure class as the harness gap just fixed on VENUS-475. If Elazar's eventual RLS-enablement decision changes who writes (e.g. a future direct-client write path), this row's inventory needs re-running before trusting it, since it is a snapshot of today's app code. NOT A FIX, FLAGGED AS-IS: this does not change VENUS-401's PARKED status or do anything live - it only answers the prerequisite question pmmaster named (RLS enablement would deny alumno writes under a non-bypass caller) with 'there is no non-bypass caller today, so enabling RLS changes nothing for writes unless that also changes' - which is itself a fact Elazar's decision should see stated plainly, not a recommendation to enable.
• db-venus-cc note · 1w ago
  FORCE-RED PROOF (item 3, harness, both arms same session): - Non-bypass role (NOLOGIN NOBYPASSRLS, granted INSERT+SELECT on alumnos, no RLS policy covers INSERT): INSERT denied with 'ERROR: new row violates row-level security policy for table alumnos' - the RLS-specific denial, isolated from table-ACL denial by granting INSERT first (an earlier un-granted attempt returned 'permission denied for table alumnos' instead, a different error, correctly discarded as not isolating RLS). - Bypass role (venustest, rolbypassrls=t, the app's postgres-role analog) with the SAME RLS enabled on alumnos: INSERT reached CHECK-constraint validation (ERROR: violates check constraint alumnos_role_check) rather than any RLS-policy error - proof the row passed RLS and was stopped only by an unrelated domain constraint, i.e. RLS enablement does not block this role's writes. (Did not bother completing a fully-valid fixture row since the ERROR TYPE itself is the proof point, not a successful INSERT.) Teardown confirmed clean after each run. CLOSING VENUS-474 on this basis: inventory (zero non-bypass write callers exist in app code today) + decision (document as server-only/bypass, no new policies - none would be exercised by anything live) + proof (RLS enablement would not disturb the one real writer, and would correctly deny a hypothetical non-bypass writer) all satisfied. This is the fact for Elazar's VENUS-401 decision: RLS changes nothing for writes under the current architecture.
• db-venus-cc completed · 1w ago
  completed