/setup-gbrain: provision picks transaction pooler (6543) but new Supabase projects only listen on session pooler (5432)

[Acantor21]

Summary

gstack-gbrain-supabase-provision pooler-url builds the Postgres URL using db_port from the Management API response, which for new Supabase projects returns the transaction-mode pooler at port 6543 — but newly-provisioned shared poolers actually listen on the session-mode port 5432. Result: gbrain init runs against an unreachable port. Symptoms cascade into ~30 min of confusing rotation/auth diagnosis.

Repro

Fresh Supabase project provisioned via /setup-gbrain (path 2a auto-provision) on 2026-05-02:

1. Run /setup-gbrain → pick "Supabase, auto-provision"
2. Project becomes ACTIVE_HEALTHY ✅
3. gstack-gbrain-supabase-provision pooler-url <ref> returns a URL with port 6543
4. gbrain init fails: Cannot connect to database: (ENOTFOUND) tenant/user postgres.<ref> not found
5. Manual psql "$URL" -c 'SELECT 1;' against port 6543: hangs to TCP timeout
6. Same psql against port 5432 (after manually swapping in config.json): password authentication failed for user "postgres" — error fires fast (different limb), so port is at least reachable
7. Supabase dashboard → Connect → Direct connection-string view shows port: 5432, host: aws-1-us-east-1.pooler.supabase.com for the shared pooler

Root cause

cmd_pooler_url in bin/gstack-gbrain-supabase-provision:

if printf '%s' "$resp" | jq -e 'type == "array"' >/dev/null 2>&1; then
    first_or_session=$(printf '%s' "$resp" | jq '[.[] | select(.pool_mode == "session")][0] // .[0]')
else
    first_or_session="$resp"
fi
db_port=$(printf '%s' "$first_or_session" | jq -r '.db_port // empty')
local url="postgresql://${db_user}:${DB_PASS}@${db_host}:${db_port}/${db_name}"

For new projects the API returns a single object (not array), with pool_mode=transaction and db_port=6543. The fallback to .[0] in the array case has the same problem — first entry happens to be transaction mode. There's no validation that the picked entry's port is actually listening.

I'm not 100% sure whether this is a Supabase API regression (returning a transaction pooler config for a project that doesn't actually have one running) or whether the helper has always been wrong about which pool to prefer. Either way, the user-facing failure mode is the same.

Local workaround (in my copy of the helper)

Added at cmd_pooler_url after parsing fields:

# Verified 2026-05-02 against fresh Supabase projects: the API returns
# transaction-mode (6543) by default for new projects, but the shared
# *session* pooler at 5432 is what actually accepts connections. Trusting
# db_port from the API gives "tenant/user not found" or "password auth
# failed" (TCP connects, auth rejects). Switch.
if [ "$pool_mode" = "transaction" ] && [ "$db_port" = "6543" ]; then
  db_port=5432
  pool_mode="session"
fi

This is a band-aid — a proper fix would either:

1. Query the actual pool configuration to confirm the listener is up before returning the URL
2. Always prefer the session pooler when both modes exist (current code does this for arrays but not single objects)
3. Add a TCP-connectivity probe inside wait before returning ACTIVE_HEALTHY

Other findings while debugging

• wait returns too early. Polling returns success the moment the management API says ACTIVE_HEALTHY, but the pooler's tenant routing can take an additional 30-60 seconds to propagate. First gbrain init got tenant/user postgres.<ref> not found even with the correct port. My setup-gbrain wrapper now sleeps 60s after wait completes. Worth either bumping the wait condition or adding a separate wait-pooler sub-command that does a TCP handshake.
• gstack-gbrain-source-wireup --database-url <url> leaks the URL via ps aux. Forced me to rotate the DB password mid-flow. Should accept URL via env var only (the rest of the codebase enforces this for PAT and DB_PASS — --database-url is the inconsistent surface).

Environment

• gstack: 1.26.0.0
• gbrain: 0.18.2
• Supabase project: us-east-1, Free tier, provisioned via Management API on 2026-05-02
• macOS Darwin 25.3.0

Happy to PR the band-aid fix if useful, or wait for a more principled solution. Either way figured the report is the right starting point.

[diazMelgarejo]

opened both:

• fix(migrations): propagate GBRAIN_DATABASE_URL + GBRAIN_DISABLE_DIRECT_POOL to migration subprocesses (IPv6 band-aid) gbrain#1005
• fix(connection-manager): skip direct URL derivation for Session Pooler (port 6543) — fixes IPv6-only ECONNREFUSED gbrain#1006

[0xDevNinja]

taking a look at this — the band-aid swap to session/5432 is the right shape for new shared-pooler projects. patching cmd_pooler_url so a single transaction-mode response at 6543 maps to 5432 + session, with regression tests.