`gen types --db-url` fails with "Tenant or user not found" on self-hosted environment

[KeppyNaushika]

Describe the bug

When running supabase gen types --db-url against a self-hosted Supabase instance, the command fails with a FATAL: Tenant or user not found (SQLSTATE XX000) error, even though all server-side containers (including supabase-pooler and supabase-meta) are running and healthy.

To Reproduce

1. Set up a self-hosted Supabase instance on a Rocky Linux server using the official Docker Compose setup.
2. Ensure all containers are running and healthy (sudo docker ps).
3. Set the POOLER_TENANT_ID environment variable in the server's .env file (e.g., POOLER_TENANT_ID=domain-supabase).
4. On a client machine, define the database URL in an .env file (e.g., LOCAL_SUPABASE_DB_URL="postgresql://postgres:[PASSWORD]@supabase.domain.jp:6543/postgres").
5. Run the following command (using dotenv-cli or similar to load .env):

   dotenv -e .env -- sh -c 'npx supabase gen types typescript --db-url "$LOCAL_SUPABASE_DB_URL" --schema public --debug'

6. See error in the terminal output.

Expected behavior

The command should connect to the self-hosted database via the specified --db-url and generate TypeScript types based on the specified schema (types/supabasetype.ts should be created/updated without errors).

Screenshots

N/A (Error is text-based in the terminal output below)

System information

• Ticket ID: 8b81ce4d826046ecae9519b8619f9ec1
• Version of OS: Rocky Linux 8.10 (Server) / macOS 15.4.1（24E263） (Client)
• Version of CLI: 2.22.6
• Version of Docker: Docker version 26.1.3, build b72abbb
• Versions of services:

supabase/storage-api:v1.22.7
supabase/realtime:v2.34.47
supabase/studio:2025.04.21-sha-173cc56
kong:2.8.1
postgrest/postgrest:v12.2.11
supabase/edge-runtime:v1.67.4 
supabase/supavisor:2.5.1
supabase/postgres-meta:v0.88.9
supabase/gotrue:v2.171.0
supabase/logflare:1.12.0
supabase/postgres:15.8.1.060
darthsim/imgproxy:v3.8.0
timberio/vector:0.28.1-alpine

Additional context

• Client Environment:
  • Browser: N/A (CLI issue)
  • Version of supabase-js: 2.49.4
  • Version of Node.js: v23.6.0
• Server Details:
  • CPU supports AES-NI: Yes (Confirmed via grep aes /proc/cpuinfo)
  • OpenSSL Version: 1.1.1k FIPS 25 Mar 2021 (or replace with your output of openssl version)
• Troubleshooting Steps Taken:
  • Tried resetting the Supabase environment completely (docker compose down -v).
  • Tried different values for POOLER_TENANT_ID.
  • Tried adding/removing --schema public flag.
  • Tried adding --project-id local (resulted in flag conflict error).
  • Tried running the command with and without dotenv-cli/sh -c.
• The issue seems related to how the CLI/postgres-meta identifies the tenant when connecting via --db-url to a self-hosted Supavisor instance, despite the server containers being healthy.
• This might be related to Selfhosted Supavisor Pooler Keeps Restarting supabase#30210, although the initial ErlangError with Supavisor seems resolved now.
• Full Debug Output:

> gen:types
> dotenv -e .env -- sh -c 'npx supabase gen types typescript --db-url "$LOCAL_SUPABASE_DB_URL" --schema public --debug > types/supabasetype.ts'

Supabase CLI 2.22.6
Connecting to supabase.domain.jp 6543
2025/05/05 21:16:53 PG Send: {"Type":"StartupMessage","ProtocolVersion":196608,"Parameters":{"database":"postgres","user":"postgres"}}
2025/05/05 21:16:53 PG Recv: {"Type":"ErrorResponse","Severity":"FATAL","SeverityUnlocalized":"FATAL","Code":"XX000","Message":"Tenant or user not found","Detail":"","Hint":"","Position":0,"InternalPosition":0,"InternalQuery":"","Where":"","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"","Line":0,"Routine":"","UnknownFields":null}
failed to connect to postgres: failed to connect to `host=supabase.domain.jp user=postgres database=postgres`: server error (FATAL: Tenant or user not found (SQLSTATE XX000))