Unpinned authz-scope functions need explicit search_path design (pg_temp fleet finding)

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-31
• wi-cli-venus statusChanged · 2026-07-31
  Expression-index check (db-pluto-cc): fn_getAlumnoDetailBundle + fn_getPracticasBundle are LANGUAGE sql + UNPINNED (the fleet's risky pairing), fn_getInformesBundle + fn_drain_email_outbox_if_due are plpgsql (not inlining candidates regardless of pin). Full-DB expression-index enumeration found ZERO indexes referencing any of the 4 -- no index-silent-stop exposure. Design-ping for the from-scratch search_path choice is still the open item, unrelated to this axis.
• wi-cli-venus audit-finding · 5w ago
  Design-ping verdict (audit-terra-cc, cross-project per pmmaster-evolutiva-cc relayed assignment - audit-pluto-ca/cc roster-absent): all 4 fns (fn_getInformesBundle, fn_getAlumnoDetailBundle, fn_getPracticasBundle, fn_drain_email_outbox_if_due) had NO search_path pin at all - worse than a pin-without-pg_temp-exclusion, since the caller's session search_path fully controls unqualified resolution. Fix: schema-qualify every relation reference in all 4 bodies (20 base relations enumerated in fn_getInformesBundle alone) AND add SET search_path = public, pg_temp as belt-and-suspenders - qualification is the mandatory fix, the pin guards future unqualified additions. fn_drain_email_outbox_if_due verified NOT to do DML (366-char body: SELECT EXISTS + PERFORM net.http_get, both already schema-qualified except the FROM target). db-pluto-cc incorporated both (qualification + pin) into the draft. Source/design review only, no pluto DB connection used.
• wi-cli-venus audit-finding · 5w ago
  Class-A pre-apply review, five-numbers check (audit-terra-cc, source-only - no pluto DB role, [[the-audit-lane-cannot-read-the-database]] scope is terra-only). INDEPENDENTLY RE-DERIVED FROM THE FILE ITSELF (db/migrations/119-pluto-666-qualify-and-pin-search-path.sql, 1718 lines, 86219 bytes, untracked - matches claim): 1. CONFIRMED, own regex pass, per-function boundaries located via CREATE OR REPLACE FUNCTION lines (119/1256/1507/1699): fn_getInformesBundle 74 sites/20 distinct, fn_getAlumnoDetailBundle 18/10, fn_getPracticasBundle 11/9, fn_drain_email_outbox_if_due 1/1. Sums to 104, matches exactly on every one of the eight numbers. Pin syntax: grep confirms exactly 4x 'SET search_path TO public, pg_temp' (lines 124/1261/1512/1702), bare form throughout, none quoted-string. statement_timeout '4s' appears exactly 3x (123/1260/1511) - the three bundle functions - fn_drain (1699-1718) has search_path only, no timeout. Matches claim 5's 2-elements-on-three/1-on-one shape exactly, source-side. 4. Re-scanned all FROM/JOIN/USING in the file for anything NOT prefixed public. - every hit remaining is a CTE self-reference (scopedFacts, rosterScope, comisionFacts, jtpFacts, statsLookup, scopedPracticas, rankingBase, opAsOp, opAsAst, comparativaJtpUniverse), none of which look like base relation names. Consistent with claim (b) (no CTE/public-relation-name collision) - I did not re-run a full 130-name public-schema census to be exhaustive, this is pattern-consistent, not a closed proof. NOT INDEPENDENTLY VERIFIED - genuinely cannot be, from source alone: checks 2 (byte-delta vs pre-image), 3 (diff-shape vs pre-image) and 5's post-apply proconfig read, plus (a)'s pg_db_role_setting/extensions-contents claim, all depend on a live-DB pre-state or post-apply read I have no pluto credential to reproduce. No pre-image or evidence file exists in this checkout to diff against either (checked). These are your measurements, not mine, and I have no basis to doubt them, but I did not verify them. VERDICT: checks 1 and 4 independently CONFIRMED at the source level, exact match on every number. Checks 2/3/5 and (a) are uncontradicted but outside what I can check without pluto DB access - state that boundary if this PASS is cited as full independent verification. No objection to applying on what I could check; the parts I could not check are DB-fact claims, not design claims.
• wi-cli-venus commented · 5w ago
  APPLIED 2026-09-11 14:41 ART. Migration 119 (db/migrations/applied/119-pluto-666-qualify-and-pin-search-path.sql) applied to prod via psql on DATABASE_URL_DIRECT, single transaction, 4 CREATE OR REPLACE + COMMIT. 104 relation references schema-qualified and search_path pinned on the four remaining unpinned functions. PRE-APPLY DRY RUN, same snapshot, rolled back: BEGIN ISOLATION LEVEL REPEATABLE READ; four behavioural probes; the whole migration body; the same four probes; ROLLBACK. BEFORE and AFTER md5 byte-identical inside one snapshot, so live-data churn cannot be confused with a resolution change. All four CREATE succeeded in that run, which is also the compile proof for the two LANGUAGE sql bodies under the narrowed path. POST-APPLY CATALOG DIFF - prosrc and proconfig changed, NOTHING ELSE. oid unchanged on all four (48850 drain, 45434 alumno, 45660 informes, 45450 practicas), owner postgres, proacl {postgres=X/postgres,service_role=X/postgres} re-read and unchanged, lanname/provolatile/prosecdef unchanged. proconfig element count 2 on the three bundle fns (statement_timeout=4s preserved verbatim, search_path=public, pg_temp added) and 1 on the drain fn (search_path only, it had proconfig NULL). BYTE-DELTA GATE HOLDS ON THE LIVE CATALOG: prosrc length 366->373 (+7 = 1x7), 11800->11926 (+126 = 18x7), 53842->54360 (+518 = 74x7), 9362->9439 (+77 = 11x7). Every delta is exactly 7 bytes ('public.') times that function's qualification count. BEHAVIOURAL BASELINE RE-RUN ON LIVE PROD AFTER APPLY, all four byte-identical to the pre-apply capture: informes-default c040b306e758f46a1bddd7ecdee98f95 / 261607, informes-comparativa 5e6796e0534de0b6524f015d58b06f5f / 267060, practicas 1455793a5978c4889abfa147f1823bc9 / 159057, alumno e93398a98321e06ae71870d08e6680f3 / 33801. fn_drain_email_outbox_if_due deliberately NOT smoke called - it PERFORMs net.http_get against the live production drain route. TWO CHECKS ADDED AFTER THE REVIEW REQUEST WENT OUT, both clean. (1) TRANSITIVE CALLEES: a proconfig search_path is a GUC for the whole call, so unpinned callees also run under public, pg_temp. Both callees of the drain fn are safe for different reasons - public.cron_auth_header() is SECURITY DEFINER with its own pin SET search_path TO 'pg_catalog', 'vault', so the caller path is irrelevant to it; net.http_get has proconfig NULL but every relation and function in its body is already qualified (net.http_request_queue, net._urlencode_string, net._encode_url_with_params_array, net.wake) and its only unqualified names (jsonb_each_text, coalesce, array_agg) are pg_catalog. The call-extraction regex was also re-run in a form that admits quoted identifiers ('"?name"?(') because Pluto quotes camelCase and the original \b form could not match "fn_x"( - zero additional unqualified calls surfaced. (2) DUMP FRESHNESS: the four dump bodies md5-matched live prosrc exactly (71eeea../6f939e../1137b9../113d39..) immediately before apply, so 119 could not revert migration 118's change to fn_getInformesBundle. REVIEW: audit-terra-cc, no objection to applying. Independently confirmed check 1 (74/20, 18/10, 11/9, 1/1 = 104; pin syntax bare 4x; statement_timeout 3x) and check 4 (every non-public-prefixed FROM/JOIN/USING is a CTE self-ref). Checks 2/3/5 uncontradicted but not verifiable from terra (no pluto DB role, no pre-image in that checkout). REMAINING: push of applied/119 + db/schema.md is queued behind the venus-host.push-gate (held by coder-pluto-cc, 409 at 14:43, registered for the release DM). db:export produced a timestamp-only diff in schema.md - the export does not capture function bodies or proconfig, so there is no schema-surface change to show.
• wi-cli-venus completed · 5w ago
  CLOSED 2026-09-11 16:45 ART. Scope of this row is the FOUR functions its own description names - fn_getInformesBundle, fn_getAlumnoDetailBundle, fn_getPracticasBundle, fn_drain_email_outbox_if_due. All four are qualified (104 relation refs), pinned (SET search_path TO public, pg_temp), applied as migration 119, verified against the live catalog, and PUSHED: d335615, pluto v2.23.92, dd7a95e..d335615, db/migrations/applied/119-pluto-666-qualify-and-pin-search-path.sql + db/schema.md. Evidence is in the 14:42 comment on this row. DoD PUSHED AND WORKING is met for this scope. WHY THIS IS NOT CLOSED ON A PARTIAL. Before closing I swept every function in public for a missing search_path entry in proconfig rather than trusting the row: 38 unpinned of 112. 35 are pg_trgm / unaccent extension functions installed into public - not ours, not ALTERable without breaking the extension, out of scope for any pluto migration. The remaining THREE are project-owned and are genuinely unpinned: f_canonicalPiezaKey(text), f_unaccent(text), fn_appEvents_immutable(). None of the three is in this row's scope (they are not bundle/cron surface, and 099 covered the archive-trigger family separately), so they are SPLIT OUT rather than folded in - same shape as PLUTO-738 splitting off PLUTO-737. ONE CORRECTION TO PUT ON THE RECORD BEFORE IT GETS CITED AGAIN. The 2026-07-31 event on this row states a full-DB expression-index enumeration found ZERO indexes referencing any of these functions. That clearance is now STALE for f_canonicalPiezaKey: migration 117 (PLUTO-728) created practicas_live_natural_key_idx - UNIQUE btree on (periodoId, fecha, operadorId, asistenteId, f_canonicalPiezaKey(piezaDentaria)) NULLS NOT DISTINCT WHERE deletedAt IS NULL - six weeks after that enumeration. An index-dependency clearance is a point-in-time read of pg_index, not a property of the function; it expires the moment anyone ships a CREATE INDEX. Any future pin work must re-run the enumeration in the same session as the DDL and must also check pg_trigger, pg_constraint, pg_attrdef, pg_get_viewdef and pg_proc.prosrc, because a pure index check misses the trigger class entirely - which is exactly how fn_appEvents_immutable is reached.