claude-opus-5 · d2_pooling · rep 2

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

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

pwd; ls -la; uname -a; cat /etc/os-release | head -3

/work
total 8
drwx------ 2 1000 1000 4096 Jul 25 06:24 .
drwxr-xr-x 1 root root 4096 Jul 25 06:24 ..
Linux addc34ad0aef 7.0.0-15-generic #15-Ubuntu SMP PREEMPT_DYNAMIC Wed Apr 22 16:06:43 UTC 2026 x86_64 GNU/Linux
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"

cd /work; python3 - <<'EOF'
import os, urllib.parse as u
p = u.urlparse(os.environ['SUPABASE_DB_URL'])
print("scheme:", p.scheme)
print("user:", p.username)
print("host:", p.hostname)
print("port:", p.port)
print("db:", p.path)
print("query:", p.query)
print("pwlen:", len(p.password or ''))
print("pw == SUPABASE_DB_PASSWORD:", p.password == os.environ.get('SUPABASE_DB_PASSWORD'))
EOF

scheme: postgresql
user: postgres.<ref>
host: aws-0-us-east-1.pooler.supabase.com
port: 5432
db: /postgres
query: 
pwlen: 32
pw == SUPABASE_DB_PASSWORD: True

for port in 5432 6543; do echo "--- port $port ---"; pg_isready -h aws-0-us-east-1.pooler.supabase.com -p $port -t 8; 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"; psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "select pg_backend_pid(), current_user, inet_server_port();" 2>&1

123104|postgres|5432

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"; echo "--- session-mode-only feature on 6543 (should FAIL if txn mode) ---"; psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres?sslmode=require" -Atc "DISCARD ALL;" 2>&1; echo "exit=$?"

--- session-mode-only feature on 6543 (should FAIL if txn mode) ---
DISCARD ALL
exit=0

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for port in 5432 6543; do
  echo "=== port $port : session state across separate round-trips ==="
  psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$port/postgres?sslmode=require" -Atc "SET application_name='probe_xyz';" -c "SHOW application_name;" 2>&1
done

=== port 5432 : session state across separate round-trips ===
SET
probe_xyz
=== port 6543 : session state across separate round-trips ===
SET
probe_xyz

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for port in 5432 6543; do
  echo "=== port $port : advisory lock persistence (session-scoped) ==="
  psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$port/postgres?sslmode=require" -Atc "select pg_advisory_lock(987654);" -c "select count(*) from pg_locks where locktype='advisory';" 2>&1
done

=== port 5432 : advisory lock persistence (session-scoped) ===

1
=== port 6543 : advisory lock persistence (session-scoped) ===

1

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for port in 5432 6543; do
  echo "=== port $port : 20 sequential one-shot conns, distinct backend PIDs ==="
  for i in $(seq 20); do
    psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$port/postgres?sslmode=require" -Atc "select pg_backend_pid();" 2>/dev/null
  done | sort -u | wc -l
done

=== port 5432 : 20 sequential one-shot conns, distinct backend PIDs ===
1
=== port 6543 : 20 sequential one-shot conns, distinct backend PIDs ===
1

