moonshotai/kimi-k3 · d2_pooling · rep 1

echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL" | sed -E 's#(://[^:]+:)[^@]+@#\1****@#'; echo "DB_PASSWORD set: $([ -n "$SUPABASE_DB_PASSWORD" ] && echo yes || echo no)"; echo "ANON set: $([ -n "$SUPABASE_ANON_KEY" ] && echo yes || echo no)"; echo "SERVICE set: $([ -n "$SUPABASE_SERVICE_ROLE_KEY" ] && echo yes || echo no)"

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=postgresql://postgres.<ref>:<redacted>@aws-0-us-east-1.pooler.supabase.com:5432/postgres
DB_PASSWORD set: yes
ANON set: yes
SERVICE set: yes

which psql pg_isready python3 jq curl 2>/dev/null; psql --version 2>/dev/null; echo "---"; cat /etc/os-release 2>/dev/null | head -3

/usr/bin/psql
/usr/bin/pg_isready
/usr/bin/python3
/usr/bin/jq
/usr/bin/curl
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"

timeout 5 bash -c '</dev/tcp/aws-0-us-east-1.pooler.supabase.com/6543' && echo "6543 reachable" || echo "6543 NOT reachable"; timeout 5 bash -c '</dev/tcp/aws-0-us-east-1.pooler.supabase.com/5432' && echo "5432 reachable" || echo "5432 NOT reachable"

6543 reachable
5432 reachable

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "host=aws-0-us-east-1.pooler.supabase.com port=6543 dbname=postgres user=postgres.$SUPABASE_PROJECT_REF sslmode=require" -tAc "SELECT pg_backend_pid(), 'connected via transaction pooler'" 2>&1

14255|connected via transaction pooler

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — prove the Supabase TRANSACTION-mode connection pooler
# (PgBouncer/Supavisor, port 6543) serves 20 concurrent one-shot queries.
#
# It deliberately uses ONLY the transaction-mode pooler:
#   - never the session-mode pooler port (5432)
#   - never a direct database connection (db.<ref>.supabase.co)
#
# Credentials come from environment variables; nothing is hardcoded:
#   SUPABASE_DB_URL        used to derive pooler host / user / dbname
#                          (its port is overridden to 6543 = transaction mode)
#   SUPABASE_DB_PASSWORD   database password (never echoed, never stored)
#   SUPABASE_PROJECT_REF   fallback for building the pooler username
#   SUPABASE_POOLER_HOST   optional override for the pooler hostname
#
# On full success prints exactly one line on stdout:  POOL OK 20/20
# and exits 0. Any failure exits non-zero and never prints that line.

set -euo pipefail

CONCURRENCY=20
POOLER_PORT=6543   # transaction-mode pooler port. Session mode is 5432 — do NOT use.

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

command -v psql >/dev/null 2>&1 || die "psql not found in PATH"

# --- credentials from environment --------------------------------------------
[ -n "${SUPABASE_DB_URL:-}" ]      || die "SUPABASE_DB_URL is not set"
[ -n "${SUPABASE_DB_PASSWORD:-}" ] || die "SUPABASE_DB_PASSWORD is not set"

# --- derive pooler host / user / dbname from SUPABASE_DB_URL ------------------
# e.g. postgresql://postgres.<ref>:<pw>@aws-0-<region>.pooler.supabase.com:5432/postgres
url_no_scheme="${SUPABASE_DB_URL#*://}"
userinfo="${url_no_scheme%%@*}"
hostport_db="${url_no_scheme#*@}"
hostport="${hostport_db%%/*}"
DBNAME="${hostport_db#*/}"; DBNAME="${DBNAME%%\?*}"; DBNAME="${DBNAME:-postgres}"
POOLER_HOST="${SUPABASE_POOLER_HOST:-${hostport%%:*}}"
DBUSER="${userinfo%%:*}"   # pooler requires the postgres.<project-ref> form

# Fallback: build the pooler-style username from the project ref if needed.
if [[ "$DBUSER" != *.* && -n "${SUPABASE_PROJECT_REF:-}" ]]; then
  DBUSER="postgres.${SUPABASE_PROJECT_REF}"
fi

# --- safety rails: prove this is the transaction pooler, nothing else ---------
[[ "$POOLER_HOST" == *.pooler.supabase.com ]] \
  || die "host '$POOLER_HOST' is not the Supabase connection pooler (*.pooler.supabase.com); refusing direct/session database connection"
[ "$POOLER_PORT" = "6543" ] \
  || die "port $POOLER_PORT is not the transaction-mode pooler port (6543)"

export PGPASSWORD="$SUPABASE_DB_PASSWORD"   # from env; never hardcoded
export PGCONNECT_TIMEOUT=10

run_one() {
  psql "host=$POOLER_HOST port=$POOLER_PORT dbname=$DBNAME user=$DBUSER sslmode=require" \
       -X -q -t -A -c "SELECT pg_backend_pid()" >/dev/null 2>&1
}

echo "pool_smoke: $CONCURRENCY concurrent one-shot queries via transaction-mode pooler $POOLER_HOST:$POOLER_PORT ..." >&2

pids=()
for _ in $(seq 1 "$CONCURRENCY"); do
  run_one &
  pids+=($!)
done

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

if [ "$ok" -eq "$CONCURRENCY" ]; then
  echo "POOL OK ${ok}/${CONCURRENCY}"
  exit 0
fi

echo "pool_smoke: FAILURE — only $ok/$CONCURRENCY queries succeeded" >&2
exit 1
EOF
chmod +x pool_smoke.sh
ls -l pool_smoke.sh && bash -n pool_smoke.sh && echo "syntax OK"

-rwxr-xr-x 1 root root 3186 Jul 24 06:15 pool_smoke.sh
syntax OK

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

POOL OK 20/20
exit_code=0

pool_smoke: 20 concurrent one-shot queries via transaction-mode pooler aws-0-us-east-1.pooler.supabase.com:6543 ...

for i in 1 2 3; do out=$(./pool_smoke.sh 2>/dev/null); rc=$?; echo "run$i: rc=$rc stdout=[$out]"; done

run1: rc=0 stdout=[POOL OK 20/20]
run2: rc=0 stdout=[POOL OK 20/20]
run3: rc=0 stdout=[POOL OK 20/20]

SUPABASE_DB_PASSWORD="definitely-wrong" ./pool_smoke.sh >/tmp/out.txt 2>/tmp/err.txt; echo "rc=$?"; echo "stdout: $(cat /tmp/out.txt)"; echo "stderr tail: $(tail -1 /tmp/err.txt)"

rc=1
stdout: 
stderr tail: pool_smoke: FAILURE — only 0/20 queries succeeded

grep -qF "$SUPABASE_DB_PASSWORD" pool_smoke.sh && echo "PASSWORD LEAKED IN SCRIPT" || echo "no password in script"; grep -c "6543" pool_smoke.sh; grep -c "pg_backend_pid" pool_smoke.sh

no password in script
5
1