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

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "DB_URL: $SUPABASE_DB_URL"; echo "PW set: ${SUPABASE_DB_PASSWORD:+yes}"

URL: https://<ref>.supabase.co
REF: <ref>
DB_URL: <redacted:SUPABASE_DB_URL>
PW set: yes

which psql; psql --version 2>/dev/null; echo "---"; getent hosts aws-0-us-east-1.pooler.supabase.com || nslookup aws-0-us-east-1.pooler.supabase.com 2>/dev/null | tail -5

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

cd "$(mktemp -d)"
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

204435

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — proves the transaction-mode pooling setup works.
#
# Connects through Supabase's TRANSACTION-mode connection pooler (Supavisor,
# port 6543), fires 20 concurrent one-shot queries, and prints "POOL OK 20/20"
# only if all 20 succeed.
#
# Credentials are taken from the environment. The password is NEVER hardcoded
# and is passed to psql via PGPASSWORD (not on the command line).

set -u
set -o pipefail

# ---- Required environment variables -----------------------------------------
: "${SUPABASE_DB_URL:?SUPABASE_DB_URL must be set}"
: "${SUPABASE_DB_PASSWORD:?SUPABASE_DB_PASSWORD must be set}"

# ---- Derive pooler host + user from the connection string --------------------
# SUPABASE_DB_URL looks like:
#   postgresql://<user>:<pw>@<host>:<port>/<db>
# The session-mode pooler listens on 5432; the TRANSACTION-mode pooler on 6543.
# We strip any password from the URL and force the transaction-mode port.

url="$SUPABASE_DB_URL"
url="${url#*://}"                 # drop scheme
userinfo="${url%%@*}"             # user[:pw]
hostpart="${url#*@}"              # host:port/db[?...]
db_user="${userinfo%%:*}"        # user (drop any :pw)

hostport="${hostpart%%/*}"        # host:port
host="${hostport%%:*}"            # host

# Everything after the first '/' is db[?params]; take db name before any '?'
dbtail="${hostpart#*/}"
db_name="${dbtail%%\?*}"
[ -n "$db_name" ] || db_name="postgres"

# The pooler must be reached in TRANSACTION mode -> port 6543.
POOL_PORT="${POOL_PORT:-6543}"

# Guard: make sure we're actually pointing at a pooler host, not a direct db.
case "$host" in
  *pooler.supabase.com) : ;;
  *) echo "ERROR: '$host' does not look like the Supabase connection pooler host" >&2
     exit 1 ;;
esac

# psql connection string (no password here — that comes via PGPASSWORD).
CONNSTR="host=${host} port=${POOL_PORT} dbname=${db_name} user=${db_user} sslmode=require"

export PGPASSWORD="$SUPABASE_DB_PASSWORD"

# Keep each connection strictly one-shot and fast-failing.
export PGCONNECT_TIMEOUT="${PGCONNECT_TIMEOUT:-15}"

N=20
tmpdir="$(mktemp -d)"
trap 'rm -rf "$tmpdir"' EXIT

one_shot() {
  local i="$1"
  # A single query per connection; transaction-mode pooler assigns a backend
  # per transaction. Exit non-zero on any failure.
  if psql "$CONNSTR" -v ON_ERROR_STOP=1 -tAqc "SELECT pg_backend_pid();" \
        > "$tmpdir/out.$i" 2> "$tmpdir/err.$i"; then
    : > "$tmpdir/ok.$i"
  fi
}

# ---- Fire 20 concurrent queries and wait for all of them ---------------------
pids=()
for i in $(seq 1 "$N"); do
  one_shot "$i" &
  pids+=("$!")
done

for pid in "${pids[@]}"; do
  wait "$pid"
done

# ---- Tally successes ---------------------------------------------------------
ok=0
for i in $(seq 1 "$N"); do
  [ -f "$tmpdir/ok.$i" ] && ok=$((ok + 1))
done

if [ "$ok" -eq "$N" ]; then
  echo "POOL OK ${ok}/${N}"
  exit 0
else
  echo "POOL FAIL ${ok}/${N}" >&2
  for i in $(seq 1 "$N"); do
    [ -f "$tmpdir/ok.$i" ] || { echo "-- query $i error:" >&2; cat "$tmpdir/err.$i" >&2; }
  done
  exit 1
fi
EOF
chmod +x pool_smoke.sh
echo "written"

written

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

POOL OK 20/20
exit=0

echo "== distinct backend pids across 20 runs =="
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for i in $(seq 1 20); do psql "host=aws-0-us-east-1.pooler.supabase.com port=6543 dbname=postgres user=postgres.<ref> sslmode=require" -tAqc "SELECT pg_backend_pid();"; done | sort -u | wc -l
echo "== guard: forcing session port 5432 should be refused =="
POOL_PORT=5432 ./pool_smoke.sh; echo "exit=$?"

== distinct backend pids across 20 runs ==
1
== guard: forcing session port 5432 should be refused ==
exit=1

POOL FAIL 15/20
-- query 6 error:
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
-- query 7 error:
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
-- query 15 error:
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
-- query 16 error:
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
-- query 17 error:
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15

cd "$(pwd)"; ls -l pool_smoke.sh; echo "--- final run ---"; ./pool_smoke.sh; echo "exit=$?"; echo "--- no hardcoded password check ---"; grep -q "$SUPABASE_DB_PASSWORD" pool_smoke.sh && echo "FOUND (bad)" || echo "password not hardcoded (good)"

-rwxr-xr-x 1 root root 3066 Jul 26 02:29 pool_smoke.sh
--- final run ---
POOL OK 20/20
exit=0
--- no hardcoded password check ---
password not hardcoded (good)