db push over Session Pooler: EOF after GRANT TO current_user while hosted PG reports recovery mode

[RRValhalla]

Affected area

Migrations / Database

Supabase CLI version

2.109.1

Operating system

macOS 26.5.2 (arm64)

Installation method

Homebrew

Summary

supabase db push against an empty hosted PostgreSQL 17 project through the IPv4 Session Pooler authenticates successfully and starts the first migration, but the connection ends with unexpected EOF.

The CLI reports CREATE EXTENSION as the failing statement. Hosted PostgreSQL logs show that execution actually continued through schema and role DDL and reached a role-membership GRANT ... TO current_user WITH SET TRUE, INHERIT TRUE; immediately afterward, two connections received FATAL: the database system is in recovery mode.

The transaction rolled back completely. The project returned to ACTIVE_HEALTHY, and pg_is_in_recovery() returned false.

This appears related to #4687, but this report adds current CLI/PostgreSQL versions, exact server-side statement ordering, recovery-mode evidence, and the misleading CLI statement attribution.

Environment

• Hosted Supabase project
• Region: ap-southeast-1
• PostgreSQL: hosted 17.6
• Project state before the command: healthy and empty of application migrations, schemas, tables, policies, and custom roles
• Connection: shared Session Pooler / Supavisor, port 5432
• Direct database endpoint was not used because the current network path could not reach the IPv6-only endpoint
• Database password was supplied only through SUPABASE_DB_PASSWORD; no credential is included here

Command

export SUPABASE_DB_PASSWORD='<redacted>'
POOLER_URL='postgresql://postgres.<redacted>@aws-0-ap-southeast-1.pooler.supabase.com:5432/postgres'
supabase db push --db-url "$POOLER_URL" --include-all

A preceding dry-run connected successfully and listed the expected migration files:

supabase db push --db-url "$POOLER_URL" --include-all --dry-run

Actual output

Connecting to remote database...
Do you want to push these migrations to the remote database? [Y/n] y
Applying migration <timestamp>_identity_access_foundation.sql...
NOTICE (42P06): schema "extensions" already exists, skipping
NOTICE (42710): extension "pgcrypto" already exists, skipping
unexpected EOF
At statement: 1
create extension if not exists pgcrypto with schema extensions

The command exited with status 1.

Relevant migration shape

The migration is executed transactionally and includes standard PostgreSQL 17 role ownership preparation:

create schema if not exists extensions;
create extension if not exists pgcrypto with schema extensions;

-- create several NOSUPERUSER / NOLOGIN owner roles if absent
-- normalize their non-superuser attributes

grant app_schema_owner, app_acl_owner, app_auth_owner
  to current_user with set true, inherit true;

-- create application objects and transfer ownership
-- revoke the temporary memberships before commit

Hosted PostgreSQL log ordering

Sanitized PostgreSQL logs for the failed CLI attempt show:

execute: create schema if not exists extensions
execute: create extension if not exists pgcrypto with schema extensions
execute: create application schemas
execute: create/alter the non-superuser roles
execute: grant app_schema_owner, app_acl_owner, app_auth_owner
         to current_user with set true, inherit true
FATAL: the database system is in recovery mode
FATAL: the database system is in recovery mode

Therefore the CLI's At statement: 1 location does not match the last server-side statement reached.

Rollback and health evidence

Immediately after the error:

migration history rows: 0
application schemas:     0
application relations:   0
application policies:    0
application roles:       0
project status:           ACTIVE_HEALTHY
pg_is_in_recovery():      false

No later migration started.

Two earlier attempts through the Supabase Management/MCP migration channel also returned Connection terminated unexpectedly around the same role-membership stage and rolled back completely.

Local control

The same full migration chain passes on PostgreSQL 17.10 when run with psql --single-transaction as a LOGIN role with:

NOSUPERUSER + CREATEROLE + BYPASSRLS

The local verification completes all migrations, transfers object ownership, revokes temporary memberships, and leaves no membership or schema-CREATE residue. This suggests the SQL and PostgreSQL 17 privilege model are valid outside the hosted connection path.

Expected behavior

One of the following:

1. The migration completes through the Session Pooler; or
2. The server returns a precise PostgreSQL error explaining that this role-membership operation is unsupported; and
3. The CLI reports the actual failing/last-reached statement rather than attributing the EOF to an earlier CREATE EXTENSION.

A normal role membership change should not coincide with the hosted database entering recovery mode.

Questions

• Is GRANT <owner roles> TO current_user WITH SET TRUE, INHERIT TRUE unsupported through the shared Session Pooler for hosted PostgreSQL 17?
• What is the supported migration path for a hosted non-superuser postgres role that must transfer object ownership and therefore needs SET ROLE capability?
• Can the CLI preserve/report the last server-side statement reached when the connection ends with EOF?

[7ttp]

I think this has already been fixed in:

• fix: segfault on GRANT/REVOKE role with current_user/session_user/public grantee supautils#205

and should be included in the next image....