PUBLIC pg_net grants give Terra _ro roles effective writes; cron grant remains dormant

Sub-tickets

Activity

• coder-terra-cc created · 2w ago
• coder-terra-cc audit-finding · 2w ago
  MARKER-t257-revoke-mulc4: option (a) may be unexecutable from the connection we have, and would LOOK like it worked. db-enamel-ca, verified live 11:19 ART: the cron/net relations are owned by supabase_admin and the PUBLIC ACL entries were granted BY supabase_admin. The current MCP connection is postgres, and pg_has_role(postgres, supabase_admin, 'MEMBER') is false. In PostgreSQL a REVOKE only removes grants the revoking role itself made. A role that is not the grantor and not a member of the grantor revokes nothing - and it does not error. So a REVOKE issued through the project connection can return success while the PUBLIC write reach stays exactly where it was. THIS IS THE CLASS OF THING THAT SHOULD SCARE US: it is not that the fix fails, it is that the fix reports success. Anyone checking closure by 'the command ran' would have marked TERRA-257 done with the condition untouched, and the roles still named '_ro'. REQUIRED FOR CLOSURE, whichever option is chosen: has_table_privilege re-checked against the specific relations AFTER the change, from a session that is not the one that made it. Never the command's exit status. If (a) is chosen it needs a platform-owner action, not a project-connection REVOKE. This strengthens (b) - rename the roles to stop asserting read-only - as the option actually reachable from where we stand. Still Elazar's call. db-enamel-ca made no changes.
• coder-terra-cc audit-finding · 2w ago
  MARKER-t257-usage-mulc5: audit-general-ca BLOCKED my framing and is right. Correcting the row before it propagates. WHAT I GOT WRONG. I wrote that PUBLIC's write grants on cron/net mean no '_ro' role on this cluster is read-only, and named db_terra_cc_ro, db02_terra_cc_ro and audit_terra_cc_ro as misnamed. That does not follow. A table privilege is only reachable through schema USAGE, which is a SEPARATE grant. audit-general-ca verified on a different Supabase ref that anon holds DELETE on cron.job_run_details and NO USAGE on cron - the privilege is there and unusable. I read one term of a two-term rule and announced the conclusion. WHAT IS ACTUALLY ESTABLISHED: PUBLIC holds those table privileges on the terra ref (db-enamel-ca, live). Nothing about whether any named role can exercise them. WHAT IS UNMEASURED AND IS NOW THE FIRST TASK: per role - db_terra_cc_ro, db02_terra_cc_ro, audit_terra_cc_ro - has_table_privilege AND schema USAGE on cron and net, ON THE TERRA REF. Evidence from another ref does not transfer, in either direction. FURTHER GATES audit-general-ca adds for option (a), beyond the grantor problem already on this row: these are extension-owned relations, so a supported owner-level change is needed rather than a project REVOKE, with explicit replacement grants and an end-to-end pg_net/cron regression test before cutover - and extension upgrades can RESTORE the grants, so closure needs a recurring drift check, not a one-time verification. The shared-cluster breakage concern stands. No terra DB write or live ACL probe by audit-general-ca.
• coder-terra-cc verified · 2w ago
  MARKER-t257-measured-mulc8: CONFIRMED, and it splits cleanly - net is a real exposure, cron is not, and the TERRA-253 provisioning would have created the cron one. db-enamel-ca, measured live on ref eprpywqwzsvrjeaforei, read-only, for db_terra_cc_ro / db02_terra_cc_ro / audit_terra_cc_ro: NET - EXPOSED, all three roles. has_schema_privilege(net,USAGE)=true, and on BOTH net._http_response and net.http_request_queue has_table_privilege is true for SELECT, INSERT, UPDATE, DELETE, TRUNCATE and MAINTAIN; the live ACL also shows REFERENCES and TRIGGER. Both terms of the rule are satisfied, so this is exercisable today, not theoretical. net.http_request_queue is the OUTBOUND HTTP queue: write access there is the ability to make the database emit requests of the writer's choosing, which is a materially different thing from reading a schema. CRON - NOT exercisable. Schema USAGE=false on all three. Relation privileges exist (SELECT on cron.job, SELECT+DELETE on cron.job_run_details) but without schema USAGE they cannot be reached. This is exactly the term I had missed and audit-general-ca caught. THE TRAP FOR TERRA-253: export-schema.sh reads cron.job and counts cron.job_run_details, so provisioning db_terra_ca_ro to run it means granting cron USAGE - which would ACTIVATE the dormant DELETE on cron.job_run_details for that role. The provisioning that was blocked on authorization would have created a second exposure as a side effect of a schema snapshot. SO THE EXPORTER SCOPE CHANGE IS NOW CORRECT, and I said the opposite earlier on this row. My objection was that narrowing the script fixes the exporter and leaves the ACL untouched - true for net, where the reach exists regardless. But for cron the reach is NOT live, and narrowing is what keeps it not live. Dropping the cron section from export-schema.sh is a real control, not a silenced log line. STILL OPEN: the net exposure, which no scope change touches. Options and their gates are on this row above.
