Fleet sweep: authz gate on a side-effecting function call in a FROM clause is not a gate - the function's INSERT fires before the enclosing WHERE
Defect class found by coder02-enamel-cc on 2026-09-12 while building ENAMEL-59, then sharpened by it into a precise discriminator. UNSAFE: select * from fn(...) where <gate> - if fn writes internally, its INSERT runs and the enclosing WHERE then merely declines to return the row, so the gate produces an audit trail of unauthorized writes rather than preventing any. SAFE: insert into ... select ... where exists (...) - the INSERT is the outer statement, the SELECT half evaluates first, a failed gate yields zero rows. DISCRIMINATOR: is the gated call itself the write, or does it wrap a function that writes internally. Neither form is visible in the predicate; both read as a correctly-gated statement, which is why review does not catch it. Known-clean by measurement, do not re-derive: enamel support-core.createTicket (enamel_059) is the safe raw form, so ENAMEL-47 never carried it. Known-affected and already fixed: resolveOrCreatePacienteForApp callers, corrected in ENAMEL-59 (0a9faac) to a separate pre-invocation authz query. SCOPE OF THIS ROW: read-only sweep of terra, mars, enamel and api-images for the unsafe shape. Find every call site where a function that writes is referenced in a FROM clause with an authz predicate in the enclosing statement, and separately every SECURITY DEFINER function that writes and is reachable that way. Report call sites with file and line; DO NOT FIX ANY OF THEM in the same pass - a repair inherits the skip list of the wrong question, and each affected project owns its own remediation through its own db lane. No writes, no gitpush.sh in terra or mars. Deliverable is a list of call sites plus, per project, which paths were NOT searched - a coverage statement, never the word clean. Note for the sweeper: an ORM or query-builder call can produce the unsafe shape without the text 'from' appearing next to the function name, so grep for the function names first and the SQL shape second.
Activity
-
coder02-enamel-cc, fleet STOP 2026-09-11-2356. DONE: enamel migrations scanned (14 write fns found, script in /tmp/find_write_fns.py); ADMIN_OF_ENAMEL/support-core createTicket/enamel_059 confirmed safe (prior). Terra: changeTerraMembership call site (user-admin-actions.ts:71) SAFE - bare FROM, authz via requireAppUserAction before call. createTerraPractica confirmed SECURITY INVOKER, wraps resolveOrCreatePacienteForApp internally, but per route-access.ts's own comment NO app code calls it at all - unreached, not the hazard shape. requestTerraAccess (access-request-actions.ts) bare call, no WHERE - safe, checked prior turn. Mars: create_support_ticket_atomic, create_support_ticket_on_behalf_atomic, reply_support_ticket_atomic, set_user_roles_atomic all SAFE - bare FROM calls, authz (requireCap/adminGuard/session) checked as a separate step before each call, none wrapped in an inline WHERE. cron_watchdog_check/refresh_multiradicular_stats_logged/wal_disk_health_check/practicas_integrity_check: ZERO app-code call sites found (grepped app/lib/components/hooks) - not reachable via app authz at all. No ORM in any of enamel/terra/mars (all raw pg/node-postgres) so pmmaster's ORM-masking concern does not apply. api-images-evolutiva: zero PDO/mysqli/pg_connect/sqlite matches across all .php files (grepped every file individually) - no relational DB layer exists in this project. NOT SEARCHED: terra's fn_terra*Archive/fn_archive*/fn_notify* trigger-shaped functions (assumed trigger-only, not individually confirmed non-FROM-callable); mars's fn_terra*Archive equivalents (not enumerated); terra's other non-listed migrations beyond the 5 files createTerraPractica appears in; no consolidated report sent to pmmaster yet (this event IS the parked state, per STOP's 'no status doc' instruction). NEXT ACTION on resume: confirm trigger-fn scope in terra+mars, then send pmmaster the consolidated per-project report (call sites + SECURITY DEFINER writers reachable + not-searched paths). Uncommitted work: none, zero edits made, read-only sweep throughout.