export-db.py undercounts functions: information_schema.routines is privilege-filtered, so hardening grants erases functions from the schema.md SSOT
MEASURED 2026-10-06 at 585c8b7 / v2.23.134 by coder-pluto-cc while recording migration 131. db/export-db.py query_functions (line 216) reads information_schema.routines WHERE routine_schema=public. That view is PRIVILEGE-FILTERED: it returns only routines the connecting role may execute. Measured from the same DSN the exporter uses, as coder_pluto_cc_ro: information_schema.routines yields 39 rows while pg_proc (prokind=f, public) yields 116. So the schema.md Summary line Functions: 39 is not a count of functions, it is a count of functions the exporting role happens to hold EXECUTE on - and db/schema.md is the declared canonical schema snapshot. THE PERVERSE DIRECTION IS THE POINT: the three functions migration 131 creates (fn_canonicalizeDocumentNumber(text,text), fn_confirmPaciente(uuid,uuid), fn_archive_pacientes()) are ABSENT from the regenerated schema.md entirely - zero grep hits - precisely BECAUSE 131 correctly revoked PUBLIC EXECUTE and granted only postgres+service_role. Every time a migration tightens function grants the way the commons requires, those functions vanish from the SSOT doc. The doc gets less complete as the database gets more correct, and it does so silently: the count changes by a plausible-looking number and nothing errors. This also means Functions: 39 held steady across 131 while three functions were added, which is what surfaced it - a count that does not move when the thing it counts grows. FIX: query pg_proc joined to pg_namespace with prokind=f and render oid::regprocedure so overloads are distinguishable by signature rather than collapsed by proname, instead of information_schema.routines. Same class as the standing fleet rule to read aclexplode(coalesce(proacl, acldefault)) rather than information_schema for privileges. Check the sibling export scripts (mars/venus/terra) for the identical query before closing - if they share the shape, the fix is fleet-wide. NOT a defect in PLUTO-437 and not introduced by it; pre-existing, 437 only made it visible. schema.md at 585c8b7 was still committed because its table halves are complete and an uncommitted applied migration is the worse state - but the Functions section there is known-wrong until this lands.
Activity
-
SCOPE UPGRADE - this is a dated REGRESSION affecting the whole schema SSOT, not a functions-count bug. Measured same-DSN, information_schema vs pg_catalog: routines 39/116, triggers 0/56, table_constraints 0/224, columns 1322/1322 (fine), tables 110/110 (fine). triggers needs TRIGGER privilege and table_constraints needs a privilege OTHER than SELECT; the exporting role holds SELECT only. query_pks/query_fks/query_uniques ALL read table_constraints, so the doc loses every PK, FK and UNIQUE at once. columns/tables survive because SELECT suffices - which is why it looked healthy. WHY INVISIBLE: triggers/pks/fks/uniques render PER-TABLE via dict.get(t, default), so an empty result renders NOTHING rather than an empty section; only "if functions:" guards a header, so Functions stayed visible as a count while the rest vanished leaving no shape behind. ROOT CAUSE PINNED: e0d34af 2026-10-04 (PLUTO-701) added the DATABASE_URL_DIRECT fallback; DATABASE_URL is empty on this host so every run since connects as a SELECT-only agent role instead of the privileged one. PLUTO-701 made the exporter work on a machine without DATABASE_URL and silently turned a privileged export into an unprivileged one. MEASURED LOSS from schema.md history: 3670281 (2026-09-22) and earlier pk=55 trg=108 Functions=112; de11c2f (2026-10-04) and later pk=0 trg=0 Functions=37. Degraded across EIGHT schema.md commits, 585c8b7 included. The archive triggers are absent entirely - zero hits for trg_ or fn_archive in the current snapshot. Correcting my own earlier framing: the detector was NOT a count that failed to move - counts moved hugely (112 to 37 in one commit) and eight pushes passed without anyone reading the summary line; the per-table silent omission is the actual reason. FIX SCOPE now five queries: routines, triggers, and table_constraints x3 (pks/fks/uniques) all to pg_catalog, functions rendered oid::regprocedure so overloads separate by signature. ALSO FOUND: query_indexes already reads pg_indexes (unfiltered, correct) but its result is assigned and never rendered - dead code. SIXTH ITEM, not requested, and the real control: the exporter must REFUSE to write a snapshot whose section counts collapsed, and must print the connected role in the file header - catalog reads fix this instance, a completeness assert fixes the class, because a generator that silently emits less when it can see less has no failure mode. SIBLINGS (closing condition 1): mars/db/export-db.py:193 and venus/db/export-db.py:189 both read information_schema.routines; all five export scripts (pluto, mars, venus, ayudarg-com-ar, ayudarg-solo) read information_schema columns/table_constraints/key_column_usage/triggers. Same defect every one. Only pluto is mine to push; whether mars/venus are actually degraded depends on which role runs their export, unmeasured and not guessed.
-
FIX LANDED 6063de5 / v2.23.135. All six items in one push. Queries moved to pg_catalog: query_pks/query_fks/query_uniques to pg_constraint (were information_schema.table_constraints, which needs a privilege other than SELECT and returned 0 of 224); query_triggers to pg_trigger with the tgtype bitmask expanded to preserve the (name,timing,event) shape (was information_schema.triggers, needs TRIGGER privilege, returned 0 of 56); query_functions to pg_proc with oid::regprocedure so overloads separate by signature (was information_schema.routines, 39 of 116). query_indexes already read pg_indexes correctly but its result was assigned and NEVER RENDERED - now rendered, skipping indexes that merely back a PK or UNIQUE constraint. COMPLETENESS GUARD added: the export refuses to write a snapshot whose section counts collapsed against the committed one, prints the connected role in the file header, and embeds a section-counts marker to compare against; total loss from a nonzero prior is always refused, a genuine contraction is recorded with --allow-shrink. Summary line now carries keys/constraints/triggers/indexes so a future collapse is visible on one line. FORCED RED BOTH DIRECTIONS. Content: before, FK/TRIGGER/UNIQUE/INDEX lines were 0/0/0/0 and the three functions migration 131 created were absent entirely; after, 119 FKs, 112 trigger events, 11 composite UNIQUEs, 173 INDEX lines, Functions 39->116, all three 131 functions present by signature, and all 24 live archive triggers verified present with none dropped by the tgtype expansion. Guard: 5/5 cases as specified - real pre-fix degraded counts REFUSED, single section to zero REFUSED, identical counts PASS, legitimate small shrink PASS, degraded with --allow-shrink PASS; the refusal text names the privilege cause and the role. NOTHING LOST: views 131->131, column rows 779->779, Purpose lines 110->110, non-TODO purposes 106->106; the 58 apparently-deleted column lines are those same lines gaining a PK note, matching pkColumns=58 exactly. PTD: pushed at v2.23.135, remote package.json on main reads 2.23.135, live health was still serving 2.23.134 at check time with the build in flight - version match to be confirmed on the next read, not claimed here. NOT FIXED HERE, needs the owning lanes: mars/db/export-db.py:193 and venus/db/export-db.py:189 read information_schema.routines, and all five scripts (pluto, mars, venus, ayudarg-com-ar, ayudarg-solo) read information_schema columns/table_constraints/key_column_usage/triggers; whether each is degraded depends on which role runs its export, unmeasured and not guessed.
-
Landed 6063de5 / v2.23.135, all six items (pg_constraint/pg_trigger/pg_proc migrations off information_schema, query_indexes rendered, completeness-collapse guard with --allow-shrink, connected-role header). PTD version-match PASS (remote main package.json 2.23.135 = live health 2.23.135, deploymentId dpl_5DPYktHj->dpl_74W7FW3c, dbOk true). Forced red 5/5 on the guard; forced-red content FK 0->119, TRIGGER 0->112, UNIQUE 0->11, INDEX 0->173, functions 39->116, three 131 functions present by signature. 58 apparently-deleted column lines verified as PK-note modifications (pkColumns=58 match), not deletions.
-
completed
-
FOLLOW-UP LANDED acc9d0f / v2.23.140 (coder-pluto-cc). 6063de5 left ONE section on information_schema - query_columns - and it survived the sweep BECAUSE its count matched: 779 column rows before, 779 after. That agreement was not evidence. information_schema.columns filters rows to relations the connected role holds some privilege on, exactly as routines/triggers/table_constraints do; it matched only because columns need nothing beyond the SELECT coder_pluto_cc_ro still had. Lose SELECT on a table and its columns leave the view with it. Second half, and the reason this was unguarded rather than merely unfixed: check_completeness counted tables, views, functions, lookupCategories, pkColumns, fks, uniqueColumns, compositeUniques, triggerEvents and indexes - every section that had already visibly failed - and NO column count. The one section still reading a privilege-filtered view was also the only one the guard could not see collapse. Fixed in acc9d0f: query_columns on pg_attribute/pg_class/pg_namespace/pg_type + pg_attrdef; 'columns' added to the counts dict; and the SECOND information_schema.columns read in export_to_sqlite (reference-data export, not covered by the completeness guard at all) moved to the same catalog query. No live information_schema reads remain - the two surviving mentions are a docstring and the guard's error text. Fidelity limits stated in the commit: udt_name is the UNDERLYING type so it diverges from pg_type.typname on a DOMAIN column (zero CREATE DOMAIN in db/migrations, zero Postgres domains in schema.md, so interchangeable here and not in a schema that grows one); character_maximum_length computed as atttypmod-4, varchar/bpchar only, display-only, no type(n) rendering in the current snapshot. NOT VERIFIED AGAINST THE LIVE DB. This seat holds no Pluto DSN, so pnpm db:export was not run and db/schema.md is unchanged by acc9d0f. The next export by a credentialed seat is what proves the catalog read lossless. PTD: remote main package.json 2.23.140, live /api/health 2.23.140, deploymentId dpl_AG4ULXk71z2gZ5u9jAPynVgjJQLE -> dpl_3dsUfmS4iSQvJ34hBKx7GWwFyi41, dbOk true. Class-S, both files build-inert. Siblings NOT checked by this row: mars/venus/ayudarg export-db.py each have the same two reads and the same guard shape.