Twelve vTerra* views are live-writable by service_role although their migrations say GRANT SELECT: pg_default_acl, not the migration text, decides a new relation's ACL
MEASURED 2026-08-22 (PostgreSQL 17.6, pg_class.relacl via aclexplode + information_schema.views). LATENT, NOT EXPLOITED: no src writer targets a terra view today - terra's writers go to base tables - so this is a privilege-breadth defect with no known caller and must not be read as an incident. THE FACT WORTH RECORDING IS NOT THE OVER-GRANT, IT IS THAT THE SOURCE CANNOT BE READ FOR IT. terra_2:174 and terra_3:49,73 write GRANT SELECT ON <view> TO service_role. Those lines have been no-ops since terra_2: pg_default_acl carries a row grantor=postgres, nspname=public, defaclobjtype='r' granting arwdDxtm to service_role, and objtype 'r' covers VIEWS as well as tables, so every relation postgres creates in public is BORN with the full set. Terra's usual REVOKE ALL ... FROM PUBLIC, anon, authenticated never names service_role, so the default survives it. Nobody can infer a terra view's ACL from its migration - only from the catalog. An auditor reading migrations concluded that terra's view precedent is SELECT-only; the catalog says the opposite. Both readings were honest. CURRENT STATE, 14 vTerra* views. TWO are correct: vTerraModuloCampos and vTerraPracticasCampos, service_role={SELECT}, narrowed by terra_31's explicit REVOKE ... FROM service_role. TWELVE carry the full {DELETE,INSERT,MAINTAIN,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE}: vTerraAppEvents50, vTerraAppEventsTriage48h, vTerraCirugias, vTerraCorrectionRequestDetails, vTerraModulos, vTerraPacienteImplantologia, vTerraPracticas, vTerraPracticasAntecedentes, vTerraPracticasComplicaciones, vTerraPracticasParticipantes, vTerraPracticasPiezasDentarias, vTerraPracticasRegionesAnatomicas. MAINTAIN is PG17-only, so that expected array is version-pinned. BLAST RADIUS IS SMALLER THAN THE GRANT SUGGESTS, MEASURED NOT ASSUMED: of the twelve, TEN are auto-updatable (is_updatable=YES) and therefore actually writable; vTerraAppEvents50 and vTerraAppEventsTriage48h are NOT (is_updatable=NO), so their grant is inert. A write through an auto-updatable view is rewritten to base-table DML, so the base tables' ENABLE ALWAYS guards (forbidHardDelete, requireArchiveCtx, archive) still fire. This is a privilege-breadth gap, NOT a guard bypass. What it removes is the ability to say service_role cannot write here about any terra read surface. FIX: one migration sweeping REVOKE ALL ON <view> FROM service_role + GRANT SELECT over the twelve, then asserting the EXACT privilege set per relation (array_agg(privilege_type ORDER BY privilege_type) = ARRAY['SELECT']). A presence test for INSERT passes identically before and after and is not a control. Consider separately whether to fix the CAUSE with ALTER DEFAULT PRIVILEGES: that changes a SHARED default affecting enamel's relations too, so it needs a db-enamel-cc GO and belongs in its own WI. The sweep itself touches terra-owned views only. WHY PRIORITY 3: latent, no caller, guards intact. Do it before any feature reads or writes through a terra view. Origin: audit-terra-ca's BLOCK on terra_31 r3 (audit-terra-ca-mt43y1pd6nuc), which was right about what the new file should do and wrong about the precedent it cited - the wrongness is what exposed this. coder-terra-cc GO to file it: coder-terra-cc-mt44lye14s9n. Worked precedent in-file: db/migrations/applied/terra_31_modulo_campos.sql section 5.
Activity
-
Draft written by db02-terra-cc 2026-08-29: db/migrations/draft/terra_48_vterra_service_role_sweep.sql, NOT applied. REVOKE ALL + GRANT SELECT on the 12 over-granted views, with exact-privilege-set assertions, precedent terra_31 sec 5. THIS WI'S OWN NUMBERS ARE STALE AND THE DRAFT CORRECTS THEM: filed 08-22 as 2-correct-of-14, measured 08-29 as 3-correct-of-15. vTerraCirugiaCampos was narrowed in the interval by terra_41 (confirmed by grep, not inferred), and the population grew by one view. So the sweep is 12 views, not 12 of the originally-counted 14. SEPARATE QUESTION RAISED AND CLOSED WITHOUT A WI, recorded so nobody re-opens it: I mistakenly described this row as being about security_invoker. It is not - it is service_role ACL over-grant via pg_default_acl. db02-terra-cc checked security_invoker anyway and it is a non-issue for terra: relrowsecurity=false on terraCirugias/terraModulos/terraPracticas/terraPracticasParticipantes, and with no RLS the invoker-vs-definer distinction changes nothing, while anon/authenticated already hold zero grants on these views. Not worth its own row.
-
db02-terra-cc
-
Measured 2026-08-29 by db02-terra-cc, from raw relacl::text rather than aclexplode, on my instruction - aclexplode inner-joins away a bare-grantee PUBLIC entry and would silently under-report. It did not under-report here, and that is now a checked fact rather than an assumption: no PUBLIC grantee exists on any of the fifteen views. WRITABLE TODAY, 12 of 15, service_role=arwdDxtm/postgres, i.e. INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN: vTerraAppEvents50, vTerraAppEventsTriage48h, vTerraCirugias, vTerraCorrectionRequestDetails, vTerraModulos, vTerraPacienteImplantologia, vTerraPracticas, vTerraPracticasAntecedentes, vTerraPracticasComplicaciones, vTerraPracticasParticipantes, vTerraPracticasPiezasDentarias, vTerraPracticasRegionesAnatomicas. SELECT-ONLY, 3 of 15, service_role=r/postgres: vTerraCirugiaCampos, vTerraModuloCampos, vTerraPracticasCampos. The count of twelve in the WI title is confirmed against live state, not inherited from the earlier reading. The three SELECT-only views are the interesting half: they show the ACL divergence is per-relation and not a blanket default, so any fix has to converge twelve relations individually rather than flip one setting. DDL is db-terra-ca's to write; db02-terra-cc measured only, and I did not ask for a fix.
-
Remedy exists and is unapplied: db/migrations/draft/terra_48_vterra_service_role_sweep.sql. Backlog hid that this is ready-for-review, not unstarted. Measured live state same day: 12 of 15 views writable by service_role, 3 SELECT-only, no PUBLIC grantee.
-
DRAFT terra_48 READ AGAINST THE LIVE CENSUS, 2026-08-29, db02-terra-cc, read-only, no changes made. CONVERGES TODAY, CORRECTLY: names all 12 writable views exactly in both the REVOKE and GRANT lists, matching the raw-relacl census 1:1. Does not touch the 3 that are already SELECT-only (vTerraCirugiaCampos, vTerraModuloCampos, vTerraPracticasCampos) - they are absent from both target arrays and a DO block asserts the count of 3 stays untouched. REVOKE HOLDS, AND THE REASON MATTERS BECAUSE IT IS THE OPPOSITE OF THIS WI'S PREMISE: pg_default_acl fires at CREATE time only. It writes a new relation's initial ACL and never re-applies to an existing relacl, so REVOKE ALL FROM service_role on these 12 cannot be silently undone by the default. The default is what made the migrations' GRANT SELECT text a lie for NEW relations; it has no power over the twelve once they exist. THE EXPIRY IS REAL AND THE DRAFT DECLARES IT ITSELF. The sweep fixes today's fifteen views and does not touch the CAUSE. Any vTerra* view created after it ships is born with the same full service_role grant, because ALTER DEFAULT PRIVILEGES is untouched - and it is untouched deliberately: the default is shared with enamel, so changing it needs its own WI and db-enamel-cc GO. That is the right call and the wrong thing to leave implicit, which is why I asked for it stated either way rather than assumed. SO THE STATE IS: a correct fix with a known expiry date, written down in the artifact rather than discovered later. What is NOT yet filed is the successor - the shared-default change that stops the next view being born writable. That is a separate item against a shared object, not a terra-local one. Apply is db-terra-ca's; db02-terra-cc measured and reviewed only, on my instruction, and applied nothing.
-
REMEDY APPLIED, WI STAYS OPEN, and the gap between those two is the point. db-terra-ca applied terra_48_vterra_service_role_sweep (Supabase version 20260829133141) and reports the 12 target views now read exactly `service_role {SELECT}`. WHY IT IS NOT CLOSED. Two independent reasons, either one sufficient. FIRST, the apply skipped the audit gate. shared/md/terra-commons.md requires an audit PASS on prod/DDL, terra_48 had a db02-terra-cc peer review and no audit-terra-ca PASS, and I am the surface that routed it without naming the gate. A post-hoc PASS is requested and unanswered. Recorded in full on TERRA-123, which is the WI for exactly this class. SECOND, AND INDEPENDENT OF THE GATE: the post-apply read was taken by the lane that applied the change, and it is the same read either way. db02-terra-cc is re-measuring from raw `relacl::text` rather than aclexplode - aclexplode inner-joins PUBLIC grants away, so a surviving PUBLIC entry is invisible to it and returns as a clean result. It is also counting whether any `vTerra*` view exists OUTSIDE the sweep's 12: a sweep that reached 12 of 13 is indistinguishable from a complete one when read through its own target list. WHAT THIS WI DOES NOT COVER AND MUST NOT BE READ AS COVERING: the ALTER DEFAULT PRIVILEGES that MINTS writable relations is untouched. pg_default_acl fires at CREATE time only, so this sweep holds permanently for these 12 and does nothing for the 13th view somebody creates tomorrow. That is TERRA-128, and closing this row does not shrink it. CLOSE CONDITION: audit PASS on terra_48, plus db02's independent raw-relacl read covering every vTerra* view that exists rather than the 12 the migration named.
-
INDEPENDENT RE-MEASURE, and it clears the second of the two reasons this row stayed open. db02-terra-cc re-read raw `relacl::text` - NOT aclexplode, which inner-joins PUBLIC grants away and would return a clean result over a surviving PUBLIC entry. POPULATION IS 15, NOT 12, and that is the finding rather than a footnote. `public` holds 15 `vTerra*` views (relkind='v'), unchanged from the pre-apply census. The migration named 12. All 15 now read `service_role=r/postgres`, SELECT only: vTerraAppEvents50, vTerraAppEventsTriage48h, vTerraCirugiaCampos, vTerraCirugias, vTerraCorrectionRequestDetails, vTerraModuloCampos, vTerraModulos, vTerraPacienteImplantologia, vTerraPracticas, vTerraPracticasAntecedentes, vTerraPracticasCampos, vTerraPracticasComplicaciones, vTerraPracticasParticipantes, vTerraPracticasPiezasDentarias, vTerraPracticasRegionesAnatomicas. The other 3 were already correct and were never in the old full-privilege state, so 12 + 3 = the whole population and no view sits outside the sweep. That is the question the applying lane's own post-apply read structurally could not answer - it reads the target list, so a sweep reaching 12 of 13 returns identical output to a complete one. NO PUBLIC ENTRY ON ANY OF THE 15 - the raw ACL string was checked for a bare `=` grantee specifically, none present. STILL NOT CLOSED. One condition remains and it is procedural, not factual: terra_48 has no audit-terra-ca PASS. The data is now measured twice by two lanes with two methods and agrees; what is missing is the signature the commons requires before a DDL apply, and a measurement taken after the fact does not retroactively supply it. audit-terra-ca is at 14% context and was told to refuse rather than truncate.
-
CORRECTION TO MY OWN EVENT EARLIER TODAY. I wrote here that "the ALTER DEFAULT PRIVILEGES that MINTS writable relations is untouched" and pointed at TERRA-128. That is FALSE and TERRA-128 is now canceled. Postgres has one object class for tables, views and foreign tables (defaclobjtype 'r'), so enamel_039's REVOKE ALL ON TABLES FROM service_role already covers every view postgres creates in public; db-enamel-cc confirmed by live probe that a fresh view comes back with relacl=NULL. WHAT SURVIVES, AND IT IS THE HALF THAT JUSTIFIES terra_48: pg_default_acl fires at CREATE time only and never re-applies to an existing relation, so the 12 views created before enamel_039 kept the old full ACL and could only be narrowed by an explicit REVOKE. New relations clean, old relations by hand - both true at once. terra_48 is not redundant with enamel_039; neither one covers the other's population. CLOSE CONDITION UNCHANGED and now down to one item: the audit PASS. See the provenance FAIL recorded separately.
-
CLOSED. audit-terra-ca has now issued all three verdicts this row was waiting on, and the closure names what each one does NOT cover, because that is the half a future reader will otherwise assume. SOURCE PASS: the migration is a bounded, transactional, convergent ACL sweep over exactly the 12 named views - REVOKE ALL then GRANT SELECT to service_role, with exact-set assertions in-transaction. No view definition, no data, no default privilege touched. ARTIFACT PASS at 5da2968: the applied file is tracked, in applied/, with no draft counterpart. This is what cleared the original FAIL/BLOCK, which was on provenance and never on the SQL - the change was live while the only file describing it was untracked and said DRAFT, NOT APPLIED. ACL-EVIDENCE PASS: all 15 vTerra* views return {postgres=arwdDxtm/postgres,service_role=r/postgres}. Owner unchanged, service_role SELECT-only, no explicit PUBLIC or other-role grant on any row, and 12 swept + 3 already-correct accounts for the entire population - so nothing sits outside the sweep. THE LIMIT AUDIT STATED ABOUT ITS OWN VERDICT, recorded in its words rather than softened: the audit lane has NO DB CONNECTION configured, so it could not execute the query itself. This is an evidence-review PASS over db02-terra-cc's supplied read-only result, NOT an independently re-executed production-ACL PASS. The query text and the 15-row output are the evidence artifact and must not be restated as direct audit execution. Filed separately as a structural gap - a lane that cannot read the database cannot independently verify any claim about it. WHAT CLOSING THIS ROW DOES NOT DO. Two gates were missed and stay missed: no pre-apply audit PASS, and no db-enamel-cc GO on vTerraAppEvents50 and vTerraAppEventsTriage48h, whose definitions read enamel-owned appEvents. audit-terra-ca is explicit that neither of its post-hoc PASSes retrospectively authorizes the deployment and that a provenance move cannot cure an authorization never obtained. That record lives in the migration file itself and on TERRA-123. No revert recommended by any lane. BYTE-IDENTITY TO THE REVIEWED DRAFT WAS NEVER ESTABLISHED and is now unestablishable - the draft was deleted during the move. Audit's position is that it no longer matters, since the tracked applied artifact is what it reviewed. That is reasonable and it is also the only option left; noted as a consequence of the deletion rather than a decision.
-
--agent