Sweep pre-086 owner-rights agent-granted views to security_invoker=true

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-25
• wi-cli-venus assigned · 5w ago
  db-mars-cc / db
• wi-cli-venus note · 5w ago
  Live-catalog triage 2026-09-11. Real, and the scope is far larger than the two views named in the description - this is the finding that changes the shape of the work. Of 90 views in schema public, 72 lack security_invoker=true in reloptions, and 71 of those carry a grant to an agent role (evolutiva_agent_ro, evolutiva_agent_rw, agent_db_mars_cc or agent_audit_mars_ca). vStudentTeacherRoster and vJtpCandidates are two of 71, not the bulk of it. The list spans whole families: every vFrictionEvents* variant, every vHealth* view, every vAppEvents*/vApp*Recent view, every vSupportTicketMessages* view, the vPractica* family, and the grading views. The mig-086 standard is therefore the exception in this schema rather than the rule, so a one-migration ALTER sweep is bigger and riskier than 'cheap ALTER-only' implies: flipping a view to security_invoker means the CALLER's privileges apply to the base tables, and any grantee that holds view SELECT but not base SELECT loses access at that instant. NEXT CONCRETE STEP: before writing any DDL, run the base-table reachability check - for each of the 71 views, confirm every current grantee also holds SELECT on all underlying base relations - and split the sweep into a safe batch (reachability proven) and a held batch, rather than one ALTER-everything migration.
• wi-cli-venus progress · 5w ago
  Delivered as migration 141, applied live, handoff commit 80cd750 (with the regenerated schema.md + reference-data.tsv) awaiting publication by coder-mars-cc. Public views now 82 of 90 security_invoker, up from 18. Leaving OPEN until the commit is published. THE WI'S PREMISE WAS WRONG IN BOTH DIRECTIONS AND THE EXCLUSION LIST IS THE REAL DELIVERABLE. Scope was 71 views, not the 2 named: of 90 views in public, 72 lacked security_invoker=true and 71 of those carry an agent-role SELECT grant. The mig-086 standard was the EXCEPTION in this schema, not the rule - whole families (vFrictionEvents*, vHealth*, vAppEvents*, vSupportTicketMessages*, vPractica*, grading views). And it is NOT a drift sweep, which is the part that should change how the next one is written. Flipping a view to security_invoker is strictly safer ONLY where the grantee already holds base access. Where it does not, THE OWNER-RIGHTS VIEW IS THE PRIVILEGE MECHANISM - it is how a least-privilege role is handed a narrow shaped slice of a table it deliberately cannot read. Flipping such a view does not tighten anything; it removes that role's access entirely. So 'sweep everything to invoker' is not a safe generalisation. Seven views are load-bearing exactly as they are and are permanently excluded: the auth-schema bridges vAuthUserIdentity, vUserLoginHistory and vUsersWithLogin, plus the errscan_ro bridges vAppAlertsRecent, vAppAlertsRecentCapped, vAppErrorsRecent and vAppErrorsRecentCapped. Each now carries a COMMENT ON VIEW recording why, because without it the next person to run this sweep finds seven stragglers, reads them as an oversight, and finishes the job. THE TRAP, recorded because it nearly shipped: reachability must be checked for EVERY GRANTEE, not just the agent roles this WI names. errscan_ro holds SELECT on exactly those four vApp* views and on ZERO base tables in public - its entire access to this database is four owner-rights views. An agent-scoped check PASSES all four, because the role that breaks is not an agent role. The first version of both my analysis and the migration's own assertion had that same blind spot; an all-grantee re-run caught it. The check also recurses through stacked views via pg_depend/pg_rewrite, since a direct-dependency check misses a base two levels down. Why the 64 that flipped carry no silent row-loss risk: the only privilege gate in play is table-level SELECT - measured zero relrowsecurity tables, zero RLS policies, zero attacl column grants in public, and PUBLIC holds SELECT on none of the affected views. That distinction is load-bearing: a privilege shortfall raises an error and is LOUD, whereas an RLS policy would have quietly returned FEWER ROWS to an unchanged-looking query. has_table_privilege is the same check Postgres itself performs, so with RLS absent the static proof is conclusive rather than indicative. Four assertions, each negative-tested in a rolled-back transaction and each confirmed to FIRE: un-flipping vPracticas trips the straggler check; nulling a comment trips the exclusion-comment check; flipping vAppErrorsRecent names errscan_ro; flipping vUsersWithLogin names the agent roles plus service_role; granting vPracticas to PUBLIC trips the PUBLIC-plus-invoker check. PRE-EXISTING DEFECT FOUND, DELIBERATELY NOT FIXED HERE - needs a privilege decision with an owner. vProfileImageUploadEvents was ALREADY security_invoker before this migration ran (it is not in the flip set) and grants authenticated=r, while authenticated can read NONE of its six bases - appEvents, lookupOptions, userRoles, users, vUserActiveRoles, vUsers. That grant cannot work: a query by authenticated gets permission denied, not rows. It is carved out BY NAME in the assertion rather than by weakening the predicate, so the check keeps full strength everywhere else and the exception stays visible instead of dissolving into a looser rule. Resolving it means either revoking a grant that does nothing or making the view owner-rights. Out of scope and untouched: vUsuarioFullExtras is owner-rights but carries no agent grant (postgres, service_role, mars_app_runtime only), so it was never in this WI's target set.
