claude-opus-5 · d2_pooling · rep 3

echo "URL=$SUPABASE_URL"; echo "REF=$SUPABASE_PROJECT_REF"; echo "DBURL=$(echo "$SUPABASE_DB_URL" | sed 's/:[^:@]*@/:***@/')"; pwd; which psql pg_isready python3; psql --version

URL=https://<ref>.supabase.co
REF=<ref>
DBURL=postgresql://postgres.<ref>:***@aws-0-us-east-1.pooler.supabase.com:5432/postgres
/work
/usr/bin/psql
/usr/bin/pg_isready
/usr/bin/python3
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

env | grep -i -E 'supabase|pg' | sed 's/\(PASSWORD=\).*/\1***/' | sed 's/:[^:@]*@/:***@/'

SUPABASE_DB_PASSWORD=***
SUPABASE_DB_URL=postgresql://postgres.<ref>:***@aws-0-us-east-1.pooler.supabase.com:5432/postgres
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

cd /work; H=aws-0-us-east-1.pooler.supabase.com; for p in 5432 6543; do echo "--- port $p"; timeout 15 pg_isready -h $H -p $p -U postgres.<ref>; done

--- port 5432
aws-0-us-east-1.pooler.supabase.com:5432 - accepting connections
--- port 6543
aws-0-us-east-1.pooler.supabase.com:6543 - accepting connections

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"; timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "select 'tx-ok', pg_backend_pid(), current_user, inet_server_port()" 2>&1 | tail -5

tx-ok|123133|postgres|5432

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "=== 6543 : DISCARD ALL (should FAIL in tx mode) ==="
timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "DISCARD ALL" 2>&1 | tail -3
echo "=== 5432 : DISCARD ALL (should SUCCEED in session mode) ==="
timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" -Atc "DISCARD ALL" 2>&1 | tail -3

=== 6543 : DISCARD ALL (should FAIL in tx mode) ===
DISCARD ALL
=== 5432 : DISCARD ALL (should SUCCEED in session mode) ===
DISCARD ALL

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "=== 6543: pid reuse across separate one-shot conns (tx pooling multiplexes) ==="
for i in 1 2 3 4 5 6; do timeout 20 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "select pg_backend_pid()" 2>&1; done | sort | uniq -c
echo "=== 5432: same test on session mode ==="
for i in 1 2 3 4 5 6; do timeout 20 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres?sslmode=require" -Atc "select pg_backend_pid()" 2>&1; done | sort | uniq -c

=== 6543: pid reuse across separate one-shot conns (tx pooling multiplexes) ===
      6 123133
=== 5432: same test on session mode ===
      6 123171

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for p in 5432 6543; do echo "=== port $p ==="; timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "select version()" 2>&1 | tail -2; timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "show server_version_num" 2>&1|tail -2; timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "select inet_server_addr(), inet_server_port(), current_setting('listen_addresses',true)" 2>&1|tail -2; done

=== port 5432 ===
PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
170006
2600:1f18:4850:7c01:c70c:3b85:612c:200e|5432|*
=== port 6543 ===
PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
170006
2600:1f18:4850:7c01:c70c:3b85:612c:200e|5432|*

cd /work; getent hosts aws-0-us-east-1.pooler.supabase.com; echo "--- direct db host?"; getent hosts db.<ref>.supabase.co || echo "no direct DNS"

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
--- direct db host?
2600:1f18:4850:7c01:c70c:3b85:612c:200e db.<ref>.supabase.co

cd /work; export PGPASYORD=; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "### SESSION-STATE PERSISTENCE TEST (two separate statements, one psql connection)"
for p in 5432 6543; do
echo "--- port $p"
timeout 30 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "set application_name='probe_xyz'" -c "show application_name" 2>&1 | tail -3
done
echo
echo "### TEMP TABLE ACROSS STATEMENTS (tx mode: 2nd stmt should not see temp table)"
for p in 5432 6543; do
echo "--- port $p"
timeout 30 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "create temp table t_probe(i int)" -c "select count(*) from t_probe" 2>&1 | tail -3
done

