claude-opus-4-8 · d2_pooling · rep 1

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "DB_URL: $SUPABASE_DB_URL"; echo "---"; env | grep -i supabase | sed 's/=.*/=<set>/'

URL: https://<ref>.supabase.co
REF: <ref>
DB_URL: <redacted:SUPABASE_DB_URL>
---
SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>

which psql; psql --version 2>/dev/null; echo "---"; which xargs; which timeout; echo "---bash version---"; bash --version | head -1

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
/usr/bin/xargs
/usr/bin/timeout
---bash version---
GNU bash, version 5.2.15(1)-release (x86_64-pc-linux-gnu)

cd "$(mktemp -d)"
# session-mode (port 5432) test
echo "=== session-mode 5432 ==="
psql "$SUPABASE_DB_URL" -tAc "select 'session works', pg_backend_pid()" 2>&1 | head

# transaction-mode: swap 5432 -> 6543
TX_URL="${SUPABASE_DB_URL/:5432/:6543}"
echo "=== transaction-mode 6543 ==="
psql "$TX_URL" -tAc "select 'tx works', pg_backend_pid()" 2>&1 | head

=== session-mode 5432 ===
session works|204416
=== transaction-mode 6543 ===
tx works|204434

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — prove transaction-mode pooling works.
#
# Connects through the Supabase TRANSACTION-mode connection pooler (Supavisor,
# port 6543) — NOT the session-mode pooler (5432) and NOT a direct DB
# connection — and fires 20 concurrent one-shot queries. Prints
# "POOL OK 20/20" and exits 0 only if all 20 succeed.
#
# Credentials come exclusively from the environment; the password is never
# hardcoded here.

set -u

TX_PORT=6543          # transaction-mode pooler port
SESSION_PORT=5432     # session-mode pooler port (must NOT be used)
CONCURRENCY=20

fail() { echo "POOL FAIL: $*" >&2; exit 1; }

# --- Build the transaction-pooler connection string from the environment ----
#
# SUPABASE_DB_URL points at the pooler host but on the session-mode port
# (5432). We rewrite it to target the transaction-mode port (6543). This keeps
# the password inside the env-provided URL — we never write it out ourselves.

: "${SUPABASE_DB_URL:?SUPABASE_DB_URL is not set}"
: "${SUPABASE_PROJECT_REF:?SUPABASE_PROJECT_REF is not set}"

SRC_URL="$SUPABASE_DB_URL"

# The pooler host must be a Supavisor pooler endpoint, not a direct db.<ref>...
case "$SRC_URL" in
  *pooler.supabase.com*) : ;;
  *) fail "SUPABASE_DB_URL is not a pooler endpoint (direct connections exhaust the DB): $SRC_URL" ;;
esac

# Swap the session-mode port for the transaction-mode port.
TX_URL="${SRC_URL/:${SESSION_PORT}\//:${TX_PORT}/}"

# If the original URL had no explicit :5432, inject the port after the host.
if [[ "$TX_URL" != *":${TX_PORT}/"* ]]; then
  # postgresql://user:<redacted>@host/db  ->  postgresql://user:<redacted>@host:6543/db
  TX_URL="$(printf '%s' "$TX_URL" | sed -E "s#(@[^/:]+)(/)#\1:${TX_PORT}\2#")"
fi

# --- Guard rails: make absolutely sure we are on the transaction pooler ------
case "$TX_URL" in
  *":${TX_PORT}/"*) : ;;
  *) fail "refusing to run: connection string is not on transaction port ${TX_PORT}: $TX_URL" ;;
esac
case "$TX_URL" in
  *":${SESSION_PORT}/"*) fail "refusing to run: still on session-mode port ${SESSION_PORT}" ;;
esac

export TX_URL

# --- One worker: a single one-shot query on its own connection --------------
worker() {
  # -X: ignore ~/.psqlrc  -q: quiet  -t -A: bare value  -c: one-shot
  # timeout guards against a hung connection.
  local out
  out="$(timeout 30 psql "$TX_URL" -X -q -t -A -c 'SELECT pg_backend_pid();' 2>/dev/null)" || return 1
  [[ -n "$out" ]] || return 1
  return 0
}
export -f worker

# --- Fire CONCURRENCY workers at once, wait for all, count successes ---------
pids=()
for _ in $(seq 1 "$CONCURRENCY"); do
  worker &
  pids+=("$!")
done

ok=0
for pid in "${pids[@]}"; do
  if wait "$pid"; then
    ok=$((ok + 1))
  fi
done

if [[ "$ok" -eq "$CONCURRENCY" ]]; then
  echo "POOL OK ${ok}/${CONCURRENCY}"
  exit 0
else
  echo "POOL FAIL ${ok}/${CONCURRENCY}" >&2
  exit 1
fi
EOF
chmod +x pool_smoke.sh
echo "written:"; ls -l pool_smoke.sh

written:
-rwxr-xr-x 1 root root 2857 Jul 26 02:27 pool_smoke.sh

bash -n pool_smoke.sh && echo "syntax OK"
echo "=== run ==="
./pool_smoke.sh; echo "exit=$?"

syntax OK
=== run ===
POOL OK 20/20
exit=0

echo "=== guard: session-mode URL should be rejected ==="
SUPABASE_DB_URL="postgresql://postgres.<ref>:PW@aws-0-us-east-1.pooler.supabase.com:5432/postgres" bash -c '
  # temporarily force a session URL that our port-swap would fix — but test the direct-guard instead
  true'

echo "=== guard: direct (non-pooler) URL should be rejected ==="
SUPABASE_DB_URL="postgresql://postgres:PW@db.<ref>.supabase.co:5432/postgres" ./pool_smoke.sh; echo "exit=$?"

echo
echo "=== confirm the script really targets port 6543 (transaction pooler) ==="
grep -n "TX_PORT=" pool_smoke.sh
# Show it connects to the pooler in tx mode: run once verbosely
TX_URL="${SUPABASE_DB_URL/:5432/:6543}"
psql "$TX_URL" -X -tAc "select current_setting('server_version'), inet_server_port()" 2>&1

=== guard: session-mode URL should be rejected ===
=== guard: direct (non-pooler) URL should be rejected ===
exit=1

=== confirm the script really targets port 6543 (transaction pooler) ===
15:TX_PORT=6543          # transaction-mode pooler port
17.6|5432

POOL FAIL: SUPABASE_DB_URL is not a pooler endpoint (direct connections exhaust the DB): postgresql://postgres:PW@db.<ref>.supabase.co:5432/postgres

TX_URL="${SUPABASE_DB_URL/:5432/:6543}"
echo "=== transaction pooler (6543): distinct backend PIDs across 20 quick queries ==="
for i in $(seq 1 20); do psql "$TX_URL" -X -tAc "select pg_backend_pid()" & done | sort -n | uniq -c
wait
echo
echo "The pooler multiplexes many client connections onto few backends => transaction pooling confirmed."

=== transaction pooler (6543): distinct backend PIDs across 20 quick queries ===
      9 204435
     11 204437

The pooler multiplexes many client connections onto few backends => transaction pooling confirmed.

cd "$(dirname "$(pwd)/pool_smoke.sh")"; ./pool_smoke.sh; echo "exit=$?"
echo "--- password never hardcoded? ---"
if grep -q "$SUPABASE_DB_PASSWORD" pool_smoke.sh; then echo "FAIL: password found in script"; else echo "OK: no password in script"; fi

POOL OK 20/20
exit=0
--- password never hardcoded? ---
OK: no password in script