Harden append-only surfaces: revoke/guard UPDATE-DELETE-TRUNCATE on appEvents + practicasArchive + practicaParticipationsArchive

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-31
• wi-cli-venus note · 2026-07-31
  pmmaster ruling ms8xltwo0bqo: no lane drafts a REVOKE on any audit/archive surface until it has ENUMERATED THE LEGITIMATE WRITERS of that surface, retention/prune jobs first. Remedy must be a ROLE DESIGN, not a REVOKE list (SECURITY DEFINER pruner or dedicated role for the appEvents prune crons). Also in scope: evolutiva_agent_rw DELETE/UPDATE over-grant on appEvents. Two mechanism facts: row-level trigger never stops TRUNCATE; app.bypass_immutable settable by any session. anon/authenticated zero grants = external surface tight; exposure is internal-role shaped.
• wi-cli-venus note · 2026-07-31
  pmmaster ms8xnmbikwar: VENUS-347 WAITS for terra's audit-surface pruner design (SECURITY DEFINER pruner or dedicated role — fleet reference shape) rather than inventing its own cron path; terra scopes it (no live retention job = only lane that can design without outage risk), design reviewed by mars + venus (both hold live crons a bare REVOKE would break). Venus is a REVIEWER when the design lands.
• wi-cli-venus blocked · 2026-07-31
  status=blocked
• wi-cli-venus note · 2026-07-31
  pmmaster ms8xqou4umjp: REVOKE prerequisite = legitimate-writers enumeration (dispatched to db-venus-cc, ms8xr2vq0rtv). Design doc re-scoped fleet-side: terra keeps pruner-shape, enamel drafts per-tenant-role design, ONE doc, mars+venus review; bumped at mars's ask (venus declined bump, reasoning stands as general caution). GUC hardening sequenced INSIDE role design (on shared-role DBs it cannot succeed at all; venus has own service_role so viable, but sequenced). appKey attribution rule: derive from session_user, never validate self-declared.
• wi-cli-venus note · 2026-07-31
  REVOKE-prerequisite writer enumeration (pmmaster ms8xqou4umjp, db-venus-cc ms8xsshc3eno): App direct-pg DSN connects as POSTGRES (proven: archivedBySessionUser='postgres' on all 2759 attributed archive rows), not service_role — REVOKE design must target postgres-as-app-DSN. appEvents INSERT: (1) app runtime via logger-core.ts insertEvent/insertFeedbackEvent (role postgres, event-driven); (2) applog-listen heartbeat via least-priv applog_writer DSN (INSERT-only, daemon); (3) repair-access-grant.ts CLI (human-driven, app DSN). appEvents UPDATE/DELETE: prune crons jobids 7/8/12/13 (daily 04:05-04:35, username postgres, DELETE via app.bypass_immutable GUC + fail-closed security-set guard); historical one-off migrations (venus4b/4d). Archives (practicasArchive/practicaParticipationsArchive) INSERT: solely the 2 INVOKER archive triggers (run as connecting role=postgres); UPDATE/DELETE: ZERO paths — no retention defined, archives grow unbounded (design question for terra+enamel joint doc). Fleet ruling noted: venus GUC hardening viable (own project) but sequences INSIDE the role design, not parallel. Remedy stays blocked on the joint design doc. [db-venus-cc]
• wi-cli-venus note · 2026-07-31
  pmmaster ms8xu5mhwa0o: VENUS-347 SPLITS. Archive pair (practicasArchive/practicaParticipationsArchive) has ZERO legitimate UPDATE/DELETE paths — no deleter to break, REVOKE-able NOW without terra's design. appEvents half stays blocked on the joint doc (4 live prune crons). Also fleet-ratified from venus's enumeration: runtime role per path measured — pg-pool = postgres (2759-row evidence), PostgREST = service_role; every REVOKE design targets the measured USER of the grant, not the ACL holder. applog_writer INSERT-only DSN cited as the fleet's one genuine grade-3 separation.
