db push (passwordless login-role path): migration containing `reset role` fails the ledger INSERT with 42501 and silently no-ops `granted by current_user` cleanup

[MAIAppraiser]

Describe the bug

When supabase db push --linked runs without a database password (the temporary-login-role path, "Initialising login role..."), a migration that contains set role / reset role breaks in two ways:

1. The push fails on the CLI's own ledger bookkeeping. After the migration's reset role, the session is no longer postgres (see analysis below), so the CLI's appended
   INSERT INTO supabase_migrations.schema_migrations(version, name, statements) VALUES($1, $2, $3)
   fails with 42501 permission denied for schema supabase_migrations, and the whole migration rolls back. Every statement in the migration file itself succeeds — the failure is always "At statement: N" where N == the number of top-level statements in the file, i.e. the ledger INSERT.
2. Worse (silent correctness hazard): statements after reset role execute under the temporary login role, not postgres. In our migration, cleanup of the form
   revoke <role> from postgres granted by current_user;
   became a silent no-op (grantor mismatch: the earlier grant was recorded with grantor postgres, but current_user at revoke time is the temp role). If the ledger INSERT had not happened to fail, the migration would have committed with its role-hygiene cleanup quietly missing — no error, no warning surfaced by the CLI.

Additionally, on v2.110.0 (the first release where db push is served by the TypeScript port) the real error is swallowed: the CLI prints only

{"_tag":"Error","error":{"code":"LegacyDbPushApplyError","message":"effect/sql/SqlError: Failed to execute statement\nAt statement: 14\nINSERT INTO supabase_migrations.schema_migrations(version, name, statements) VALUES($1, $2, $3)"}}

with no SQLSTATE/message/detail, and --debug adds nothing. v2.114.0 correctly surfaces ERROR: permission denied for schema supabase_migrations (SQLSTATE 42501), which is how we diagnosed this.

To Reproduce

Against a hosted (linked) project, with no SUPABASE_DB_PASSWORD set and no -p/--db-url (so the CLI mints the temporary login role), push this migration:

-- repro migration: role choreography around a definer function
create role repro_writer nologin;

grant repro_writer to postgres;

set role repro_writer;

create or replace function public.repro_fn() returns int
 language sql security definer as 'select 1';

reset role;

revoke repro_fn_unrelated_priv_or_any_cleanup on schema public from repro_writer; -- any post-reset statement
revoke repro_writer from postgres granted by current_user; -- silent no-op under temp role

Observed:

• supabase db push --linked fails at "statement N" (the ledger INSERT) with 42501 permission denied for schema supabase_migrations (visible on ≥ 2.114.0; swallowed into a generic effect/sql/SqlError on 2.110.0). The migration rolls back.
• Running the exact same push with SUPABASE_DB_PASSWORD set (session is genuinely postgres): the migration applies and registers cleanly, and the granted by current_user revoke actually takes effect.

Two sibling migrations in the same push containing plain DDL (no set role) applied and registered fine, both paths.

Expected behavior

Either of:

• RESET ROLE inside a migration should restore the effective role the CLI intends to run as (postgres), not silently drop the session to the temporary login role. E.g. set the step-down role via connection-time options (options=-c role=postgres) so RESET ROLE restores postgres, or re-assert SET ROLE postgres before the ledger INSERT.
• At minimum, document that under the passwordless path, set role / reset role / granted by current_user inside migrations have different semantics than under a real postgres session — the silent-no-op cleanup case is the dangerous half of this, since it commits without any error.

(And thank you for the v2.114.0 error-reporting rebuild — v2.110.0's swallowed error made this significantly harder to diagnose.)

System information

• OS: macOS (arm64)
• CLI: reproduced on 2.110.0 (error swallowed) and 2.114.0 (real SQLSTATE surfaced); both fail identically on the passwordless path, both succeed with SUPABASE_DB_PASSWORD set
• Hosted project, db push --linked, migration applied via the CLI only (no dashboard SQL)

Analysis

Initialising login role... = the CLI minting a temporary login role via the Management API (POST /v1/projects/{ref}/cli/login-role) and connecting as it (cli_login_postgres), then stepping down to postgres with a session-level SET ROLE (the db diff template's "Step down from login role to postgres" comment is the same mechanism). Because the step-down is a plain SET ROLE statement rather than a connection-time default, a migration's own RESET ROLE restores the session to cli_login_postgres. From that point on, (a) the ledger INSERT runs as a role with no USAGE on supabase_migrations, and (b) role DDL that depends on current_user resolves against the temp role — grantor-mismatched REVOKE ... GRANTED BY current_user no-ops silently.

Possibly related cluster: #5912, #6116, #6094 (role-statement interactions on the hosted path in the same era).