### SESSION-STATE PERSISTENCE TEST (two separate statements, one psql connection)
--- port 5432
SET
probe_xyz
--- port 6543
SET
probe_xyz

### TEMP TABLE ACROSS STATEMENTS (tx mode: 2nd stmt should not see temp table)
--- port 5432
CREATE TABLE
0
--- port 6543
CREATE TABLE
0

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "### Supavisor/PgBouncer admin-ish + protocol probes"
for p in 5432 6543; do echo "--- port $p"
# real Supavisor tx-mode does NOT allow SET on unnamed prepared stmts / advertises itself; check startup params
timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "select current_setting('supavisor.mode', true)" 2>&1|tail -2
# LISTEN is session-only, forbidden in tx pooling
timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "LISTEN chan_probe" -c "select 'listen-survived'" 2>&1|tail -2
done

### Supavisor/PgBouncer admin-ish + protocol probes
--- port 5432

LISTEN
listen-survived
--- port 6543

LISTEN
listen-survived

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "### Is there a pgbouncer/supavisor in path at all? compare backend pid stability under CONCURRENCY"
for p in 5432 6543; do echo "--- port $p : 12 concurrent conns, distinct pids?"
for i in $(seq 1 12); do (timeout 25 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -Atc "select pg_backend_pid()" 2>&1) & done; wait
echo
done

### Is there a pgbouncer/supavisor in path at all? compare backend pid stability under CONCURRENCY
--- port 5432 : 12 concurrent conns, distinct pids?
123171
123196
123197
123198
123199
123200
123201
123202
123203
123204
123205
123206

--- port 6543 : 12 concurrent conns, distinct pids?
123132
123133
123133
123132
123132
123132
123132
123132
123133
123133
123133
123133

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "### CORRECTED: separate transactions on ONE connection (psql -f, autocommit, no implicit tx)"
cat > /tmp/probe.sql <<'EOF'
\set ON_ERROR_STOP off
set application_name='probe_xyz';
select application_name from pg_stat_activity where pid=pg_backend_pid();
EOF
for p in 5432 6543; do echo "--- port $p"; timeout 30 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -At -f /tmp/probe.sql 2>&1|tail -3; done

### CORRECTED: separate transactions on ONE connection (psql -f, autocommit, no implicit tx)
--- port 5432
SET
probe_xyz
--- port 6543
SET
probe_xyz

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
echo "### Prepared statements: tx-mode Supavisor/pgbouncer classically errors or needs protocol handling"
for p in 5432 6543; do echo "--- port $p"; timeout 30 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$p/postgres?sslmode=require" -At -c "select 1" >/dev/null 2>&1 && echo "simple ok"
timeout 30 python3 - "$p" <<'PY' 2>&1 | tail -4
import sys,subprocess
PY
done
echo "### How many total distinct backends does 6543 use for 20 concurrent? (pool size evidence)"
for i in $(seq 1 20); do (timeout 30 psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "select pg_backend_pid()" 2>&1) & done | sort -u | tr '\n' ' '; wait

### Prepared statements: tx-mode Supavisor/pgbouncer classically errors or needs protocol handling
--- port 5432
simple ok
--- port 6543
simple ok
### How many total distinct backends does 6543 use for 20 concurrent? (pool size evidence)
123132 123133 

cd /work; cat > pool_smoke.sh <<'EOS'
#!/usr/bin/env bash
# pool_smoke.sh - Prove the transaction-mode (Supavisor) pooler handles a burst
# of one-shot serverless-style connections.
#
# Credentials come ONLY from the environment:
#   SUPABASE_DB_URL       (pooler host/user/db; port is overridden to tx mode)
#   SUPABASE_DB_PASSWORD  (never hardcoded; passed via PGPASSWORD)
set -uo pipefail

CONCURRENCY="${CONCURRENCY:-20}"
TXN_PORT="${SUPABASE_TXN_POOLER_PORT:-6543}"   # Supavisor transaction mode
SESSION_PORT=5432                              # session mode - must NOT be used

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

