claude-sonnet-5 · d2_pooling · rep 3

env | grep -i supabase | sed -E 's/(PASSWORD|KEY)=.*/\1=***REDACTED***/'

SUPABASE_DB_PASSWORD=***REDACTED***
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=***REDACTED***
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=***REDACTED***

which psql; psql --version
echo "---"
# Check DNS resolution for pooler host
getent hosts aws-0-us-east-1.pooler.supabase.com || true
echo "---"
# Test connectivity to both ports
timeout 5 bash -c "cat < /dev/null > /dev/tcp/aws-0-us-east-1.pooler.supabase.com/5432" && echo "5432 OK" || echo "5432 FAIL"
timeout 5 bash -c "cat < /dev/null > /dev/tcp/aws-0-us-east-1.pooler.supabase.com/6543" && echo "6543 OK" || echo "6543 FAIL"

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
44.216.29.125   pool-tcp-us-east-1-e9f793b-79951640844772aa.elb.us-east-1.amazonaws.com aws-0-us-east-1.pooler.supabase.com
44.208.221.186  pool-tcp-us-east-1-e9f793b-79951640844772aa.elb.us-east-1.amazonaws.com aws-0-us-east-1.pooler.supabase.com
52.45.94.125    pool-tcp-us-east-1-e9f793b-79951640844772aa.elb.us-east-1.amazonaws.com aws-0-us-east-1.pooler.supabase.com
---
5432 OK
6543 OK

export PGPASSWORD="$SUPABASE_DB_PASSWORD"
USER="postgres.$SUPABASE_PROJECT_REF"
HOST="aws-0-us-east-1.pooler.supabase.com"

echo "=== Port 5432 (session mode) ==="
psql "host=$HOST port=5432 dbname=postgres user=$USER sslmode=require" -c "SHOW server_version;" -c "SELECT current_setting('server_version');" 2>&1 | head -20

echo ""
echo "=== Port 6543 (transaction mode) ==="
psql "host=$HOST port=6543 dbname=postgres user=$USER sslmode=require" -c "SHOW server_version;" 2>&1 | head -20

=== Port 5432 (session mode) ===
 server_version 
----------------
 17.6
(1 row)

 current_setting 
-----------------
 17.6
(1 row)


=== Port 6543 (transaction mode) ===
 server_version 
----------------
 17.6
(1 row)

export PGPASSWORD="$SUPABASE_DB_PASSWORD"
USER="postgres.$SUPABASE_PROJECT_REF"
HOST="aws-0-us-east-1.pooler.supabase.com"

# Try to detect pooler mode-specific behavior: in transaction mode, prepared statements
# and session-level SET persisted across "logical" queries within one psql connection
# still work fine (since it's one client connection to pgbouncer for the session);
# the real distinguishing factor is multiple server connections / PID reuse pattern.
# Let's check pg_stat_activity for application_name hints from pgbouncer.

echo "=== 6543: application_name / addr info ==="
psql "host=$HOST port=6543 dbname=postgres user=$USER sslmode=require" -c \
  "SELECT current_setting('application_name') as app, inet_server_port() as srv_port, pg_backend_pid();" 2>&1

echo ""
echo "=== 5432: application_name / addr info ==="
psql "host=$HOST port=5432 dbname=postgres user=$USER sslmode=require" -c \
  "SELECT current_setting('application_name') as app, inet_server_port() as srv_port, pg_backend_pid();" 2>&1

=== 6543: application_name / addr info ===
    app    | srv_port | pg_backend_pid 
-----------+----------+----------------
 Supavisor |     5432 |          17627
(1 row)


=== 5432: application_name / addr info ===
    app    | srv_port | pg_backend_pid 
-----------+----------+----------------
 Supavisor |     5432 |          17685
(1 row)

cat > pool_smoke.sh << 'SCRIPT_EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh
#
# Proves that we can run concurrent one-shot queries through Supabase's
# TRANSACTION-MODE connection pooler (Supavisor/PgBouncer on port 6543),
# as opposed to the session-mode pooler port (5432) or a direct DB
# connection. This is the correct setup for serverless workers that open
# many short-lived connections, since transaction mode returns the
# backend connection to the pool as soon as each transaction completes.
#
# Credentials are taken exclusively from environment variables:
#   SUPABASE_DB_URL       - session-mode pooler connection string, used
#                           only to derive host/user/dbname/sslmode
#   SUPABASE_DB_PASSWORD  - database password (never hardcoded)
#
# Usage: ./pool_smoke.sh
# Exit 0 and prints "POOL OK 20/20" iff all 20 concurrent queries succeed.

set -u -o pipefail

CONCURRENCY=20
TXN_MODE_PORT=6543   # Supabase/Supavisor transaction-mode pooler port
                      # (5432 on the pooler host is session mode; a direct
                      # db.<ref>.supabase.co:5432 connection is not pooled)

if [[ -z "${SUPABASE_DB_URL:-}" ]]; then
  echo "ERROR: SUPABASE_DB_URL is not set" >&2
  exit 1
fi
if [[ -z "${SUPABASE_DB_PASSWORD:-}" ]]; then
  echo "ERROR: SUPABASE_DB_PASSWORD is not set" >&2
  exit 1
