← back to York Reprice 2026 08

Fix broken PGPASSWORD psql auth in 6 scripts; read dw_admin pw from secrets-manager/.env

b984417de33984c4dd868f51d566552f4bf53173 · 2026-09-15 18:24:41 -0700 · steve

The inline `PGPASSWORD=` prefix sent libpq an empty password (nothing exports
PGPASSWORD, and ~/.pgpass has no dw_unified/dw_admin line to fall back to), which
in bridge-reprice was swallowed and silently degraded pricing to MAP-only
(overpricing naturals). Now each script reads the real DW_ADMIN_PG_PASSWORD from
~/Projects/secrets-manager/.env (same pattern as SHOPIFY_ADMIN_TOKEN) and passes
it via execSync's env option (no shell-quoting risk). bridge-reprice now aborts
LOUDLY (console.error + exit 1) if the single-roll or valid-MAP DB load fails
instead of falling back to MAP-only. Removed a leftover hardcoded pw and repaired
a corrupted import line. Pricing math unchanged. Verified: node --check all 6 +
read-only `select 1` over TCP and the /tmp socket.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Pnon7dut9MfPekhhm9YY4Q

Files touched

• M bridge-reprice.mjs
• M build-rows-17.mjs
• M cadence/york-cadence.mjs
• M diag-brewster-images.mjs
• M recover-7-settlement.mjs
• M recover-images-17.mjs

Diff

commit b984417de33984c4dd868f51d566552f4bf53173
Author: steve <steve@designerwallcoverings.com>
Date:   Tue Sep 15 18:24:41 2026 -0700

    Fix broken PGPASSWORD psql auth in 6 scripts; read dw_admin pw from secrets-manager/.env
    
    The inline `PGPASSWORD=` prefix sent libpq an empty password (nothing exports
    PGPASSWORD, and ~/.pgpass has no dw_unified/dw_admin line to fall back to), which
    in bridge-reprice was swallowed and silently degraded pricing to MAP-only
    (overpricing naturals). Now each script reads the real DW_ADMIN_PG_PASSWORD from
    ~/Projects/secrets-manager/.env (same pattern as SHOPIFY_ADMIN_TOKEN) and passes
    it via execSync's env option (no shell-quoting risk). bridge-reprice now aborts
    LOUDLY (console.error + exit 1) if the single-roll or valid-MAP DB load fails
    instead of falling back to MAP-only. Removed a leftover hardcoded pw and repaired
    a corrupted import line. Pricing math unchanged. Verified: node --check all 6 +
    read-only `select 1` over TCP and the /tmp socket.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01Pnon7dut9MfPekhhm9YY4Q
---
 bridge-reprice.mjs       | 11 +++++++----
 build-rows-17.mjs        |  5 ++++-
 cadence/york-cadence.mjs |  5 ++++-
 diag-brewster-images.mjs |  6 +++++-
 recover-7-settlement.mjs |  5 ++++-
 recover-images-17.mjs    |  5 ++++-
 6 files changed, 28 insertions(+), 9 deletions(-)

diff --git a/bridge-reprice.mjs b/bridge-reprice.mjs
index 2b016b9..5935bdd 100644
--- a/bridge-reprice.mjs
+++ b/bridge-reprice.mjs
@@ -3,6 +3,9 @@ import { execSync } from 'node:child_process';
 const SHOP='designer-laboratory-sandbox.myshopify.com',VER='2024-10';
 const TOKEN=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1]?.trim();
 const URL=`https://${SHOP}/admin/api/${VER}/graphql.json`;
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN). Nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so the old inline `PGPASSWORD=` prefix sent libpq an empty password → auth failure swallowed → silent MAP-only overpricing of naturals.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env — cannot price safely, aborting.');process.exit(1);}
 const args=Object.fromEntries(process.argv.slice(2).map(a=>{const[k,v]=a.replace(/^--/,'').split('=');return[k,v===undefined?true:v];}));
 const APPLY=args.apply===true&&args['i-am-steve']===true;
 const INCLUDE_DOWN=args['include-down']===true;