• wi-cli-venus statusChanged · 2026-07-31
  status=inProgress
• wi-cli-venus note · 2026-07-31
  Archive-pair half dispatched to db-venus-cc (ms8xuszeaffh): own Class-A migration after the pin window, audit pre-apply, surrogate-carrier guard evidence (non-vacuous, 2759 rows). Flagged design question: postgres is likely table owner — owner retains implicit rights, so a bare REVOKE may not bind; trigger-guard or ownership change may be the real shape. appEvents half remains blocked on terra+enamel joint doc.
• wi-cli-venus note · 2026-07-31
  pmmaster ms8xxi7bkeuu: FOURTH GRADE — a grant only binds a non-superuser role; mars's app connects as superuser postgres, so REVOKEs there are inert vs the live path. Venus probe dispatched (ms8xxuaquwyr): rolsuper/rolbypassrls read on postgres + service_role BEFORE drafting the archive REVOKE; if venus postgres is superuser, draft re-scopes to hygiene-of-unused-roles and real remedy = non-superuser app role (enamel's per-tenant role design direction).
• wi-cli-venus progress · 2026-07-31
  Probes + draft (db-venus-cc-ms8y4wtrp721): venus disarm-inference FALSIFIED by live exercise — SET LOCAL session_replication_role=replica as postgres SUCCEEDED (rolled-back txn; mechanism = supabase_privileged_role membership, same as enamel). Membership graph: postgres member of 18 roles (all WITH ADMIN OPTION except supabase_privileged_role + supabase_realtime_admin) → acceptance = full-relacl EQUALS CONTRACT, never per-entry. Archive-pair draft at /srv/share/incoming/venus/2026-07-31-draft-archive-pair-hardening.sql: advisory-depth tamper-EVIDENT framing, Phase 1 REVOKE (postgres UPDATE/DELETE/TRUNCATE; service_role +REFERENCES/TRIGGER), Phase 2 four guard triggers ENABLE ALWAYS + TRUNCATE bit, no bypass GUC; per-phase config-only rollback; evidence E1-E4 incl. replica-mode retry + unknown-grantee drift (pm_triage_reader). Sent to audit-venus-ca pre-apply. Fleet rulings absorbed (pmmaster ms8y4essfx6e/ms8y5icair3t): 3-read probe (rolsuper+relowner+pg_auth_members) ruled; venus NEUTRAL survives pg_get_expr retirement (held plan pairs); pg_depend replaces pg_get_expr as emptiness instrument fleet-wide.
• wi-cli-venus progress · 2026-07-31
  Archive-pair hardening APPLIED + VERIFIED (db-venus-cc ms8ych227mu0; audit PASS v2 audit-venus-ca-ms8y88uc37yu). Committed relacl both tables EQUALS CONTRACT {postgres=arxtm, service_role=ar, agent_ro=r, agent_rw=ar}; 4 guard triggers tgenabled='A'; TRUNCATE-bit 2; guard fn pinned {public,pg_temp}. Evidence: control arm non-vacuous; E1 UPDATE/DELETE/TRUNCATE stopped; E2 replica-mode stopped; E3 unknown-grantee drift failed acceptance; E4 INSERT intact. Coverage addition: trigger arm proven separately with privileges re-granted (owner-regrant threat) — stops came from the guard's own exception, not just the grant layer. Archive half DONE at advisory depth; appEvents half stays blocked on terra pruner + enamel per-tenant-role joint doc. schema.md regenerated, lands via coder push.
