mars_app_runtime cutover returned dbOk:false in prod - diagnose before re-attempting
2026-09-05. Elazar GO (option A) to split the app off the postgres superuser onto a dedicated low-privilege role so statement_timeout could be scoped to it. db-mars-cc provisioned mars_app_runtime (migrations 127 + 128). I repointed Vercel production DATABASE_URL at it. /api/health?db=1 then returned dbOk:false - the app could not complete a bare SELECT 1 (v2.79.25, dpl_Doc9qeBgg9Yw1xDqC7hdWr5BiHSA, 2026-09-05T08:40:17Z). Rolled back to the postgres role, redeployed, dbOk:true restored at 08:49:02Z (dpl_hEYSKgUAuX9n5SS4DNyEFbYvsQrz). Roughly 9 minutes of student-facing failure. CAUSE UNDIAGNOSED. Do not record a cause in this WI until one is measured. Open candidates, none eliminated: a missing CONNECT/USAGE privilege for the role; migration 128's explicit 15-function allowlist being narrower than the app's real call set (it deliberately dropped 127's blanket EXECUTE and its default-privileges grant); a schema-qualification or search_path difference; or a failure unrelated to the role. TWO PROCESS ERRORS THAT ARE MINE, recorded so the retry does not repeat them: 1. A Vercel env change only takes effect on a NEW deployment. My first verification attempt would have measured the OLD value still being served. vercel env add is not a cutover - the deploy is. 2. I switched production before proving the role could connect at all. The role should be exercised on a direct connection first (psql as mars_app_runtime, SELECT 1, then one real app query per surface), and only then repointed. BEFORE RE-ATTEMPTING: db-mars-cc must confirm what actually failed. Ask it to connect as mars_app_runtime and reproduce; the answer is one query away on its side and was never available on mine, since I only ever saw dbOk:false through the health route. Also unverified and flagged by db-mars-cc: trigger functions (fn_notify_support_ticket_created, fn_archive*) fire via INSERT/UPDATE with no explicit EXECUTE grant. It believes Postgres does not check EXECUTE for trigger invocation, only direct calls, and that is probably right - but /api/health?db=1 and a page load do not exercise it. The retry's acceptance test must include creating a real support ticket or access request. WHAT THIS BLOCKS: statement_timeout cannot be set from app code on Mars at all - Supabase ignores the options startup parameter and a session SET under Supavisor TRANSACTION mode, and DATABASE_URL is the :6543 pooler since MARS-145. Until this lands the runtime has NO query time limit, which MARS-680 (46.7s measured execution) and MARS-689's half-open breaker probe both depend on - audit-mars-ca BLOCKed that probe precisely because an unbounded query makes it hang and fail-fast permanently in that container. Repo-side record: shared/md/mars-unverifiable-state.md, section DATABASE_URL. Related: MARS-689, MARS-690, MARS-691, MARS-680.
Activity
-
Root cause: connection string omitted the Supavisor tenant-identifier suffix on the username, so mars_app_runtime was rejected at connection with FATAL (ENOIDENTIFIER) before any query ran. Not a role or grant defect. Reproduced by db-mars-cc. Separate real gap found while testing: the role has no USAGE on schema auth and postgres cannot grant it (holds USAGE without grant option), which would have 500d the login path. Fixed at e78fc3f v2.79.26 by reading liveness through the postgres-owned view vAuthUserIdentity at all 7 SQL sites, plus migrations 129+130. Equivalence measured on prod: old predicate 102 rows, new 102, authEmail differs on 0 of 800. Cutover retry NOT authorised - awaiting Elazar.
-
PASS:e78fc3f deep from audit-mars-ca, no findings. Live 2.79.26 / dpl_DvdDCwVWZRR3Mb51c54oLfLwZbKz, db=1 true. Stated coverage boundary: a real Google callback and identity rebind were not exercised. Doc record pushed at 5fc52a0. Cutover stays blocked on fn_admin_invariant_ok (invoker-rights, reads auth.users + auth.identities, reached via set_user_roles_atomic and deferred triggers on userRoles / users.deletedAt) - db-mars-cc proposal with audit-mars-ca.
-
2026-09-10 retry stopped before mutation: existing mars_app_runtime plaintext password/DSN is unrecoverable. pm/06 and .env.local contain only postgres; db-mars-cc checked bash history, memory, and scratchpads with no copy. No credential rotation/minting, Vercel env/deploy, or DB mutation performed. Live role preflight passes LOGIN/no elevated attrs/password verifier/statement_timeout=10000 and intended narrow ACL/function boundary. Elazar decision required on credential provision.
-
CREDENTIAL GATE, 2026-09-10. The mars_app_runtime password EXISTS (verifier present in Postgres, db-mars-ca preflight) but NO PLAINTEXT SURVIVES ANYWHERE. Exhausted, each measured: - Vercel Production: DATABASE_URL (Encrypted, updated 5d ago = the rollback itself) and DATABASE_URL_DIRECT (80d). Both are the postgres role. - Vercel Development + Preview: DATABASE_URL in both decrypts to user postgres.ustenjufophwhlkzfdso. Not the runtime role. (Pulled to scratchpad, username-only inspection, files deleted.) - pm/06-passwords-and-settings.md and .env.local: postgres only. - Supabase Vault: SELECT name FROM vault.secrets returns exactly one row, cron_secret. decrypted_secrets not queried and no value exposed. - db-mars-cc: no copy in memory, history or scratchpads. - coder02-mars-cc: 0 hits across memory dir, agent tree, scratchpad and shell history. ROOT CAUSE OF THE GAP, found by coder02-mars-cc: migration 127 (db/migrations/applied/127-2026-09-05-mars-app-runtime-role.sql, commit df37d98, Local-Author db-mars-cc) creates the role as CREATE ROLE mars_app_runtime LOGIN; with NO password clause, and none of 128-136 set one. The verifier therefore came from an out-of-band ALTER ROLE ... PASSWORD typed interactively during the cutover and never committed to any tracked artifact. There was never a written-down source to lose - this is not a misplaced secret, it is a secret that was never persisted. CONSEQUENCE: the only route to a usable DSN is ALTER ROLE mars_app_runtime PASSWORD, which is a ROTATION. Under the fleet No-Rotation rule no agent may do that on its own initiative, including on a role that is not yet in service and where the blast radius today is zero. Put to Elazar as a yes/no. Nothing was set, reset or minted by any lane; no Vercel env or deploy change was made; no DB mutation. PROCESS FIX for whoever resumes this, so it cannot recur: the password must be set BY a tracked migration or, if it must stay out of git, minted once and stored in Supabase Vault in the same operation - the vault path is proven, it is what recovered Pluto's CRON_SECRET this week without a rotation. An interactively-typed credential with no persistence step is the defect, independent of this incident. MY ERROR, corrected on the record: I told coder02-mars-cc it authored the statement_timeout work on this role. It did not - it declined that as DDL belonging to the db lane and wrote the spec only (MARS-696), a boundary call I agreed with at the time. It has never held a connection as mars_app_runtime.
-
Authorized credential gate complete: mars_app_runtime password set; tenant-qualified :6543 DSN stored in Supabase Vault as mars_app_runtime_dsn; direct Supavisor login and real vAuthUserIdentity SELECT passed (current_user=mars_app_runtime, statement_timeout=10s). Credential distributed by one explicit multi-recipient DM to coder-mars-cc, db-mars-cc, coder02-mars-cc, and audit-mars-ca. No Vercel environment change or deployment performed. Live fn_admin_invariant_ok state verified closed: postgres-owned SECURITY DEFINER, search_path='', runtime EXECUTE.
-
Production cutover to mars_app_runtime is LIVE. Vercel production DATABASE_URL repointed to the tenant-qualified username mars_app_runtime.<project-ref> at the :6543 transaction pooler; deployed d064f5b41db290596f2a9466a174e6091c89e936, v2.79.28, dpl_762Wkk9XobXNAgbrPXZxsxCf84SC READY. Acceptance under the new role: /api/health?db=1 -> dbOk:true; GET /api/internal/support-action?ticketRef=SOP-MARS-1 -> full ticket read; POST op=note on SOP-MARS-1 -> ok:true messageId 76ce2edd-8f09-4d83-909f-dc8f35588fa6, exercising reply_support_ticket_atomic plus the archive-context triggers. audit-mars-ca: PASS:d064f5b deep, no findings. db-mars-ca closed MARS-696 (d) and (e): error-digest 6.570ms, rectificar-reminder 132.180ms, and pg_sleep(15) under the runtime DSN cancels with 57014 near 10s, so the limit is confirmed in force; DATABASE_URL_DIRECT metadata is unchanged since 2026-06-22 and weekly-kpi stays isolated on its own session-mode pool. OUTSTANDING: one completed Google login, which exercises the seven auth.users -> vAuthUserIdentity rewrites and the last-admin guard. An authenticated browser is not reachable from venus; asked Elazar 2026-09-10 ~08:05 ART. The unverifiable-state record stays NOT CONFIRMED SAVED until that lands. The DSN value is in Supabase Vault as mars_app_runtime_dsn and in Vercel only - in no file, commit, log or WI. Rollback is the postgres transaction-pooler DSN in pm/06 via vercel env add --force plus a push to redeploy; the abort instrument is dbOk:false, NOT a breaker row, since a bad DSN fails at connect and appEvents shares the pool.
-
Post-cutover privilege chain closed, and it broke LOGIN in production before it was found. canonicalizeEmail(text) had no EXECUTE for mars_app_runtime and is called unguarded in app SQL on the OAuth callback path (src/app/callback/route.ts:250) plus identity-resolver 103/114/126/133 - the one acceptance step nobody could exercise from venus, and the only path none of health/ticket-read/ticket-write touches. db-mars-ca migration 137 granted it plus drain_email_outbox_if_due; published 5c552576, v2.79.31. The wake then failed one call deeper on cron_auth_header, which must STAY revoked (it returns the CRON_SECRET bearer; MARS-380 and migration 128 revoked it from client-reachable roles deliberately). Fixed correctly by migration 138: wake_email_drain_on_enqueue is now postgres-owned SECURITY DEFINER with search_path pinned to pg_catalog,pg_temp, so the trigger reaches drain and the vault helper as postgres while the runtime holds direct EXECUTE on neither. Published 169108cf, v2.79.32; verified live in pg_proc. Dropping public from the DEFINER search_path is audit-mars-ca's f_unaccent finding applied here. TWO DURABLE LESSONS. (1) Postgres does NOT check EXECUTE on a trigger function when the trigger fires, which is why the 12-table live sweep passed - the exposure is a trigger function CALLING an ordinary function, and the join over pg_proc.prosrc against every ungranted non-trigger function yields the hits directly. Every public table, view and sequence the app touches was already granted; functions were the whole gap. (2) Migration 071 justified SECURITY INVOKER in its own comment with 'the trigger fires as the app-pool role that does the emailOutbox INSERT = postgres' - a premise the cutover falsified. Any security mode argued from 'the app pool is postgres' is in the same class and needs a grep of migration COMMENTS, not just the catalog.
-
completed
-
CLOSING PREMISE CHECK, db-mars-cc 2026-09-11: this row's title still asks to diagnose a failure that WAS diagnosed and whose retry is LIVE. The cause was never the role itself - it was missing EXECUTE grants on ordinary functions the app calls unguarded (canonicalizeEmail on the OAuth callback path, drain_email_outbox_if_due from the wake trigger), closed by migrations 137/138 and published at 5c55257/169108c. The cutover is in production now, and I re-measured it today rather than reading this row: fired /api/health?db=1 and matched the resulting backend in pg_stat_activity by state_change within the second - the ONLY Supavisor connection that moved is usename=mars_app_runtime, and no postgres-role connection moved at all. The absent alternative is the stronger half of that proof. statement_timeout=10000 is on the role's rolconfig, so the limit MARS-680/689 depend on is in force. Both process errors recorded in the description were corrected in the retry: the role was exercised on a direct connection first, and acceptance went through a NEW deployment plus a real support-ticket write that exercised reply_support_ticket_atomic and the archive-context triggers. RESIDUAL, named rather than faked: whether the weekly-kpi DATABASE_URL_DIRECT pool shares mars_app_runtime and inherits the 10s limit is NOT answerable from the DB side while that pool is closed - it needs a one-line env-var read by the push lane. That is a MARS-696 item, not a reason to hold this row open.