404-capture path exhausts Postgres connection pool during scanner floods (EMAXCONN)

Sub-tickets

Questions

Activity

• wi-cli-venus created · 7w ago
• wi-cli-venus commented · 7w ago
  MECHANISM: Each scanner-probe 404 (and every demoted info/security 404) triggers a per-request appEvents INSERT that opens its own Postgres client connection. During a rapid scanner sweep (~20+ distinct probe paths in <1s) the 200-connection Supabase pool is exhausted -> the 404-capture writes themselves fail with [logEvent-failed] reason=(EMAXCONN) max client connections reached, limit: 200, AND real user requests in that window risk being starved of a connection. Confirmed off-baseline today. REPRO WINDOW (2026-06-29, prod runtime errors): 06:22 sweep (dpl_J9Yy9icqZV8EMHWhKCwRG4j4xiMf) + 07:32 sweep (dpl_FA86khp9aqW8HRmNYdsUMGcBLRwk). ~24 logEvent-failed groups, each count=1 user=1. SAMPLE ROUTES (scanner probes): /profiler/phpinfo, /admin/phpinfo.php, /portal/.env, /amazon/credentials, /data.sql, /db.sql.gz, /backup.sql.gz, /backup.zip /backup.tar.bz2, /sendgrid_backup.zip, /backup/sendgrid.zip, and ~14 docker-compose/helm variants (docker-compose.{prod,dev,override,local,production,yaml}.yml, app/ deploy/ backend/ infra/ infrastructure/ helm/values*.yaml). NOTE: MARS-340/341/342/345 scanner-DEMOTE work cuts PAGING but NOT the DB-write attempts — demoted info/security 404s still INSERT, so EMAXCONN persists. This is an availability risk, not paging noise. FIX DIRECTIONS (do NOT start — PM surfacing priority to Elazar): (A) CHEAPEST: skip the appEvents write entirely for SCANNER_PROBE_RE-demoted 404s. They are retained-silent already (below rail warn-floor), so dropping their DB row loses nothing operationally and removes the burst write pressure at the source. (B) STRUCTURAL: route all 404 logging through a pooled/batched writer (shared pooled connection or a queue) so a burst cannot open N raw connections. Filed at PM request. Audit owns this finding; not yet assigned for implementation.
• wi-cli-venus assigned · 7w ago
  coder-mars-cc-w / coder
• wi-cli-venus statusChanged · 7w ago
  Elazar greenlit. Coder-mars implements (picks mechanism: full skip / sampled write / pooled writer for SCANNER_PROBE_RE-demoted 404s). Audit (audit-mars-cc-w) does pre-push diff review + PTD. Review angle: (1) real non-scanner 404s keep logging unchanged; (2) chosen fix genuinely removes the per-hit connection, not just reduces count.
• wi-cli-venus commented · 7w ago
  Audit pre-push review: GREEN on logic. Verified (1) real/non-scanner 404s log unchanged (scannerProbe skip gated only on the SCANNER_PROBE_RE tier branch); (2) full-skip zeroes the DB conn (return precedes the only logEvent write; classifyNotFound DB-free for scanner paths incl non-uuid resource-prefix short-circuit); (3) zero queryable consumer — error-digest filters level IN(error,warn) so info scanner rows already excluded, grep finds no action='404'/category='security' reader. Held coder for 2 stale-comment fixes (MARS-340 'retained info/security' + persist:false comments now contradict the skip). Tradeoff accepted: permanent appEvents probe record -> 1h runtime-log line. PTD + live sweep re-exercise pending after push.
• wi-cli-venus completed · 7w ago
  Scanner-404 DB-write skip: full-skip appEvents write for SCANNER_PROBE_RE-demoted 404s (root cause = serverless cold-start conn storm exhausting 200-pool; sampling can't cross containers), forensic visibility kept via connection-free console.warn [scanner-404]. 833cec1/v2.22.23, PASS, live 2.22.24. Caveat: live re-exercise owed on next scanner sweep.