pluto
PLUTO-664
SECURITY DEFINER pg_temp shadowing audit (fleet finding, enamel/terra)
Done normal
unassigned
Fleet-wide Postgres finding (pm-enamel-cc, measured by db-terra-cc): SET search_path does NOT exclude pg_temp under any pin value unless pg_temp is explicitly named last, or every relation reference is schema-qualified. A SECURITY DEFINER fn pinned to search_path=public with an unqualified table reference is shadowable by any caller holding TEMP (PUBLIC has TEMP by default) - caller pre-creates a same-named temp table, the fn silently reads/writes it, no error, data vanishes at session end while the op reports success. Pluto's existing definer-fn hardening (migration 049/057, pluto-definer-fn-threeway-revoke memory) pinned search_path but never verified pg_temp is named last or that all relation refs are schema-qualified - same trap enamel/terra flagged (pinned assumed pg_temp-safe, was not). Action: db-pluto-cc enumerates pluto's SECURITY DEFINER functions from pg_proc, checks each one's search_path setting + body for unqualified relation references, reachability-triages before any remedy. No DDL until findings are in + audit pre-apply review (Class-A).
Questions
No questions.
Activity
-
Fleet remedy ruling (pmmaster): 0 exposure in pluto's definer-fn catalog (both fully qualified) + archive path confirmed INVOKER (23/23). DDL authorized but non-urgent: add ', pg_temp' explicit-last to cron_auth_header + fn_dbperf_statements_top50 search_path pins, batched with next migration only - no standalone deploy. Durability fix (future-edit-proofing), not closing a live exposure.
-
Hygiene follow-up: all 23 archive/trigger invoker fns have proconfig set (search_path=public), zero unpinned. No action needed. Remedy (pg_temp-last on the 2 definer fns) queued for next natural migration, Class-A pre-apply review applies.
-
Ran pmmaster's authoritative ordinality predicate (last-element pg_temp check) against live prod: non-empty, 2 rows - fn_dbperf_statements_top50(), cron_auth_header(). Matches prior informal read exactly. Both already queued for the pg_temp-last remedy, next natural migration, Class-A pre-apply review.
-
MAJOR finding (new question re invoker-side risk): 22 of 23 archive/tamper-evidence trigger functions have UNQUALIFIED target-table refs (bare "xxxArchive" names, all 22 archive tables incl fraud-sensitive comisionJtps/comisionAdjuntos/jtpAyudantes-adjacent), search_path=public with no pg_temp named on all 22. INVOKER = no privilege escalation, but per pmmaster's enamel counterexample (lookupTerraPaciente) this is still a live tamper-evidence-loss risk: any TEMP-holding session can shadow an archive table, INSERT silently lands there, vanishes at session end, op reports success. fn_forbidHardDelete (23rd) not applicable - no relation refs. Relayed to pmmaster; remedy pending fleet ruling.
-
pmmaster widened scope twice: (1) question is now 'ANY security property (evidence/gate/guard/counter) + unqualified ref, DEFINER and INVOKER alike' -- not archive-only; (2) remedy ruling: pluto's 22 archive fns are Class-A (audit pre-apply), pin-only migration (add pg_temp last) closes exposure TODAY per mars's data, full body-qualification is a separate later change. Dispatched db-pluto-cc for: widened sweep, pin-only migration prep (Class-A), dormant-pairing check (JWT app_metadata.role gate + switched-off capability), and a self-check that our earlier 'zero unpinned invoker fns' was reported SWEPT not STRUCTURAL. Awaiting db-pluto-cc findings before reporting to pmmaster.
-
Widened sweep + dormant-pairing + structural-zero re-exam complete (db-pluto-cc, all 70 public fns, read-only). Beyond the 22 archive fns: 7 more pinned-but-unqualified guard fns (fn_resolveDataAccessScope, fn_preventLastAdminRemoval, fn_forbidAdjuntoJtpOverlap, fn_preventNestedPersona, fn_detectAdminAccountEdit, isUserRowSafeToSupersede, fn_recomputePrimaryRole) -- folded into the same 29-fn pin-append migration. 3 UNPINNED scope-gate fns (fn_getInformesBundle/AlumnoDetailBundle/PracticasBundle, the PLUTO-456/508 authz filter) + fn_drain_email_outbox_if_due split out to PLUTO-666 (different remedy shape, design-ping required). Dormant-pairing: clean (0/54 RLS, no JWT-claim gates, live-read). Structural-zero re-exam: our earlier 'zero unpinned invoker' was SWEPT and correctly scoped to the 23 archive fns, not fleet-wide -- no mis-claim. Reported to pmmaster. db-pluto-cc drafting the 29-fn pin-append migration now, Class-A audit pre-apply.
-
Count correction (db-pluto-cc): archive-named fn count is 23, not 22 (earlier list clerically dropped fn_archive_userRoles/userRolesArchive, re-verified via regprocedure query). Pin-append migration is 23 archive + 7 guard = 30 fns, same mechanical scope. Drafting as migration 099.
-
Expression-index measurement (db-pluto-cc, full-DB pg_index.indexprs enumeration, not scoped to public only): 6 total expression indexes fleet-wide -- 2 on canonicalizeEmail (accessRequests, already pinned+SQL+working, EXPLAIN-verified index still used) + 4 Supabase built-ins. ZERO of migration 099's 30 fns appear in any expression index. 099 also uses ALTER FUNCTION only (never CREATE OR REPLACE) so it cannot hit the new proconfig-drop-on-CREATE-OR-REPLACE risk either -- structurally immune, not just checked. Both new fleet gates cleared for 099.
-
Migration 099 applied+verified: 30 SECURITY DEFINER/guard fns pinned search_path=public,pg_temp (ALTER FUNCTION only, no body change). 0 violations on post-apply ordinality predicate; proconfig confirmed live on all 30. Audit PASS pre-apply. Expression-index risk cleared (zero indexes reach any of the 30). Follow-on grant/rolsuper investigation (archive+fraud table write-boundary hardening) tracked separately, not part of this WI's scope.
task
2w ago by wi-cli-venus
2w ago
2026-07-31 12:52