@@ -18,19 +21,19 @@ for(const line of fs.readFileSync('data/master-priced-by-sku.csv','utf8').trim()
 // sell at brewster_york_master.roll_price_single, NOT MAP. Without this, master-priced-by-sku.csv (MAP-only)
 // false-flagged every correctly-priced natural as a reprice and would push it UP to MAP (135 of 279 on 2026-07-28).
 const RPS=new Map();
-try{ const out=execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -c "select upper(trim(mfr_sku))||chr(31)||roll_price_single from brewster_york_master where roll_price_single>0"`,{encoding:'utf8'});
+try{ const out=execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -c "select upper(trim(mfr_sku))||chr(31)||roll_price_single from brewster_york_master where roll_price_single>0"`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}});
   for(const line of out.trim().split('\n').filter(Boolean)){const [s,p]=line.split(String.fromCharCode(31)); if(!RPS.has(s))RPS.set(s,parseFloat(p));}
   console.log(`  single-roll targets loaded: ${RPS.size}`);
-}catch(e){ console.log('  ⚠ single-roll load failed (falling back to MAP-only):',String(e.message||e).slice(0,80)); }
+}catch(e){ console.error('  ✖ FATAL: single-roll (naturals) load failed — refusing to fall back to MAP-only (that would OVERPRICE naturals). Aborting.',String(e.message||e).slice(0,120)); process.exit(1); }
 // VALID-MAP SET (parity with york-cadence): york_master_aug2026 has dup rows per SKU (two roll sizes →
 // conflicting us_map, e.g. NW3503 carries BOTH $102 and $123.25). master-priced-by-sku.csv keeps only ONE
 // (first-wins), so bridge would push a correctly-priced live roll to the OTHER dup value. Skip when live
 // already matches ANY valid master MAP row.
 const VMAP=new Map();
-try{ const out=execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -c "select upper(trim(export_sku))||chr(31)||coalesce(us_map,round(us_msrp*0.75,2)) from york_master_aug2026 where export_sku is not null"`,{encoding:'utf8'});
+try{ const out=execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -c "select upper(trim(export_sku))||chr(31)||coalesce(us_map,round(us_msrp*0.75,2)) from york_master_aug2026 where export_sku is not null"`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}});
   for(const line of out.trim().split('\n').filter(Boolean)){const [s,p]=line.split(String.fromCharCode(31)); if(!VMAP.has(s))VMAP.set(s,new Set()); VMAP.get(s).add(parseFloat(p));}
   console.log(`  valid-MAP sets loaded: ${VMAP.size}`);