• coder-terra-cc note · 2w ago
  MARKER-t257-crondrop-mulcf: exporter scope change pushed as 38fda9c (db/export-schema.sh, -38/+11). export-schema.sh no longer reads cron contents, so provisioning a role to run it no longer requires cron schema USAGE and therefore cannot activate the dormant DELETE on cron.job_run_details. REQUIRED_SECTIONS updated in the same commit so the generator's own guard stays consistent with what it emits. Net section retained deliberately - USAGE is true there and the reach is live, so removing it would hide an exposure instead of closing one. CARRIED HERE SO IT IS NOT LOST WITH THE SECTION, because a rewrite must be judged on what it drops and not only on what it adds. The removed cron text recorded a finding that the replacement exclusion note does not: cron.job carries no appKey and no tenant column of any kind, so a job either tenant schedules is visible and runnable by the other's superuser role, and zero terra function or trigger references cron. anywhere (pg_proc grepped 2026-08-16). It also recorded the right way to read the row count - a zero is a clean result about TODAY, not a property of the object, because cron.job accepts an un-scoped INSERT from either tenant with nothing in place to notice. That was the original TERRA-12 finding and it is now recorded here rather than in a generated file. STILL OPEN ON THIS ROW: the net exposure, which no scope change touches, plus its gates - owner-level action rather than a project REVOKE, a REVOKE that can report success without removing anything, replacement grants with an end-to-end pg_net/cron regression before cutover, and a recurring drift check because an extension upgrade can restore the grants.
• db-enamel-ca statusChanged · 1w ago
  PM GO 2026-10-05 22:48 ART; refreshing live ACL/role reach and owner path. Prior Elazar decision still unrecorded in row; no write.
• db-enamel-ca verified · 1w ago
  Live 2026-10-06 read on shared ref eprpywqwzsvrjeaforei: db_terra_cc_ro, db02_terra_cc_ro, audit_terra_cc_ro each have net USAGE=true and effective INSERT/DELETE on net.http_request_queue and DELETE on net._http_response. PUBLIC grants all table privileges and net schema USAGE; both relations and schema are supabase_admin-owned/granted. cron.job_run_details DELETE is a dormant table privilege because all three roles have cron USAGE=false. postgres current_user is not MEMBER of supabase_admin, so project connection cannot reliably remove owner grants. db_terra_ca_ro does not exist yet. net.http_get/post/delete/collect_response are INVOKER and executable by db_terra_cc_ro, with net USAGE; extensions.grant_pg_net_access EXECUTE is true but extensions USAGE=false for these roles. No grants changed. Owner-level supported revoke/replacement, post-change effective-privilege readback, end-to-end pg_net regression, and drift check remain required for real _ro boundary.
• db-enamel-ca spec · 1w ago
  Proposed owner-level remediation for Elazar choice (not applied): supabase_admin/platform must remove PUBLIC net.USAGE and PUBLIC write privileges on net.http_request_queue and net._http_response, then grant only the extension/app runtime principals whose pg_net calls need them. Preserve cron USAGE=false for named read roles. Before cutover, enumerate pg_net callers/ACLs and replace needed grants; rehearse an outbound HTTP request through each supported app path and cron path. After cutover, independently verify has_schema_privilege + has_table_privilege and net.http_post EXECUTE as each db_terra_cc_ro/db02_terra_cc_ro/audit_terra_cc_ro and new db_terra_ca_ro; expected no usable net write path. Add recurring drift check because extension/platform upgrades may restore PUBLIC grants. Current postgres connection is not supabase_admin member, so an apparent REVOKE success here is not evidence. Alternative if platform cannot change: rename roles to reflect net write reach and explicitly accept the risk; do not issue another *_ro role under today's ACL.
• db-enamel-ca blocked · 1w ago
  Live net write reach is confirmed for existing *_ro roles, but PUBLIC/schema/table grants are supabase_admin-owned and this postgres connection cannot remove them. Awaiting Elazar/platform-owner choice and action: supported owner-level revoke plus replacement grants/regression/drift check, or explicit rename/risk acceptance. Read-only evidence and precise remediation spec are on this row; no ACL mutation made.
• db-enamel-ca titleChanged · 1w ago
  PUBLIC pg_net grants give Terra _ro roles effective writes; cron grant remains dormant
• db-enamel-ca verified · 1w ago
  Additional live object missed by table-only scans: net.http_request_queue_id_seq is a supabase_admin-owned sequence with PUBLIC =rwU; db_terra_cc_ro has effective USAGE, UPDATE, SELECT. Any platform remedy claiming a real read-only boundary must include this sequence, not just net tables/functions. Existing ENAMEL-41 covers the same project's pg_net function ACL/version-gate support ask but does not by itself remove the PUBLIC table/sequence writes TERRA-257 tracks. Repo search confirms enamel app uses a Venus HTTPS timer for outbox drain and no current app SQL references net.http_* outside migrations/comments; BWI ENAMEL-41 records zero in-database pg_net consumers at its prior census. This is dependency evidence, not an authorization to REVOKE.
• pmmaster-evolutiva-cc note · 1w ago
  pmmaster 2026-10-06: the net leg of this row is the same finding as MARS-712 and TERRA-262's security limit - one upstream cause (extensions.grant_pg_net_access gates its REVOKE FROM PUBLIC on extversion 0.2-0.11.0 while the refs run 0.20.3). Consolidated into ONE Elazar ask across all three rows. Does not change this row's own open question (platform-owner REVOKE vs renaming the _ro roles); it means the net half will be answered by the same decision.