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

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

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

python3 - <<'EOF'
import os
from urllib.parse import urlparse
u = urlparse(os.environ["SUPABASE_DB_URL"])
print("scheme:", u.scheme)
print("username:", u.username)
print("hostname:", u.hostname)
print("port:", u.port)
print("path(db):", u.path)
print("password_present:", bool(u.password))
EOF
which psql; psql --version 2>/dev/null || echo "no psql"

scheme: postgresql
username: postgres.<ref>
hostname: aws-0-us-east-1.pooler.supabase.com
port: 5432
path(db): /postgres
password_present: True
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

cd "$(dirname "$(mktemp)")" 2>/dev/null; nc -zv -w 5 aws-0-us-east-1.pooler.supabase.com 5432 2>&1 | tail -1
nc -zv -w 5 aws-0-us-east-1.pooler.supabase.com 6543 2>&1 | tail -1

Connection to aws-0-us-east-1.pooler.supabase.com (44.208.221.186) 5432 port [tcp/postgresql] succeeded!
Connection to aws-0-us-east-1.pooler.supabase.com (52.45.94.125) 6543 port [tcp/*] succeeded!

python3 - <<'EOF'
import os
from urllib.parse import urlparse, urlunparse, quote

u = urlparse(os.environ["SUPABASE_DB_URL"])
# Rebuild netloc pointing at transaction-mode pooler port 6543
pw = quote(os.environ["SUPABASE_DB_PASSWORD"], safe="")
netloc = f"{u.username}:{pw}@{u.hostname}:6543"
txn_url = urlunparse((u.scheme, netloc, u.path, "", "", ""))
# print redacted form for logging only
print("txn host:port ->", u.hostname, "6543")
EOF
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "host=aws-0-us-east-1.pooler.supabase.com port=6543 dbname=postgres user=postgres.<ref> sslmode=require" -tAc "select pg_backend_pid();" 2>&1

txn host:port -> aws-0-us-east-1.pooler.supabase.com 6543
204434

cat > pool_smoke.sh <<'SCRIPT'
#!/usr/bin/env bash
#
# pool_smoke.sh
#
# Proves the correct connection-pooling setup for our serverless workers:
#   * connects through Supabase's TRANSACTION-MODE connection pooler
#     (Supavisor, port 6543) -- NOT the session-mode port (5432) and
#     NOT a direct database connection.
#   * fires 20 concurrent one-shot queries, waits for all of them, and
#     prints "POOL OK 20/20" (and exits 0) only if every query succeeded.
#   * takes all credentials from the environment -- the DB password is
#     never hardcoded and is never passed on an argv/command line.
#
set -euo pipefail

CONCURRENCY=20
TXN_POOLER_PORT=6543          # Supavisor transaction-mode port
SESSION_POOLER_PORT=5432      # session-mode port -- must NOT be used

# --- Required environment ---------------------------------------------------
: "${SUPABASE_DB_URL:?SUPABASE_DB_URL is required}"
: "${SUPABASE_DB_PASSWORD:?SUPABASE_DB_PASSWORD is required}"

# --- Derive user/host/db from the (pooler) connection string ----------------
# We parse the pieces out of SUPABASE_DB_URL but deliberately override the
# port to the transaction-mode pooler. We never read the password from the
# URL; it comes only from SUPABASE_DB_PASSWORD via PGPASSWORD.
read -r DB_USER DB_HOST DB_NAME < <(python3 - <<'PY'
import os
from urllib.parse import urlparse, unquote
u = urlparse(os.environ["SUPABASE_DB_URL"])
user = unquote(u.username or "")
host = u.hostname or ""
db   = (u.path or "/postgres").lstrip("/") or "postgres"
print(user, host, db)
PY
)

# --- Sanity: this must be a pooler host, not a direct db connection ---------
case "$DB_HOST" in
  *pooler.supabase.com) : ;;   # good: Supavisor pooler endpoint
  db.*.supabase.co|*.supabase.co)
    echo "ERROR: host '$DB_HOST' looks like a DIRECT database connection, not the pooler." >&2
    exit 1 ;;
  *)
    echo "ERROR: host '$DB_HOST' is not a recognised Supabase pooler host." >&2
    exit 1 ;;
esac

# The transaction-mode pooler uses the "postgres.<ref>" tenant username.
if [[ "$DB_USER" != *.* ]]; then
  echo "ERROR: user '$DB_USER' is not a pooler tenant user (expected postgres.<project-ref>)." >&2
  exit 1
fi

# libpq keyword/value connection string. Password is NOT included here;
# it is supplied out-of-band through PGPASSWORD so it never hits argv.
CONNINFO="host=${DB_HOST} port=${TXN_POOLER_PORT} dbname=${DB_NAME} user=${DB_USER} sslmode=require"

export PGPASSWORD="$SUPABASE_DB_PASSWORD"
# Belt-and-suspenders: prevent any accidental session-mode fallback.
unset PGPORT PGHOST PGUSER PGDATABASE PGSERVICE

echo "Connecting via TRANSACTION-MODE pooler: ${DB_HOST}:${TXN_POOLER_PORT} (db=${DB_NAME}, user=${DB_USER})"

# --- Fire N concurrent one-shot queries -------------------------------------
tmpdir="$(mktemp -d)"
trap 'rm -rf "$tmpdir"' EXIT

pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  (
    # Each query is a fresh, independent connection -> exercises the pool.
    # -X: ignore .psqlrc  -q: quiet  -t -A: bare value  -v ON_ERROR_STOP=1
    if psql -X -q -tA -v ON_ERROR_STOP=1 "$CONNINFO" \
         -c "SELECT pg_backend_pid();" > "$tmpdir/out.$i" 2> "$tmpdir/err.$i"; then
      echo ok > "$tmpdir/status.$i"
    else
      echo fail > "$tmpdir/status.$i"
    fi
  ) &
  pids+=("$!")
done

# Wait for ALL background queries to finish.
for pid in "${pids[@]}"; do
  wait "$pid" || true
done

# --- Tally results ----------------------------------------------------------
success=0
for i in $(seq 1 "$CONCURRENCY"); do
  if [[ "$(cat "$tmpdir/status.$i" 2>/dev/null)" == "ok" ]]; then
    success=$((success + 1))
  else
    echo "query #$i failed: $(tr -d '\n' < "$tmpdir/err.$i" 2>/dev/null)" >&2
  fi
done

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

written
-rwxr-xr-x 1 root root 3836 Jul 26 02:30 pool_smoke.sh

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

Connecting via TRANSACTION-MODE pooler: aws-0-us-east-1.pooler.supabase.com:6543 (db=postgres, user=postgres.<ref>)
POOL OK 20/20
EXIT=0

# 1. Confirm password is not hardcoded anywhere in the script
if grep -Fq "$SUPABASE_DB_PASSWORD" pool_smoke.sh; then echo "FAIL: password hardcoded"; else echo "OK: password not in script"; fi

# 2. Confirm the script targets port 6543 and rejects 5432/direct
grep -n "TXN_POOLER_PORT=6543" pool_smoke.sh >/dev/null && echo "OK: uses 6543 (transaction mode)"

# 3. Prove the direct-host guard trips
echo "--- guard test (direct host) ---"
SUPABASE_DB_URL="postgresql://postgres:pw@db.<ref>.supabase.co:5432/postgres" \
SUPABASE_DB_PASSWORD="x" ./pool_smoke.sh; echo "EXIT=$?"

OK: password not in script
OK: uses 6543 (transaction mode)
--- guard test (direct host) ---
EXIT=1

ERROR: host 'db.<ref>.supabase.co' looks like a DIRECT database connection, not the pooler.

# Watch process args while the script runs; grep for the password in any argv
( for _ in $(seq 1 30); do ps -eo args 2>/dev/null; sleep 0.05; done > /tmp/ps_snap.txt ) &
WATCH=$!
./pool_smoke.sh >/dev/null
wait $WATCH
if grep -Fq "$SUPABASE_DB_PASSWORD" /tmp/ps_snap.txt; then echo "FAIL: password seen in argv"; else echo "OK: password never appeared in any process argv"; fi
rm -f /tmp/ps_snap.txt

OK: password never appeared in any process argv