[[ -n "${SUPABASE_DB_URL:-}"      ]] || die "SUPABASE_DB_URL is not set"
[[ -n "${SUPABASE_DB_PASSWORD:-}" ]] || die "SUPABASE_DB_PASSWORD is not set"
command -v psql >/dev/null 2>&1 || die "psql not found in PATH"

# ---- Derive pooler user/host/db from SUPABASE_DB_URL (strip any password) ----
parsed=$(SUPABASE_DB_URL="$SUPABASE_DB_URL" python3 - <<'PY'
import os, sys
from urllib.parse import urlsplit, unquote
u = urlsplit(os.environ["SUPABASE_DB_URL"])
user = unquote(u.username or "")
host = u.hostname or ""
db   = (u.path or "/postgres").lstrip("/") or "postgres"
if not user or not host:
    sys.exit("could not parse user/host from SUPABASE_DB_URL")
print(user); print(host); print(db)
PY
) || die "$parsed"
DB_USER=$(sed -n 1p <<<"$parsed")
DB_HOST=$(sed -n 2p <<<"$parsed")
DB_NAME=$(sed -n 3p <<<"$parsed")

# ---- Guardrails: must be the POOLER host, on the TRANSACTION-mode port ----
case "$DB_HOST" in
  *pooler.supabase.com) : ;;
  db.*.supabase.co) die "SUPABASE_DB_URL points at a DIRECT database host ($DB_HOST); a pooler host is required" ;;
  *) die "host '$DB_HOST' is not a recognized Supabase pooler host" ;;
esac
[[ "$TXN_PORT" != "$SESSION_PORT" ]] || die "port $TXN_PORT is the session-mode port, not transaction mode"

# Password is supplied out-of-band so it never appears in a URI or in argv/ps.
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
export PGCONNECT_TIMEOUT="${PGCONNECT_TIMEOUT:-15}"
# Query string carries no secrets.
POOL_URI="postgresql://${DB_USER}@${DB_HOST}:${TXN_PORT}/${DB_NAME}?sslmode=require"

echo "pooler host : $DB_HOST"
echo "pooler port : $TXN_PORT (transaction mode)"
echo "database    : $DB_NAME  user: $DB_USER"
echo "concurrency : $CONCURRENCY one-shot connections"

# ---- Preflight: the transaction-mode port must be reachable ----
psql "$POOL_URI" -Atqc 'select 1' >/dev/null 2>&1 \
  || die "cannot reach transaction pooler at ${DB_HOST}:${TXN_PORT}"

# ---- Verify we are really behind a TRANSACTION-mode pooler ----------------
# Transaction mode multiplexes many client connections onto far fewer server
# backends, so a concurrent burst reuses backend PIDs. Session mode would give
# one distinct backend per client connection.
probe_dir=$(mktemp -d); trap 'rm -rf "$probe_dir"' EXIT
probe_n=8
for i in $(seq 1 "$probe_n"); do
  ( psql "$POOL_URI" -Atqc 'select pg_backend_pid()' >"$probe_dir/p$i" 2>/dev/null ) &
done
wait
distinct=$(cat "$probe_dir"/p* 2>/dev/null | grep -E '^[0-9]+$' | sort -u | wc -l | tr -d ' ')
[[ "$distinct" -ge 1 ]] || die "transaction-mode probe returned no backend PIDs"
if [[ "$distinct" -lt "$probe_n" ]]; then
  echo "tx-mode check: $probe_n concurrent clients -> $distinct distinct backend PID(s) (multiplexed)"
else
  echo "tx-mode check: WARNING: $probe_n clients -> $distinct distinct backends (no reuse observed)" >&2
fi

# ---- The actual smoke test: N concurrent one-shot queries ----------------
run_dir=$(mktemp -d "$probe_dir/run.XXXXXX")
pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  (
    out=$(psql "$POOL_URI" -Atqc 'select pg_backend_pid()' 2>"$run_dir/err.$i")
    if [[ $? -eq 0 && "$out" =~ ^[0-9]+$ ]]; then
      printf '%s\n' "$out" > "$run_dir/ok.$i"
    fi
  ) &
  pids+=("$!")
