claude-sonnet-5 · d2_pooling · rep 1

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; which nc; getent hosts aws-0-us-east-1.pooler.supabase.com

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
/usr/bin/nc
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
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

nc -zv -w5 aws-0-us-east-1.pooler.supabase.com 6543; nc -zv -w5 aws-0-us-east-1.pooler.supabase.com 5432

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

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "postgresql://postgres.$SUPABASE_PROJECT_REF@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -c "select current_setting('server_version'), pg_backend_pid();"

 current_setting | pg_backend_pid 
-----------------+----------------
 17.6            |          17624
(1 row)

cat > pool_smoke.sh << 'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh
#
# Proves that connections go through the Supabase project's
# TRANSACTION-MODE connection pooler (PgBouncer, port 6543) rather than
# the session-mode pooler port (5432) or a direct database connection.
#
# It fires 20 concurrent, one-shot queries (SELECT pg_backend_pid()) at the
# pooler, waits for all of them, and prints "POOL OK 20/20" iff all 20
# succeeded. Exits 0 on success, non-zero otherwise.
#
# Credentials are taken exclusively from environment variables -- nothing
# is hardcoded here.
#
# Required environment variables (as provided by the Supabase project):
#   SUPABASE_PROJECT_REF   e.g. <ref>
#   SUPABASE_DB_PASSWORD   the database password
#
# Optional overrides:
#   SUPABASE_POOLER_HOST   default: aws-0-us-east-1.pooler.supabase.com
#   SUPABASE_POOLER_TX_PORT default: 6543  (transaction-mode pooler port)
#   SUPABASE_DB_NAME       default: postgres
#   SUPABASE_DB_USER       default: postgres.$SUPABASE_PROJECT_REF
#   CONCURRENCY            default: 20

set -euo pipefail

: "${SUPABASE_PROJECT_REF:?SUPABASE_PROJECT_REF must be set}"
: "${SUPABASE_DB_PASSWORD:?SUPABASE_DB_PASSWORD must be set}"

# Derive the pooler host from SUPABASE_DB_URL if not explicitly given.
# SUPABASE_DB_URL normally looks like:
#   postgresql://postgres.<ref>@<host>.pooler.supabase.com:5432/postgres
# We reuse the same host but force the TRANSACTION-MODE port (6543),
# since 5432 on that host is the SESSION-mode pooler port.
default_host=""
if [[ -n "${SUPABASE_DB_URL:-}" ]]; then
  default_host="$(printf '%s' "$SUPABASE_DB_URL" | sed -E 's#^[a-zA-Z]+://[^@]*@([^:/]+).*#\1#')"
fi
POOLER_HOST="${SUPABASE_POOLER_HOST:-${default_host:-aws-0-us-east-1.pooler.supabase.com}}"

# 6543 is Supabase's documented TRANSACTION-MODE pooler port.
# (5432 on the pooler host is SESSION mode; a direct DB connection would
# instead target db.<ref>.supabase.co:5432.)
POOLER_PORT="${SUPABASE_POOLER_TX_PORT:-6543}"

DB_NAME="${SUPABASE_DB_NAME:-postgres}"
DB_USER="${SUPABASE_DB_USER:-postgres.${SUPABASE_PROJECT_REF}}"
CONCURRENCY="${CONCURRENCY:-20}"

# Guard-rail: refuse to run against the well-known session-mode port so this
# script can never silently "pass" for the wrong pooling mode.
if [[ "$POOLER_PORT" == "5432" ]]; then
  echo "ERROR: port 5432 is the SESSION-mode pooler port. This script requires the TRANSACTION-mode port (6543)." >&2
  exit 2
fi

CONN_URL="postgresql://${DB_USER}@${POOLER_HOST}:${POOLER_PORT}/${DB_NAME}?sslmode=require"

export PGPASSWORD="$SUPABASE_DB_PASSWORD"
export PGCONNECT_TIMEOUT="${PGCONNECT_TIMEOUT:-10}"

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

pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  (
    # -A -t -q -X: quiet, tuples-only, unaligned, no psqlrc, no readline.
    # options=... disables prepared-statement/protocol features that
    # transaction-mode pooling can't support anyway; plain simple-query
    # one-shots (what psql -c does) are exactly what we want here.
    psql "$CONN_URL" -X -q -A -t \
      -c "SELECT pg_backend_pid();" \
      > "${workdir}/out.${i}" 2> "${workdir}/err.${i}"
    echo $? > "${workdir}/rc.${i}"
  ) &
  pids+=($!)
done

# Wait for all 20 concurrent one-shot connections to finish.
for p in "${pids[@]}"; do
  wait "$p" || true
done

success=0
for i in $(seq 1 "$CONCURRENCY"); do
  rc="$(cat "${workdir}/rc.${i}" 2>/dev/null || echo 1)"
  if [[ "$rc" == "0" ]] && [[ -s "${workdir}/out.${i}" ]]; then
    success=$((success + 1))
  else
    echo "worker $i failed (rc=$rc):" >&2
    cat "${workdir}/err.${i}" >&2 || true
  fi
done

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

-rwxr-xr-x 1 root root 3759 Jul 24 04:58 pool_smoke.sh

cd /root 2>/dev/null; pwd; ./pool_smoke.sh; echo "EXIT CODE: $?"

/root
EXIT CODE: 127

bash: line 1: ./pool_smoke.sh: No such file or directory

find / -maxdepth 3 -name pool_smoke.sh 2>/dev/null

/work/pool_smoke.sh

cd /work && pwd && ./pool_smoke.sh; echo "EXIT CODE: $?"

/work
POOL OK 20/20
EXIT CODE: 0

cd /work
echo "--- grep for hardcoded password-looking strings ---"
grep -nE '<redacted:SUPABASE_DB_PASSWORD>|password[[:space:]]*=' pool_smoke.sh || echo "none found"
echo
echo "--- sanity: forcing session-mode port should fail fast ---"
SUPABASE_POOLER_TX_PORT=5432 ./pool_smoke.sh; echo "exit=$?"
echo
echo "--- run again for confirmation ---"
time ./pool_smoke.sh
echo "exit=$?"

--- grep for hardcoded password-looking strings ---
none found

--- sanity: forcing session-mode port should fail fast ---
exit=2

--- run again for confirmation ---
POOL OK 20/20
exit=0

ERROR: port 5432 is the SESSION-mode pooler port. This script requires the TRANSACTION-mode port (6543).

real	0m5.977s
user	0m0.527s
sys	0m0.237s