-}catch(e){ console.log('  ⚠ valid-MAP load failed (falling back to single CSV price):',String(e.message||e).slice(0,80)); }
+}catch(e){ console.error('  ✖ FATAL: valid-MAP (dup-MAP guard) load failed — refusing to fall back (that would mis-reprice dup-MAP SKUs). Aborting.',String(e.message||e).slice(0,120)); process.exit(1); }
 let cur=null, live=[];
 // JS catalog ~6,193 products; old 90×60=5,400 cap truncated the scan (missed ~793). 120×100 headroom.
 for(let pg=0;pg<120;pg++){
diff --git a/build-rows-17.mjs b/build-rows-17.mjs
index b2946a2..73e0a27 100644
--- a/build-rows-17.mjs
+++ b/build-rows-17.mjs
@@ -2,6 +2,9 @@
 // build-rows-17.mjs — pull the 17 clean onboard candidates from brewster_york_master (+ image) into
 // data/onboard-rows-17.json (exact phase1-enriched shape build-onboard-payloads expects). $0 local.
 import fs from 'node:fs'; import { execSync } from 'node:child_process';
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env');process.exit(1);}
 const cands = JSON.parse(fs.readFileSync('data-onboard17-candidates.json','utf8'));
 const skus = cands.map(c=>`'${String(c.mfr_sku).replace(/'/g,"''")}'`).join(',');
 const SEP=String.fromCharCode(31);
@@ -19,7 +22,7 @@ const sql=`select concat_ws(chr(31),
  left join york_catalog yc on upper(trim(yc.mfr_sku))=upper(trim(bym.mfr_sku))
  where upper(trim(bym.mfr_sku)) in (${skus})`;
 fs.writeFileSync('/tmp/_rows17.sql',sql);
-const out=execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -f /tmp/_rows17.sql`,{encoding:'utf8'}).trim();
+const out=execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -f /tmp/_rows17.sql`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();
 const num=x=>(x==null||x==='')?null:(isNaN(+x)?null:+x);
 const seen=new Set(); const rows=[];
 for(const line of out.split('\n').filter(Boolean)){
diff --git a/cadence/york-cadence.mjs b/cadence/york-cadence.mjs
index b2dd931..46ccb84 100644
--- a/cadence/york-cadence.mjs
+++ b/cadence/york-cadence.mjs
@@ -16,6 +16,9 @@ const NO_WRITE=process.env.YORK_CADENCE_NO_WRITE==='1'; // read-only test mode:
 const SHOP='designer-laboratory-sandbox.myshopify.com',VER='2024-10';
 const TOKEN=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1]?.trim();
 const URL=`https://${SHOP}/admin/api/${VER}/graphql.json`;
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env');process.exit(1);}
 // Connect over the /tmp UNIX socket (DW-standard canonical dw_unified path) instead of TCP
 // 127.0.0.1:5432. TCP 5432's listener flaps transiently (it went down Aug 15 → uncaught execSync
 // throw → cadence crash); the /tmp socket is always present. Keep -U dw_admin (md5/scram over the
@@ -24,7 +27,7 @@ const URL=`https://${SHOP}/admin/api/${VER}/graphql.json`;
 // multi-line and was silently throwing into a catch{}, falsely returning onboardReady=0 for
 // MONTHS. Route every query through a temp .sql file + `-f` so multi-line SQL is safe.
 // (All cadence queries concat_ws(chr(31),…) into ONE column, so -F is unnecessary.)
-const q=sql=>{fs.writeFileSync('/tmp/_yc_q.sql',sql);return execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h /tmp -U dw_admin -d dw_unified -tA -f /tmp/_yc_q.sql`,{encoding:'utf8'}).trim();};
+const q=sql=>{fs.writeFileSync('/tmp/_yc_q.sql',sql);return execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h /tmp -U dw_admin -d dw_unified -tA -f /tmp/_yc_q.sql`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();};
 const sleep=ms=>new Promise(r=>setTimeout(r,ms));
 const gql=async(query,v)=>{for(let a=0;a<8;a++){let j;try{const r=await fetch(URL,{method:'POST',headers:{'X-Shopify-Access-Token':TOKEN,'Content-Type':'application/json'},body:JSON.stringify({query,variables:v})});j=await r.json();}catch(e){await sleep(1500*(a+1));continue;}if(j.errors){if(JSON.stringify(j.errors).includes('THROTTLED')){await sleep(2000*(a+1));continue;}throw new Error(JSON.stringify(j.errors));}const t=j.extensions?.cost?.throttleStatus;if(t&&t.currentlyAvailable<300)await sleep(1500);return j.data;}throw new Error('retries');};
 const norm=s=>String(s||'').toUpperCase().trim();
diff --git a/diag-brewster-images.mjs b/diag-brewster-images.mjs
index 07f96f0..316d579 100644
--- a/diag-brewster-images.mjs
+++ b/diag-brewster-images.mjs
@@ -2,7 +2,11 @@
 // diag-brewster-images.mjs — TK-10058: characterize brewster_catalog.image_url cross-link.
 // Token-overlap (not substring), accent-normalized, to avoid false pos/neg. READ-ONLY. $0 local.
 import { execSync } from 'node:child_process'; import fs from 'node:fs';
-const q=sql=>{fs.writeFileSync('/tmp/_bdiag.sql',sql);return execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'\x1f' -f /tmp/_bdiag.sql`,{encoding:'utf8'}).trim();};
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env');process.exit(1);}
+
+const q=sql=>{fs.writeFileSync('/tmp/_bdiag.sql',sql);return execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'\x1f' -f /tmp/_bdiag.sql`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();};
 const norm=s=>String(s||'').normalize('NFD').replace(/[̀-ͯ]/g,'').toLowerCase();
 const STOP=new Set(['wallpaper','wallcovering','the','and','of','light','dark','off','white','grey','gray','black','blue','green','red','pink','gold','silver','neutral','beige','cream','taupe','sand','natural','yellow','orange','purple','brown','stone','ivory','coral','moss','wheat','denim','rose']);
 const toks=s=>norm(s).replace(/[^a-z0-9]+/g,' ').split(/\s+/).filter(w=>w.length>2 && !STOP.has(w));
diff --git a/recover-7-settlement.mjs b/recover-7-settlement.mjs
index 53a5ff0..6881ba6 100644
--- a/recover-7-settlement.mjs
+++ b/recover-7-settlement.mjs
@@ -2,7 +2,10 @@
 // recover-7-settlement.mjs — TK-10058: for the 7 settlement-risk LIVE products, recover the CORRECT
 // image by name-slug from brewster_catalog (their own row is corrupt), download for eyeball. READ-ONLY. $0.
 import fs from 'node:fs'; import { execSync } from 'node:child_process';
-const q=sql=>{fs.writeFileSync('/tmp/_r7.sql',sql);return execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'\x1f' -f /tmp/_r7.sql`,{encoding:'utf8'}).trim();};
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env');process.exit(1);}
+const q=sql=>{fs.writeFileSync('/tmp/_r7.sql',sql);return execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'\x1f' -f /tmp/_r7.sql`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();};
 const norm=s=>String(s||'').normalize('NFD').replace(/[̀-ͯ]/g,'').toLowerCase();
 const STOP=new Set(['wallpaper','wallcovering','residential','wall','the','and','light','dark','off']);
 const toks=s=>norm(s).replace(/[^a-z0-9]+/g,' ').split(/\s+/).filter(w=>w.length>2&&!STOP.has(w));
diff --git a/recover-images-17.mjs b/recover-images-17.mjs
index 4961cd9..c89be85 100644
--- a/recover-images-17.mjs
+++ b/recover-images-17.mjs
@@ -4,9 +4,12 @@
 // product_name slug against the image FILENAME slug. Writes corrected image_url into onboard-rows-17.json.
 // $0 local. Does NOT write to brewster_catalog (that DB-level relink is a separate gated job).
 import fs from 'node:fs'; import { execSync } from 'node:child_process';
+// PG auth: read dw_admin's real password from secrets-manager/.env (same source as SHOPIFY_ADMIN_TOKEN); nothing exports PGPASSWORD and ~/.pgpass has no dw_unified/dw_admin line, so an inline empty PGPASSWORD= would silently fail auth.
+const PGPW=(fs.readFileSync(process.env.HOME+'/Projects/secrets-manager/.env','utf8').match(/^DW_ADMIN_PG_PASSWORD=(.+)$/m)||[])[1]?.trim();
+if(!PGPW){console.error('FATAL: DW_ADMIN_PG_PASSWORD missing from ~/Projects/secrets-manager/.env');process.exit(1);}
 const slug=s=>String(s||'').toLowerCase().replace(/[^a-z0-9]+/g,'-').replace(/^-|-$/g,'').replace(/-wallpaper$/,'');
 // load every brewster_catalog image once: filename-slug -> url
-const out=execSync(`PGPASSWORD=DW2024! /opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'|' -c "select image_url from brewster_catalog where image_url is not null and image_url<>''"`,{encoding:'utf8'}).trim();
+const out=execSync(`/opt/homebrew/opt/postgresql@14/bin/psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA -F'|' -c "select image_url from brewster_catalog where image_url is not null and image_url<>''"`,{encoding:'utf8',env:{...process.env,PGPASSWORD:PGPW}}).trim();
 const byslug=new Map();
 for(const url of out.split('\n').filter(Boolean)){
   const fn=url.split('/').pop().replace(/^\d+_/,'').replace(/\.[a-z]+$/i,''); // strip leading id_ and ext

← b9f39ac auto-data-snapshot: 2026-09-15T01:48:50 (1 data files) — cad  ·  back to York Reprice 2026 08  ·  Fix two reproduced defects: silent-degrade in york-cadence + a908d02 →