moonshotai/kimi-k3 · d2_pooling · rep 3

env | grep -i supabase | sed 's/\(PASSWORD=\).*/\1<redacted>/; s/\(SERVICE_ROLE_KEY=\).*/\1<redacted>/; s/\(ANON_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 pg_isready curl jq; psql --version 2>/dev/null; echo "---"; pwd

/usr/bin/psql
/usr/bin/pg_isready
/usr/bin/curl
/usr/bin/jq
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
/work

HOST=$(echo "$SUPABASE_DB_URL" | sed -E 's#.*@([^:/]+):.*#\1#'); echo "host=$HOST"; getent hosts "$HOST" | head -2; (exec 3<>/dev/tcp/"$HOST"/6543 && echo "port 6543 reachable" && exec 3>&- 3<&-) || echo "port 6543 NOT reachable"

host=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
port 6543 reachable

HOST=$(echo "$SUPABASE_DB_URL" | sed -E 's#.*@([^:/]+):.*#\1#'); USER=$(echo "$SUPABASE_DB_URL" | sed -E 's#.*//([^:]+):.*@\x28^:/]+\x29.*#\1#'); PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "host=$HOST port=6543 dbname=postgres user=postgres.$SUPABASE_PROJECT_REF sslmode=require" -tAc 'SELECT version(); SELECT pg_backend_pid();' 2>&1 | head -3

PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
47696

cat > /work/pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — smoke test that proves the Supabase TRANSACTION-MODE pooler
# is being used correctly (concurrent short-lived connections, no password
# hardcoding, all credentials from the environment).
#
# Why this matters: the session-mode pooler (port 5432) / direct DB (5432 on
# db.<ref>.supabase.co) pin one backend connection per client for the whole
# session, which is exactly what exhausts connections from serverless workers.
# The transaction-mode pooler (port 6543 on the pooler host) hands a backend
# to each transaction and releases it immediately, so many concurrent clients
# share a small pool of backends.
#
# Exits 0 and prints "POOL OK 20/20" only if all 20 concurrent one-shot
# queries succeed.

set -euo pipefail

TOTAL=20
POOLER_PORT=6543   # Supabase transaction-mode pooler port (5432 = session mode — must NOT be used)

fail() { echo "pool_smoke: ERROR: $*" >&2; exit 1; }

# ---- 1. Credentials strictly from the environment --------------------------
[ -n "${SUPABASE_DB_PASSWORD:-}" ] || fail "SUPABASE_DB_PASSWORD is not set"
[ -n "${SUPABASE_PROJECT_REF:-}" ] || fail "SUPABASE_PROJECT_REF is not set"
[ -n "${SUPABASE_DB_URL:-}" ]      || fail "SUPABASE_DB_URL is not set"

# ---- 2. Derive the pooler HOST from SUPABASE_DB_URL ------------------------
# Expected shape: postgresql://postgres.<ref>:<password>@<host>:5432/postgres
# We take only the host from it and force the transaction-mode port (6543).
DB_URL_NO_CREDS="${SUPABASE_DB_URL#*@}"          # strip user:password@
POOLER_HOST="${DB_URL_NO_CREDS%%[:/]*}"          # strip :port and/or /dbname

case "$POOLER_HOST" in
  *pooler.supabase.com) : ;;                     # expected Supabase pooler host
  *) fail "could not derive a Supabase pooler host from SUPABASE_DB_URL (got '$POOLER_HOST')" ;;
esac

# ---- 3. Build the transaction-mode connection string -----------------------
# Password is passed ONLY via the PGPASSWORD env var — never on the command
# line and never embedded in the script.
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
CONNINFO="host=${POOLER_HOST} port=${POOLER_PORT} dbname=postgres user=postgres.${SUPABASE_PROJECT_REF} sslmode=require connect_timeout=10"

# ---- 4. Fire 20 concurrent one-shot queries --------------------------------
TMPDIR_RUN="$(mktemp -d)"
trap 'rm -rf "$TMPDIR_RUN"' EXIT

pids=()
for i in $(seq 1 "$TOTAL"); do
  (
    psql "$CONNINFO" -X -q -t -A \
      -c 'SELECT pg_backend_pid();' \
      >"$TMPDIR_RUN/$i.out" 2>"$TMPDIR_RUN/$i.err"
  ) &
  pids+=($!)
done

# ---- 5. Wait for all of them and count successes ---------------------------
ok=0
declare -a status_by_idx
for idx in "${!pids[@]}"; do
  if wait "${pids[$idx]}"; then
    status_by_idx[$idx]=0
  else
    status_by_idx[$idx]=1
  fi
done

for idx in "${!pids[@]}"; do
  n=$((idx + 1))
  if [ "${status_by_idx[$idx]}" -eq 0 ] && [ -s "$TMPDIR_RUN/$n.out" ]; then
    ok=$((ok + 1))
  else
    echo "pool_smoke: query #$n FAILED:" >&2
    sed 's/^/  /' "$TMPDIR_RUN/$n.err" >&2 || true
  fi
done

# ---- 6. Verdict -------------------------------------------------------------
if [ "$ok" -eq "$TOTAL" ]; then
  echo "POOL OK 20/20"
  exit 0
else
  echo "pool_smoke: only $ok/$TOTAL queries succeeded" >&2
  exit 1
fi
EOF
chmod +x /work/pool_smoke.sh
ls -l /work/pool_smoke.sh
bash -n /work/pool_smoke.sh && echo "syntax OK"

-rwxr-xr-x 1 root root 3257 Jul 24 14:36 /work/pool_smoke.sh
syntax OK

cd /work && time ./pool_smoke.sh; echo "exit code: $?"

POOL OK 20/20
exit code: 0

real	0m0.964s
user	0m0.535s
sys	0m0.206s

cd /work && env -u SUPABASE_DB_PASSWORD ./pool_smoke.sh; echo "exit code (no password): $?"

exit code (no password): 1

pool_smoke: ERROR: SUPABASE_DB_PASSWORD is not set

cd /work && grep -F "$SUPABASE_DB_PASSWORD" pool_smoke.sh && echo "PASSWORD LEAKED IN SCRIPT" || echo "no hardcoded password in script"; grep -n "6543\|5432" pool_smoke.sh

no hardcoded password in script
7:# Why this matters: the session-mode pooler (port 5432) / direct DB (5432 on
10:# The transaction-mode pooler (port 6543 on the pooler host) hands a backend
20:POOLER_PORT=6543   # Supabase transaction-mode pooler port (5432 = session mode — must NOT be used)
30:# Expected shape: postgresql://postgres.<ref>:<password>@<host>:5432/postgres
31:# We take only the host from it and force the transaction-mode port (6543).

cd /work && ./pool_smoke.sh && ./pool_smoke.sh; echo "final exit: $?"

POOL OK 20/20
POOL OK 20/20
final exit: 0