p24-infra Docs

pg-stats-exporter-pooler-tenant-identifier

3 min read

Playbook: pg-stats-exporter — Supavisor ENOIDENTIFIER (no tenant identifier)

Alert: SupabasePgStatsScrapeStale (Prometheus / pg-stats-exporter on vps-i1) First seen: 2026-07-08 23:38 UTC (GH issue #3485) Severity: P2 warning

Symptom

pg-stats-exporter on vps-i1 cannot scrape Supabase; docker compose logs pg-stats-exporter repeats:

[pg-stats-exporter] connection error: connection to server at "54.36.123.110", port 15432 failed:
FATAL:  (ENOIDENTIFIER) no tenant identifier provided (external_id or sni_hostname required)

Metrics confirm: supabase_pg_stats_exporter_errors_total{error_type="connection"} climbing, supabase_pg_stats_exporter_last_scrape_timestamp_seconds 0.

Root cause

The exporter does not connect to Supabase directly. Its deployed .env (/opt/p24-infra/monitoring/.env) points it at the bms-4 socat proxy:

SUPABASE_DB_HOST=54.36.123.110   # bms-4
SUPABASE_DB_PORT=15432           # socat-supabase.service

socat-supabase.service on bms-4 is a raw TCP4 forward:

TCP4-LISTEN:15432 → aws-1-eu-central-1.pooler.supabase.com:5432

The upstream is the Supavisor pooler, which must resolve the Supabase tenant on every connection. It accepts the tenant from one of two places:

  1. TLS SNI hostname — but the exporter dials an IP literal (54.36.123.110), and libpq (PostgreSQL 14+) does not send an SNI extension for IP hosts. socat is a plain TCP relay and does not inject SNI either. → no SNI.
  2. external_id — the tenant ref appended to the username, i.e. grafana_readonly.mwkqmgadqnkkihjdeqsi.

The deployed SUPABASE_DB_USER was the bare grafana_readonly (no .mwkqmgadqnkkihjdeqsi suffix), so neither channel carried the tenant → Supavisor rejects the connection with ENOIDENTIFIER.

This is not a stale/rotated password — the password is valid. docker-compose.yml’s default for SUPABASE_DB_USER is already correct (grafana_readonly.mwkqmgadqnkkihjdeqsi); the deployed .env (sourced from secrets/monitoring.env.sops) overrode it with the tenant-less form.

Why n8n on bms-4 is unaffected: its Supabase Postgres credential already uses a tenant-qualified username, so its socat connections carry external_id.

Confirm (read-only, no secrets printed)

SSH_I1="ssh -i ~/.ssh/vps_root_key root@217.154.82.162"
# 1. failure mode
$SSH_I1 'cd /opt/p24-infra/monitoring && docker compose logs --tail=20 pg-stats-exporter'
# 2. error type = connection
$SSH_I1 'curl -s localhost:9201/metrics | grep supabase_pg_stats_exporter_errors_total'
# 3. deployed username lacks the tenant suffix
$SSH_I1 'grep -nE "^SUPABASE_DB_(USER|HOST|PORT)=" /opt/p24-infra/monitoring/.env'
# 4. socat target is the pooler
ssh root@54.36.123.110 'systemctl cat socat-supabase.service | grep TCP4:'
# 5. prove the fix (reads password from the container env — never prints it)
$SSH_I1 'docker exec monitoring-pg-stats-exporter-1 python -c "
import os,psycopg2 as pg
u=os.environ[\"SUPABASE_DB_USER\"]+\".mwkqmgadqnkkihjdeqsi\"
c=pg.connect(host=os.environ[\"SUPABASE_DB_HOST\"],port=os.environ[\"SUPABASE_DB_PORT\"],user=u,
 password=os.environ[\"SUPABASE_DB_PASSWORD\"],dbname=os.environ[\"SUPABASE_DB_NAME\"],
 sslmode=os.environ.get(\"SUPABASE_DB_SSLMODE\",\"require\"),connect_timeout=10)
print(\"OK\"); c.close()"'

Durable fix (secret-manager role — SOPS write)

The connection params live in secrets/monitoring.env.sops. Correct the username there so secrets-sync.yml deploys it everywhere:

SUPABASE_DB_USER=grafana_readonly.mwkqmgadqnkkihjdeqsi

Then re-run secrets-sync.yml (target vps-i1) and docker compose up -d pg-stats-exporter.

A sysadmin / infra-task worker must not write SOPS — route this to a secret-manager job.

Immediate mitigation (infra-task / sysadmin — clears the P2 now)

Editing the live .env restores the scrape in ~1 poll interval. This is temporary — the next secrets-sync.yml run overwrites .env from SOPS and reverts it, so the SOPS fix above is mandatory.

# PLAYBOOK: pg-stats-exporter-pooler-tenant-identifier.md
ssh -i ~/.ssh/vps_root_key root@217.154.82.162 \
  "sed -i 's/^SUPABASE_DB_USER=grafana_readonly$/SUPABASE_DB_USER=grafana_readonly.mwkqmgadqnkkihjdeqsi/' \
   /opt/p24-infra/monitoring/.env && cd /opt/p24-infra/monitoring && docker compose up -d pg-stats-exporter"

Verify:

$SSH_I1 'sleep 70; curl -s localhost:9201/metrics | grep -E "last_scrape_(rows|timestamp)"'
# last_scrape_rows > 0 and a non-zero timestamp = scraping again

Prevention / recommendation

Host / port / username are not secrets — only the password is. Keeping them in monitoring.env.sops lets a tenant-less username silently override the correct docker-compose.yml default. Recommend moving SUPABASE_DB_HOST/PORT/USER out of SOPS (or aligning the SOPS value with the compose default) so this drift cannot recur. Tracked alongside #3485.

Audit Log — Log to infra_operations

import sys; sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
log_op(
    actor="claude", op_type="config_change", resource="pg-stats-exporter",
    result="success",
    detail="Live .env SUPABASE_DB_USER corrected to add Supavisor tenant suffix; scrape restored. Durable SOPS fix routed to secret-manager.",
    env="vps-i1", gh_issue=3485,
)

Graph View

Backlinks