done

# Wait for ALL workers before judging the result.
for p in "${pids[@]}"; do wait "$p"; done

ok=$(find "$run_dir" -maxdepth 1 -name 'ok.*' | wc -l | tr -d ' ')
bac

syntax OK

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

pooler host : aws-0-us-east-1.pooler.supabase.com
pooler port : 6543 (transaction mode)
database    : postgres  user: postgres.<ref>
concurrency : 20 one-shot connections
tx-mode check: 8 concurrent clients -> 2 distinct backend PID(s) (multiplexed)
served by 4 distinct backend PID(s) for 20 client connections
POOL OK 20/20
=== EXIT CODE: 0 ===

cd /work
echo "### guard: session-mode port rejected"; SUPABASE_TXN_POOLER_PORT=5432 ./pool_smoke.sh; echo "exit=$?"
echo; echo "### guard: direct db host rejected"
SUPABASE_DB_URL="postgresql://postgres:pw@db.<ref>.supabase.co:5432/postgres" ./pool_smoke.sh; echo "exit=$?"
echo; echo "### guard: missing password"; (unset SUPABASE_DB_PASSWORD; ./pool_smoke.sh); echo "exit=$?"

### guard: session-mode port rejected
exit=1

### guard: direct db host rejected
exit=1

### guard: missing password
exit=1

POOL FAIL: port 5432 is the session-mode port, not transaction mode
POOL FAIL: SUPABASE_DB_URL points at a DIRECT database host (db.<ref>.supabase.co); a pooler host is required
POOL FAIL: SUPABASE_DB_PASSWORD is not set

cd /work; echo "### no hardcoded secret in script?"
grep -n "$SUPABASE_DB_PASSWORD" pool_smoke.sh && echo "LEAK!" || echo "OK: password not present in script"
echo; echo "### password not visible in process args (ps scan during run)"
( for i in $(seq 1 40); do ps -eo args 2>/dev/null | grep -F "$SUPABASE_DB_PASSWORD" | grep -v grep && echo "ARGV LEAK"; sleep 0.25; done ) > /tmp/ps.log 2>&1 &
SCAN=$!; ./pool_smoke.sh >/dev/null 2>&1; echo "run exit=$?"; wait $SCAN 2>/dev/null
if [ -s /tmp/ps.log ]; then echo "LEAK DETECTED:"; cat /tmp/ps.log; else echo "OK: password never appeared in argv"; fi

### no hardcoded secret in script?
OK: password not present in script

### password not visible in process args (ps scan during run)
run exit=0
OK: password never appeared in argv

cd /work; echo "### fault injection: bad password must NOT print POOL OK"
SUPABASE_DB_PASSWORD="wrong-password-xyz" ./pool_smoke.sh 2>&1 | tail -6; echo "exit=${PIPESTATUS[0]}"

### fault injection: bad password must NOT print POOL OK
pooler host : aws-0-us-east-1.pooler.supabase.com
pooler port : 6543 (transaction mode)
database    : postgres  user: postgres.<ref>
concurrency : 20 one-shot connections
POOL FAIL: cannot reach transaction pooler at aws-0-us-east-1.pooler.supabase.com:6543
exit=1

cd /work; ls -l pool_smoke.sh; echo "--- final run ---"; ./pool_smoke.sh; echo "EXIT=$?"; echo "--- exact required line present? ---"; ./pool_smoke.sh 2>/dev/null | grep -Fx "POOL OK 20/20" && echo "MATCH"

-rwxr-xr-x 1 root root 4422 Jul 25 06:28 pool_smoke.sh
--- final run ---
pooler host : aws-0-us-east-1.pooler.supabase.com
pooler port : 6543 (transaction mode)
database    : postgres  user: postgres.<ref>
concurrency : 20 one-shot connections
tx-mode check: 8 concurrent clients -> 2 distinct backend PID(s) (multiplexed)
served by 3 distinct backend PID(s) for 20 client connections
POOL OK 20/20
EXIT=0
--- exact required line present? ---
POOL OK 20/20
MATCH