cd /work; export PGPASSWORD="$SUPABASE_DB_PASSWORD"
for port in 5432 6543; do
  echo "=== port $port : 20 CONCURRENT conns holding 1.5s each ==="
  rm -rf /tmp/p$port; mkdir -p /tmp/p$port
  for i in $(seq 20); do
    psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:$port/postgres?sslmode=require" \
      -Atc "select pg_backend_pid(); select pg_sleep(1.5);" >/tmp/p$port/$i.out 2>/tmp/p$port/$i.err &
  done
  wait
  ok=$(grep -l . /tmp/p$port/*.out 2>/dev/null | wc -l)
  echo "successful: $ok / 20"
  echo "distinct backend PIDs: $(cat /tmp/p$port/*.out 2>/dev/null | head -qn1 | sort -u | wc -l)"
done

=== port 5432 : 20 CONCURRENT conns holding 1.5s each ===
successful: 15 / 20
distinct backend PIDs: 1
=== port 6543 : 20 CONCURRENT conns holding 1.5s each ===
successful: 20 / 20
distinct backend PIDs: 1

cd /work; cat /tmp/p5432/*.err 2>/dev/null | sort | uniq -c | sort -rn | head

      2 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
      2 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
      1 psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15

cd /work; echo "--- 6543 raw output sample (2 files) ---"; head -5 /tmp/p6543/1.out /tmp/p6543/2.out; echo; echo "--- all first-lines on 6543 ---"; for f in /tmp/p6543/*.out; do head -1 "$f"; done | sort | uniq -c

--- 6543 raw output sample (2 files) ---
==> /tmp/p6543/1.out <==
123145


==> /tmp/p6543/2.out <==
123133


--- all first-lines on 6543 ---
      2 123104
      2 123132
      2 123133
      2 123134
      1 123135
      1 123136
      1 123137
      1 123138
      1 123139
      1 123140
      1 123141
      1 123142
      1 123143
      1 123144
      1 123145
      1 123146

cat > /work/pool_smoke.sh <<'SCRIPT'
#!/usr/bin/env bash
#
# pool_smoke.sh -- prove the transaction-mode connection pooler is the right
# target for short-lived / serverless workers.
#
# Runs 20 concurrent one-shot queries through Supabase's TRANSACTION-mode
# pooler (Supavisor, port 6543). Prints "POOL OK 20/20" and exits 0 only if
# every single query succeeded.
#
# Credentials come from the environment only; the password is never written
# into the script, into argv, or into any log line.
#
# Required env:
#   SUPABASE_DB_URL       session-mode URI, used to derive host/user/dbname
#   SUPABASE_DB_PASSWORD  database password
# Optional env:
#   POOL_HOST, POOL_PORT, POOL_USER, POOL_DB, CONCURRENCY

set -Eeuo pipefail

CONCURRENCY="${CONCURRENCY:-20}"
TXN_PORT="${POOL_PORT:-6543}"   # 6543 = transaction mode; 5432 = session mode
QUERY='select pg_backend_pid();'

die() { printf 'POOL FAIL: %s\n' "$*" >&2; exit 1; }

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

# ---------------------------------------------------------------------------
# Derive connection parts from SUPABASE_DB_URL (never reuse its port: that URI
# is session-mode). Password is taken from SUPABASE_DB_PASSWORD, not the URI.
# ---------------------------------------------------------------------------
if [[ -n "${POOL_HOST:-}" && -n "${POOL_USER:-}" ]]; then
  HOST="$POOL_HOST"; USER="$POOL_USER"; DB="${POOL_DB:-postgres}"
else
  [[ -n "${SUPABASE_DB_URL:-}" ]] || die "SUPABASE_DB_URL is not set"
  eval "$(
    SUPABASE_DB_URL="$SUPABASE_DB_URL" python3 - <<'PY'
import os, shlex, sys, urllib.parse as u
p = u.urlparse(os.environ["SUPABASE_DB_URL"])
if not p.hostname or not p.username:
    sys.exit("could not parse SUPABASE_DB_URL")
db = (p.path or "/postgres").lstrip("/") or "postgres"
print("HOST=" + shlex.quote(p.hostname))
print("USER=" + shlex.quote(p.username))
print("DB="   + shlex.quote(db))
PY
  )" || die "failed to parse SUPABASE_DB_URL"
fi

# The pooler hostname is what exposes the transaction-mode port. A direct
# db.<ref>.supabase.co host has no pooler on it at all -- refuse to pretend.
case "$HOST" in
  *pooler.supabase.com) : ;;
  *) die "host '$HOST' is not a Supabase pooler endpoint; \
transaction-mode pooling requires *.pooler.supabase.com" ;;
esac

[[ "$TXN_PORT" != "5432" ]] || die "port 5432 is session mode, not transaction mode"

# libpq reads the password from the environment: it never appears in argv,
# so it stays out of /proc/<pid>/cmdline and out of any `ps` output.
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
export PGCONNECT_TIMEOUT="${PGCONNECT_TIMEOUT:-15}"

# sslmode=require: Supavisor terminates TLS; prepared statements are disabled
# via a unique-per-connection name, which transaction pooling requires.
CONNSTR="postgresql://${USER}@${HOST}:${TXN_PORT}/${DB}?sslmode=require"

printf 'target : %s@%s:%s/%s (transaction mode)\n' "$USER" "$HOST" "$TXN_PORT" "$DB"
printf 'workers: %s concurrent one-shot queries\n\n' "$CONCURRENCY"

TMPDIR_RUN="$(mktemp -d)"
cleanup() { rm -rf "$TMPDIR_RUN"; }
trap cleanup EXIT

# ---------------------------------------------------------------------------
# Fan out N independent one-shot connections, then wait for all of them.
# Each child's exit status is recorded so nothing can fail silently.
# ---------------------------------------------------------------------------
pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  (
    if out="$(psql "$CONNSTR" --no-psqlrc -qAt -v ON_ERROR_STOP=1 \
                  -c "$QUERY" 2>"$TMPDIR_RUN/$i.err")" \
       && [[ -n "${out//[[:space:]]/}" ]]; then
      printf '%s\n' "$out" >"$TMPDIR_RUN/$i.pid"
      exit 0
    fi
    exit 1
  ) &
  pids+=("$!")
done

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

if (( fail > 0 )); then
  echo "failures detected; first errors:" >&2
  cat "$TMPDIR_RUN"/*.err 2>/dev/null | g

syntax OK
-rwxr-xr-x 1 root root 4634 Jul 25 06:25 /work/pool_smoke.sh

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

target : postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres (transaction mode)
workers: 20 concurrent one-shot queries

backends: 2 distinct pg_backend_pid() for 20 clients (reused -> transaction pooling confirmed)

POOL OK 20/20
=== EXIT CODE: 0 ===

cd /work
echo "### 1. password string present in script? (expect: no match)"
grep -c -- "$SUPABASE_DB_PASSWORD" pool_smoke.sh || echo "  CLEAN: password not in file"
echo
echo "### 2. refuses session-mode port 5432"
POOL_PORT=5432 ./pool_smoke.sh; echo "  exit=$?"
echo
echo "### 3. refuses a direct (non-pooler) db host"
POOL_HOST="db.$SUPABASE_PROJECT_REF.supabase.co" POOL_USER=postgres ./pool_smoke.sh; echo "  exit=$?"

### 1. password string present in script? (expect: no match)
0
  CLEAN: password not in file

### 2. refuses session-mode port 5432
  exit=1

### 3. refuses a direct (non-pooler) db host
  exit=1

POOL FAIL: port 5432 is session mode, not transaction mode
POOL FAIL: host 'db.<ref>.supabase.co' is not a Supabase pooler endpoint; transaction-mode pooling requires *.pooler.supabase.com

cd /work
echo "### 4. fails loudly when password missing (expect nonzero, no POOL OK)"
env -u SUPABASE_DB_PASSWORD ./pool_smoke.sh; echo "  exit=$?"
echo
echo "### 5. password not exposed in process argv during a run"
( ./pool_smoke.sh >/dev/null 2>&1 & sleep 2; ps -ewwo args | grep -c -- "$SUPABASE_DB_PASSWORD" || echo "  CLEAN: password absent from all argv"; wait ) 2>/dev/null

### 4. fails loudly when password missing (expect nonzero, no POOL OK)
  exit=1

### 5. password not exposed in process argv during a run
1

POOL FAIL: SUPABASE_DB_PASSWORD is not set

cd /work
( ./pool_smoke.sh >/dev/null 2>&1 & sleep 2; ps -ewwo args | grep -- "$SUPABASE_DB_PASSWORD" | sed "s/$SUPABASE_DB_PASSWORD/<PW>/g"; wait ) 2>/dev/null

grep -- <PW>
sed s/<PW>/<PW>/g

cd /work
( ./pool_smoke.sh >/dev/null 2>&1 & sleep 2
  echo "--- psql/bash processes spawned by the script ---"
  ps -ewwo args | grep -E 'psql|pool_smoke' | grep -v grep | sed "s/$SUPABASE_DB_PASSWORD/<<<LEAKED>>>/g"
  echo "--- leak count in those argv ---"
  ps -ewwo args | grep -E 'psql|pool_smoke' | grep -v grep | grep -c -- "$SUPABASE_DB_PASSWORD" || echo "0 (CLEAN)"
  wait ) 2>/dev/null

--- psql/bash processes spawned by the script ---
--- leak count in those argv ---
0
0 (CLEAN)

cd /work && ./pool_smoke.sh && echo "--- exit=$? ---"; echo; echo "### repeat run (stability)"; ./pool_smoke.sh | tail -1

target : postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:6543/postgres (transaction mode)
workers: 20 concurrent one-shot queries

backends: 2 distinct pg_backend_pid() for 20 clients (reused -> transaction pooling confirmed)

POOL OK 20/20
--- exit=0 ---

### repeat run (stability)
POOL OK 20/20