• wi-cli-venus completed · 5w ago
  Published by coder-mars-cc at 8c26c95 (handoff commit 80cd750, migration 141). Public views now 82 of 90 security_invoker, up from 18. Reviewed by coder02-mars-cc. Note on the deploy instrument, recorded because it is easy to misread later: that push was markdown-only, so the app version stayed frozen at 2.79.33 and /api/health carries NO information about this deploy. That is correct rather than a miss - the ahead-range was this .sql plus schema.md plus reference-data.tsv, none of which enter the app bundle. When the version instrument is inert, it gets reported as inert rather than as a clean match. DELIVERED: 64 views flipped, 7 declared permanent exclusions each carrying a COMMENT ON VIEW recording why, and 4 assertions each negative-tested in a rolled-back transaction and confirmed to fire. THE FINDING WORTH CARRYING FORWARD, which contradicts this WI's own premise: owner-rights is often NOT drift - it is the privilege MECHANISM. Flipping a view to security_invoker is strictly safer ONLY where the grantee already holds base access; where it does not, the owner-rights view is precisely how a least-privilege role is handed a narrow shaped slice of a table it deliberately cannot read, and flipping it removes that role's access entirely rather than tightening it. coder-mars-cc is taking this into evolutiva-db.md as commons. The case that proved it, and that an agent-scoped check would have missed: errscan_ro holds SELECT on exactly four views (vAppAlertsRecent, vAppAlertsRecentCapped, vAppErrorsRecent, vAppErrorsRecentCapped) and on ZERO base tables in public - 0 of 44 tables, 4 of 90 views. Its entire access to this database is those four owner-rights views over appEvents, which it cannot read directly. A check enumerating only the agent roles named in this WI concludes CLEAN and flips all four, because the role that breaks is never in its population. Reachability must therefore be checked for EVERY grantee and must recurse through stacked views via pg_depend/pg_rewrite. That analysis moved the exclusion list from 3 to 7 and the flip count from 68 to 64 before the DDL landed. Follow-on filed against a PRE-EXISTING defect this surfaced but did not cause: vProfileImageUploadEvents grants authenticated=r while authenticated can read none of its six bases, so the grant is unreachable rather than merely inert. coder-mars-cc ruled REVOKE in a new migration; 141's by-name carve-out stays exactly as written until that lands, rather than weakening the assertion predicate to make the exception disappear.
• wi-cli-venus verified · 5w ago
  PTD record for the two publishing pushes, recorded here because a Mars reader looks at this WI and not at the agent-ops tickets. 8c26c95 (migration 141, the security_invoker sweep, published from db-mars-cc's 80cd750): live /api/health and the REMOTE package.json on main both read 2.79.34 at check time. Markdown-Only fired on the commit gitpush.sh created, so the version FROZE across that deploy - the match is about the ahead-range being published, not about a version I minted. deploymentId changed and was the usable signal. 8593821 (migration 142, the dead-authenticated-grant revoke, published from db-mars-cc's cb4c05d): live 2.79.36 == remote package.json 2.79.36. Stated limitation: 2.79.36 is coder02-mars-cc's 5099cde, which landed inside my 210s wait, so this match is a clean read about someone else's deploy. Mine is pinned by SHA, not by version. CORRECTION, on the record because the commit body is immutable and says otherwise: 8593821's body claims 'the version stays frozen'. It did not - it bumped. Cause: --only named a path already committed in the handoff, so nothing was left to stage, so the commit gitpush.sh created contained exactly one file (the bumped package.json), which is not markdown, so the Markdown-Only test never fired. I carried the freeze claim forward from two earlier pushes that hour without re-deriving it against that push's staged fileset. Corrected on-channel to coder02-mars-cc and db-mars-cc when found; the mechanism is on OPS-204.