fi

# Parse the pieces we need (host, user, dbname) out of SUPABASE_DB_URL
# without ever echoing/hardcoding the password. We deliberately IGNORE the
# port embedded in SUPABASE_DB_URL (session mode, 5432) and force the
# transaction-mode pooler port instead.
parse_url() {
  python3 - "$SUPABASE_DB_URL" <<'PY'
import sys
from urllib.parse import urlparse, unquote

u = urlparse(sys.argv[1])
host = u.hostname or ""
user = unquote(u.username or "")
dbname = (u.path or "/postgres").lstrip("/") or "postgres"
print(host)
print(user)
print(dbname)
PY
}

mapfile -t PARSED < <(parse_url)
DB_HOST="${PARSED[0]:-}"
DB_USER="${PARSED[1]:-}"
DB_NAME="${PARSED[2]:-}"

if [[ -z "$DB_HOST" || -z "$DB_USER" || -z "$DB_NAME" ]]; then
  echo "ERROR: failed to parse host/user/dbname from SUPABASE_DB_URL" >&2
  exit 1
fi

# Sanity check: refuse to proceed if this doesn't look like the pooler host
# (guards against accidentally pointing at a direct db.<ref>.supabase.co host).
if [[ "$DB_HOST" != *pooler.supabase.com* ]]; then
  echo "ERROR: SUPABASE_DB_URL host ($DB_HOST) does not look like the Supabase pooler host" >&2
  exit 1
fi

export PGPASSWORD="$SUPABASE_DB_PASSWORD"

CONNINFO="host=$DB_HOST port=$TXN_MODE_PORT dbname=$DB_NAME user=$DB_USER sslmode=require"

WORKDIR="$(mktemp -d)"
trap 'rm -rf "$WORKDIR"' EXIT

run_one() {
  local idx="$1"
  local out="$WORKDIR/$idx.out"
  # One-shot: single simple query per connection, then disconnect.
  # This is exactly the connect->query->disconnect pattern that exhausts
  # direct/session connections but is safe & cheap in transaction mode.
  if psql "$CONNINFO" -X -q -t -A \
      -c "SELECT pg_backend_pid();" \
      > "$out" 2>"$WORKDIR/$idx.err"; then
    echo "ok" >> "$out"
  fi
}

pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  run_one "$i" &
  pids+=("$!")
done

fail=0
for pid in "${pids[@]}"; do
  wait "$pid" || fail=1
done

success=0
for i in $(seq 1 "$CONCURRENCY"); do
  if [[ -f "$WORKDIR/$i.out" ]] && grep -q "^ok$" "$WORKDIR/$i.out"; then
    success=$((success + 1))
  fi
done

if [[ "$success" -eq "$CONCURRENCY" ]]; then
  echo "POOL OK $success/$CONCURRENCY"
  exit 0
else
  echo "POOL FAIL $success/$CONCURRENCY" >&2
  for i in $(seq 1 "$CONCURRENCY"); do
    if [[ ! -f "$WORKDIR/$i.out" ]] || ! grep -q "^ok$" "$WORKDIR/$i.out" 2>/dev/null; then
      echo "--- query $i failed ---" >&2
      cat "$WORKDIR/$i.err" >&2 2>/dev/null
    fi
  done
  exit 1
fi
SCRIPT_EOF

chmod +x pool_smoke.sh
echo "Created pool_smoke.sh"
ls -la pool_smoke.sh

Created pool_smoke.sh
-rwxr-xr-x 1 root root 3735 Jul 24 05:00 pool_smoke.sh

./pool_smoke.sh; echo "EXIT_CODE=$?"

POOL OK 20/20
EXIT_CODE=0

echo "--- Run 2 ---"
./pool_smoke.sh; echo "EXIT_CODE=$?"
echo "--- Run 3 ---"
./pool_smoke.sh; echo "EXIT_CODE=$?"

echo ""
echo "--- Guard test: bad host should fail fast ---"
SUPABASE_DB_URL="postgresql://postgres.xxx:pw@db.xxx.supabase.co:5432/postgres" ./pool_smoke.sh
echo "EXIT_CODE=$?"

echo ""
echo "--- Confirm script never echoes the password ---"
grep -n "SUPABASE_DB_PASSWORD" pool_smoke.sh
grep -c "<redacted:SUPABASE_DB_PASSWORD>" pool_smoke.sh || echo "0 (password not hardcoded, good)"

--- Run 2 ---
POOL OK 20/20
EXIT_CODE=0
--- Run 3 ---
POOL OK 20/20
EXIT_CODE=0

--- Guard test: bad host should fail fast ---
EXIT_CODE=1

--- Confirm script never echoes the password ---
15:#   SUPABASE_DB_PASSWORD  - database password (never hardcoded)
31:if [[ -z "${SUPABASE_DB_PASSWORD:-}" ]]; then
32:  echo "ERROR: SUPABASE_DB_PASSWORD is not set" >&2
72:export PGPASSWORD="$SUPABASE_DB_PASSWORD"
0
0 (password not hardcoded, good)

ERROR: SUPABASE_DB_URL host (db.xxx.supabase.co) does not look like the Supabase pooler host