• wi-cli-venus note · 2026-07-31
  Fleet rule (pmmaster ms8yfnl0io8g): demotion to hygiene does not demote the mechanism claim under it — 'this works' stays in the audited lane at every price point; hygiene framing protects SCOPE, never MECHANISM. Venus archive-pair compliance check: every mechanism claim in the applied migration IS measured, not priced — grant stops (E1), replica-mode stop (E2), trigger arm proven under deliberate owner re-grant (guard's own exception, not the grant layer), drift-detection arm (E3), INSERT-intact (E4), all against live committed state. No unmeasured 'this works' sentence identified in the venus disposition; the advisory-depth label bounds SCOPE only.
• wi-cli-venus progress · 2026-07-31
  Repo record landed: coder-venus-cc 490b69e v1.12.33 (migrations venus62-66 placed by db-venus + regenerated db/schema.md; strict --only; live-verify /api/health 1.12.33 dbOk true). Archive half fully done: applied + verified + repo-recorded. WI stays inProgress on the appEvents half (terra pruner + enamel per-tenant-role joint doc).
• wi-cli-venus note · 2026-07-31
  Per-lane 8.4 exposure statement (pmmaster ms8yu6kpgrb7 discriminator): VENUS IS EXPOSED to the cross-lane trailer defect — db-venus-cc has no push authority, so db-authored artifacts land via coder push and get trailered Agent: coder-venus-cc (live example: migrations venus62-66 authored by db-venus, pushed as 490b69e). Honest under the queued 8.4 narrowing (pushing-session semantics); supply this statement to the commons pass (maintainer coder-mars-cc) when the narrowing lands.
• wi-cli-venus assigned · 5w ago
  db-venus-cc
• wi-cli-venus progress · 5w ago
  appEvents half: venus75 DRAFTED + pre-apply harness GREEN 17/17, NOT yet applied (Class-A gate open). Archive half already shipped venus66/490b69e (2026-07-31) and re-verified live today: service_role=ar only on both, 4 guard triggers tgenabled='A', TRUNCATE bit set - no work owed there. LIVE RE-MEASUREMENT 2026-09-11 (pmmaster verify-first rule), appEvents only: D1 service_role relacl = arwdDxtm (full DML + TRUNCATE on the audit table). D2 evolutiva_agent_rw = arwd. D3 NO TRUNCATE TRIGGER EXISTS - applog_notify_error_trg tgtype 5, trg_appevents_immutable tgtype 27, bit 32 clear on both, while service_role held TRUNCATE. TRUNCATE "appEvents" erased 7864 audit rows with nothing firing. The archives have had this guard since venus66; appEvents did not. D4 the GUC is not a gate, PROVEN not assumed: BEGIN; SET LOCAL ROLE service_role; SET LOCAL app.bypass_immutable='true'; SELECT current_user, current_setting(...) -> service_role|true; ROLLBACK. app.bypass_immutable is a PLACEHOLDER GUC, therefore USERSET by construction; PG15 GRANT SET ON PARAMETER only covers SUSET params, so the GUC cannot be made privileged by any server mechanism. The connecting ROLE is the only gate available. D5 fn_appevents_immutable pinned SET search_path TO 'public' - pg_temp MISSING, unlike its fn_archives_immutable twin. D6 NEW, found by the harness not by reading: the bypass covers UPDATE too. Live body is IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; so with the GUC set an UPDATE REWRITES an audit row and returns NEW. Control C1d reproduced it (postgres+GUC+UPDATE set level='TAMPERED', succeeded). Rewriting an audit row is strictly worse than deleting one - the row still looks present and authentic. No legitimate appEvents UPDATE exists anywhere: app.bypass_immutable appears in exactly two migrations (venus4d3 retention, venus44 test cleanup), both DELETE. REMEDY (db/migrations/pending/venus75-appevents-immutable-hardening.sql), built around the WI design constraint: cron.job jobid 7/8/12/13 all have username=postgres (read from cron.job today, not assumed) and legitimately DELETE via the GUC behind venus4d3's fail-closed security-set guard. postgres also OWNS appEvents (relowner, measured) so the REVOKEs bind - a non-owner REVOKE only warns and ships green (the ENAMEL-35 trap). No role is superuser (rolsuper=f on postgres/service_role/evolutiva_agent_rw/applog_writer, measured today), so grants are load-bearing here unlike mars. P1 REVOKE UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,MAINTAIN from service_role; UPDATE,DELETE from evolutiva_agent_rw; UPDATE,TRUNCATE from postgres. postgres KEEPS DELETE - that is the prune path. Contract (acceptance = relacl EQUALS this, never 'expected names removed', because postgres inherits every member role's grants): postgres=ardxtm, service_role=ar, evolutiva_agent_rw=ar, evolutiva_agent_ro=r, applog_reader=r, applog_writer=a, no anon/authenticated/PUBLIC entry. P2 re-emit fn_appevents_immutable, three changes and no others: pg_temp added to the pin (D5), bypass gated on current_user='postgres' (D4), bypass narrowed to TG_OP='DELETE' (D6). P3 NEW fn_appevents_truncate_immutable + trg_appevents_immutable_truncate BEFORE TRUNCATE FOR EACH STATEMENT, unconditional (prune DELETEs, never TRUNCATEs - a bypass there would be a door with no authorized user). SEPARATE FUNCTION deliberately: fn_appevents_immutable RAISEs with OLD.id and OLD is UNASSIGNED in a statement trigger, so reusing it would abort with 'record old is not assigned yet' - still blocking, but reporting the wrong cause forever. Both guards then ENABLE ALWAYS (tgenabled='A'): postgres can SET session_replication_role=replica via supabase_privileged_role membership (venus66 live exercise) and disarm every 'O' trigger in one statement. FRAMING, and it must not be softened on the record: this NARROWS the bypass from every role in the cluster to postgres only. It does NOT close it. The pg-pool app path connects AS postgres (measured, this WI 2026-07-31, 2759-row evidence), so the app plane keeps the ability to set the GUC. Full closure = a non-superuser app role (terra+enamel per-tenant-role design), not this migration. Same honesty grade as venus66's advisory-depth framing. EVIDENCE: db/migrations/pending/venus75-harness.sh - standalone PG17.6 cluster, roles created locally, the DDL EXTRACTED FROM THE MIGRATION FILE by sed (never retyped: the string that ships is the string that executed - venus74 standard). 17/17 PASS. Four C1 CONTROLS run BEFORE the migration and must succeed or the harness is vacuous: service_role+GUC+DELETE open, service_role TRUNCATE open, postgres+GUC+DELETE open, postgres+GUC+UPDATE tampers a row. After: E1a service_role stopped at the grant layer; E1b OWNER-REGRANT THREAT - re-GRANT DELETE to service_role inside the txn, guard ALONE still stops it (so the stop is not a permission artifact); E2 PRUNE PATH postgres+GUC+DELETE STILL WORKS (if this fails four crons die silently every night); E3 postgres without the GUC blocked (gate is AND not OR); E3b postgres+GUC+UPDATE blocked; E4 postgres TRUNCATE blocked BY THE STATEMENT TRIGGER asserted on the TRUNCATE message specifically - that assertion is what proves it did not abort on an unassigned OLD; E5a/b both guards still fire under session_replication_role=replica; E6a/b/c INSERT intact as service_role, postgres and applog_writer (this table takes ~every app event); E7 injected drift - GRANT UPDATE to pm_triage_reader, an unknown grantee, and the relacl-EQUALS-CONTRACT comparison FAILS, then passes after the revoke. Post-apply catalog reads confirmed locally: tgtype 34 + truncate_bit t + tgenabled 'A' on the new trigger, proconfig {"search_path=public, pg_temp"} and prosecdef f on BOTH functions, relacl EQUALS CONTRACT. NOT TOUCHED, observation not a change: applog_notify_error_trg stays tgenabled='O', so replica mode silences error notifications while these guards keep firing. It is the notify rail, not a guard; re-arming someone else's rail is outside this WI. GATE: audit-venus-ca is NOT on the hub roster (empty row today), so per Elazar 09:27 relay the coder agent is PM - GO + push requested from coder-venus-cc. db-venus-cc holds no push authority. schema.md regen owed post-apply.
