harden(db): psqlExecLocal throws on psql error (ON_ERROR_STOP=1)

fe9624d25797bca1b66ab930a502dc4b99ae58bb · 2026-05-29 12:36:17 -0700 · Steve Abrams

psqlExecLocal ran psql with no ON_ERROR_STOP, so a SQL error printed the
message but psql exited 0 — execSync didn't throw and the caller mistook the
failure for success. That masked the prod incident where all_designs rejected
every UPDATE (no replica identity, in an update-publishing publication) yet the
curator returned applied:N and persisted nothing.

Now the write path runs ON_ERROR_STOP=1 and throws a clean Error carrying
psql's stderr + exitCode. ON_ERROR_STOP stops on ERROR only (NOTICE/WARNING
pass), so boot-time CREATE ... IF NOT EXISTS is unaffected. Reads (psqlQuery)
left lenient. No DDL routes through psqlExecLocal (110 callers are DML in
try/catch'd handlers). Tested Mac2: success clean, missing-table/syntax throw
with the ERROR line, NOTICE no-throw, server boots, write persists+restores.

Files touched

M lib/db.js

Diff

commit fe9624d25797bca1b66ab930a502dc4b99ae58bb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri May 29 12:36:17 2026 -0700

    harden(db): psqlExecLocal throws on psql error (ON_ERROR_STOP=1)
    
    psqlExecLocal ran psql with no ON_ERROR_STOP, so a SQL error printed the
    message but psql exited 0 — execSync didn't throw and the caller mistook the
    failure for success. That masked the prod incident where all_designs rejected
    every UPDATE (no replica identity, in an update-publishing publication) yet the
    curator returned applied:N and persisted nothing.
    
    Now the write path runs ON_ERROR_STOP=1 and throws a clean Error carrying
    psql's stderr + exitCode. ON_ERROR_STOP stops on ERROR only (NOTICE/WARNING
    pass), so boot-time CREATE ... IF NOT EXISTS is unaffected. Reads (psqlQuery)
    left lenient. No DDL routes through psqlExecLocal (110 callers are DML in
    try/catch'd handlers). Tested Mac2: success clean, missing-table/syntax throw
    with the ERROR line, NOTICE no-throw, server boots, write persists+restores.
---
 lib/db.js | 34 ++++++++++++++++++++++++++++------
 1 file changed, 28 insertions(+), 6 deletions(-)

diff --git a/lib/db.js b/lib/db.js
index af44320..bce7582 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -38,13 +38,35 @@ function pgEsc(v) {
   return "'" + String(v).replace(/'/g, "''") + "'";
 }
 
-// Behavior-identical to psqlQuery (stdin-fed). Previously used `-c ${JSON.stringify(sql)}`
-// argv form, which broke any multi-line SQL: JSON.stringify converts real newlines into the
-// literal two characters `\n`, and psql then interprets `\n` as a backslash meta-command
-// (like `\d`, `\l`) — yielding `invalid command \nINSERT`. Stdin-feed avoids all shell
-// escaping and supports INSERT...RETURNING + multi-statement batches identically.
+// Stdin-fed write path (UPDATE/DELETE/INSERT...RETURNING/multi-statement).
+//
+// HARDENED 2026-05-29: runs psql with `ON_ERROR_STOP=1` so a SQL error makes
+// psql exit non-zero and we THROW. Without it, psql's default is to print the
+// error, exit 0, and the caller mistakes the failure for success. That false
+// success masked a real prod incident: `all_designs` rejected EVERY UPDATE for
+// lack of a replica identity (it was in an update-publishing publication), yet
+// the cactus curator kept returning `applied:N` and silently persisted nothing.
+// On error we surface psql's stderr (the actual `ERROR: ...` line) so callers —
+// most of which already sit in try/catch — get an actionable message and the
+// route returns a real 500 instead of a lie. `ON_ERROR_STOP` stops on ERROR
+// only, never on NOTICE/WARNING, so boot-time `CREATE ... IF NOT EXISTS`
+// (NOTICE: already exists) is unaffected. Reads (psqlQuery) stay lenient by
+// design. See feedback_cactus_curator_bulk_delete_forkstorm memory.
+const PSQL_CMD_STRICT = `${PSQL_CMD} -v ON_ERROR_STOP=1`;
 function psqlExecLocal(sql) {
-  return execSync(PSQL_CMD, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000 }).trim();
+  try {
+    return execSync(PSQL_CMD_STRICT, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000 }).trim();
+  } catch (e) {
+    const stderr = (e.stderr ? e.stderr.toString() : '').trim();
+    const stdout = (e.stdout ? e.stdout.toString() : '').trim();
+    const detail = (stderr || stdout || e.message || 'unknown psql error')
+      .split('\n').filter(Boolean).slice(0, 4).join(' | ').slice(0, 600);
+    const err = new Error(`psql write failed (exit ${e.status == null ? '?' : e.status}): ${detail}`);
+    err.isPsqlError = true;
+    err.psqlStderr = stderr;
+    err.exitCode = e.status;
+    throw err;
+  }
 }
 
 module.exports = {

← f6e3188 cactus-curator: haiku-vision chips + Fix-now heal pipeline w · back to Wallco Ai · cactus-curator: per-card Bad/Digital/Unpublish animate out + 0b5f501 →