• wi-cli-venus progress · 5w ago
  venus75 APPLIED TO PROD 2026-09-11 13:24 UTC. Class-A GO: coder-venus-cc-mtwz781liis2, all four assertions verified against the FILE rather than my summary (audit-venus-ca is off the hub roster; Elazar 09:27 relay makes the coder agent PM). Pre-check 0 appEvents writes in the prior 10 min, lock_timeout 5s, single transaction, 3 REVOKEs + 2 CREATE FUNCTION + 1 CREATE TRIGGER + 1 ALTER TABLE, COMMIT clean. POST-APPLY VERIFIED ON COMMITTED PROD STATE, not inferred from the apply succeeding: - relacl EQUALS CONTRACT exactly, all six entries and no seventh: postgres=ardxtm, service_role=ar, evolutiva_agent_rw=ar, evolutiva_agent_ro=r, applog_reader=r, applog_writer=a. service_role went arwdDxtm -> ar; evolutiva_agent_rw arwd -> ar; postgres kept d (the prune path) and lost w and D. - trg_appevents_immutable tgenabled='A'. trg_appevents_immutable_truncate tgtype=34 with the TRUNCATE bit set, tgenabled='A'. D3 is closed: appEvents now has the statement-level guard the archives have had since venus66. - Both functions proconfig {"search_path=public, pg_temp"}, prosecdef FALSE. D5 closed. - applog_notify_error_trg deliberately untouched at tgenabled='O' (recorded above as an observation, not a change). REVIEWER'S REQUIRED POST-APPLY READ, run and clean: SECURITY DEFINER functions owned by postgres are fn_applogHeartbeatWatchdogGuard, fn_dbperf_statements_top50, fn_drainEmailOutboxGuard. NONE of the three references appEvents. The concern was correct and worth checking - a SECDEF function owned by postgres that touched appEvents would be a current_user='postgres' elevation path letting service_role reach the bypass branch through it, and 'prosecdef FALSE on the two guards' is the wrong population to check for that. There is no such function today. REVIEWER FINDINGS, accepted, folded as comment-only edits by coder-venus-cc at push time (I did not re-edit or re-run the harness, per their instruction): A. REVOKE UPDATE,TRUNCATE FROM postgres is decorative - postgres owns the table and can re-GRANT at will, so the triggers are the only thing binding postgres. Correct, and the ACL contract line reads as a gate when it is only tamper-evidence. B. The evidence plan's E1 text says to assert the guard message because a REVOKE would mask a broken gate, but harness E1a asserts 'permission denied' - the masked case. E1b (re-GRANT inside the txn, guard alone still stops it) is what actually covers it, so the harness is stronger than its own plan; the text needs reconciling, not the test. C. The DELETE-only bypass blocks future backfills - venus4b:82,104 and venus4d:101 UPDATE appEvents. Intended, and it should be named in the file so the next author gets a message instead of a mystery. db/schema.md regenerated via db/export-db.py (47 tables, 74 views), 25 insertions 24 deletions. Files moved db/migrations/pending -> applied: venus75-appevents-immutable-hardening.sql + venus75-harness.sh. pending/ is empty. Sent to coder-venus-cc for push - db-venus-cc holds no push authority. WI STATE: the appEvents half is now DONE db-side, matching the archive half (venus66, 490b69e). What this row does NOT close, stated so nobody reads it wider: the bypass is NARROWED to postgres, not closed, because pg-pool connects as postgres. Full closure needs a non-superuser app role - the terra+enamel per-tenant-role design - which is not this migration and not this row.
• wi-cli-venus completed · 5w ago
  venus75 applied to prod 13:24Z (GO coder-venus-cc-mtwz781liis2). appEvents: relacl EQUALS CONTRACT, TRUNCATE guard added (tgtype 34, ENABLE ALWAYS), bypass gated on current_user='postgres' AND TG_OP='DELETE', pg_temp pinned. Archive half was already venus66. Bypass NARROWED not closed (pg-pool is postgres) - full closure = non-superuser app role, separate work.