[object Object]

← back to Norma

security(TK-11783): strip hardcoded Google OAuth secrets from Norma Gmail-search scripts

746c031cb1ce9419a6ebc2323cc2d6730f3f49d2 · 2026-09-20 11:59:40 -0700 · Steve Abrams

client_secret (…FrL8) + refresh/access tokens (…hepo) were hardcoded in 3 tracked
scripts and re-committed nightly by the auto-data-snapshot cron (growing exposure).
Moved the values into the gitignored .env.local and switched the scripts to read
process.env.GOOGLE_CLIENT_SECRET / GOOGLE_ACCESS_TOKEN / GOOGLE_REFRESH_TOKEN via a
zero-dependency .env.local loader (no dotenv dep added), with fail-loud guards. No
`|| '<literal>'` fallback — the secret is fully out of tracked source.

- scripts/search-sacbee.js: client_secret + inline access/refresh tokens → env
- scripts/search-latimes-gmail.js: client_secret → env (tokens still from DB)
- scripts/search-kqed.mjs: client_secret → env (tokens still from DB; ESM loader)

Verified: git grep of the tracked tree = 0 literal occurrences; all 3 syntax-OK;
env loader resolves all 3 vars from .env.local (last4 matches removed values, so auth
is unchanged). Public CLIENT_ID left as-is (not a secret).

NOT DONE (stays gated in TK-11783): the secret is still in git HISTORY (history purge)
and the key itself is NOT rotated (Steve's console action). This is code hygiene only —
no rotation, no push, no deploy.

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

Files touched

Diff

commit 746c031cb1ce9419a6ebc2323cc2d6730f3f49d2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Sep 20 11:59:40 2026 -0700

    security(TK-11783): strip hardcoded Google OAuth secrets from Norma Gmail-search scripts
    
    client_secret (…FrL8) + refresh/access tokens (…hepo) were hardcoded in 3 tracked
    scripts and re-committed nightly by the auto-data-snapshot cron (growing exposure).
    Moved the values into the gitignored .env.local and switched the scripts to read
    process.env.GOOGLE_CLIENT_SECRET / GOOGLE_ACCESS_TOKEN / GOOGLE_REFRESH_TOKEN via a
    zero-dependency .env.local loader (no dotenv dep added), with fail-loud guards. No
    `|| '<literal>'` fallback — the secret is fully out of tracked source.
    
    - scripts/search-sacbee.js: client_secret + inline access/refresh tokens → env
    - scripts/search-latimes-gmail.js: client_secret → env (tokens still from DB)
    - scripts/search-kqed.mjs: client_secret → env (tokens still from DB; ESM loader)
    
    Verified: git grep of the tracked tree = 0 literal occurrences; all 3 syntax-OK;
    env loader resolves all 3 vars from .env.local (last4 matches removed values, so auth
    is unchanged). Public CLIENT_ID left as-is (not a secret).
    
    NOT DONE (stays gated in TK-11783): the secret is still in git HISTORY (history purge)
    and the key itself is NOT rotated (Steve's console action). This is code hygiene only —
    no rotation, no push, no deploy.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01A3PFQP7b2cFzSLAN6gB4ZB
---
 scripts/search-kqed.mjs         | 22 +++++++++++++++++++++-
 scripts/search-latimes-gmail.js | 20 +++++++++++++++++++-
 scripts/search-sacbee.js        | 29 ++++++++++++++++++++++++++---
 3 files changed, 66 insertions(+), 5 deletions(-)

diff --git a/scripts/search-kqed.mjs b/scripts/search-kqed.mjs
index 4dd2895..44d7a15 100644
--- a/scripts/search-kqed.mjs
+++ b/scripts/search-kqed.mjs
@@ -4,9 +4,29 @@
  */
 import { google } from 'googleapis';
 import pg from 'pg';
+import fs from 'fs';
+import path from 'path';
+import { fileURLToPath } from 'url';
+
+// Zero-dependency loader for the gitignored Norma/.env.local so OAuth creds resolve
+// from env instead of being hardcoded (TK-11783 secret-strip, 2026-09-20). Rotation stays Steve's.
+(function loadEnvLocal() {
+  try {
+    const dir = path.dirname(fileURLToPath(import.meta.url));
+    const envPath = path.join(dir, '..', '.env.local');
+    for (const line of fs.readFileSync(envPath, 'utf8').split('\n')) {
+      const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
+      if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
+    }
+  } catch { /* .env.local optional — vars may already be in the shell env */ }
+})();
 
 const CLIENT_ID = '462032529853-ef07ls3dgvm61l4esuefjofv8s1eimkk.apps.googleusercontent.com';
-const CLIENT_SECRET = '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';
+const CLIENT_SECRET = process.env.GOOGLE_CLIENT_SECRET;
+if (!CLIENT_SECRET) {
+  console.error('Missing GOOGLE_CLIENT_SECRET — set it in Norma/.env.local (see TK-11783).');
+  process.exit(1);
+}
 const REDIRECT_URI = 'http://localhost';
 const MAILBOX_EMAIL = 'natalia@studentdebtcrisis.org';
 
diff --git a/scripts/search-latimes-gmail.js b/scripts/search-latimes-gmail.js
index 81eef35..f68edd0 100644
--- a/scripts/search-latimes-gmail.js
+++ b/scripts/search-latimes-gmail.js
@@ -6,10 +6,28 @@
 
 const { google } = require('googleapis');
 const { Client } = require('pg');
+const fs = require('fs');
+const path = require('path');
+
+// Zero-dependency loader for the gitignored Norma/.env.local so OAuth creds resolve
+// from env instead of being hardcoded (TK-11783 secret-strip, 2026-09-20). Rotation stays Steve's.
+(function loadEnvLocal() {
+  try {
+    const envPath = path.join(__dirname, '..', '.env.local');
+    for (const line of fs.readFileSync(envPath, 'utf8').split('\n')) {
+      const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
+      if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
+    }
+  } catch { /* .env.local optional — vars may already be in the shell env */ }
+})();
 
 const DB_URL = (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/sdcc');
 const GMAIL_CLIENT_ID = '462032529853-ef07ls3dgvm61l4esuefjofv8s1eimkk.apps.googleusercontent.com';
-const GMAIL_CLIENT_SECRET = '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';
+const GMAIL_CLIENT_SECRET = process.env.GOOGLE_CLIENT_SECRET;
+if (!GMAIL_CLIENT_SECRET) {
+  console.error('Missing GOOGLE_CLIENT_SECRET — set it in Norma/.env.local (see TK-11783).');
+  process.exit(1);
+}
 const REDIRECT_URI = 'http://localhost';
 const MAILBOX_EMAIL = 'natalia@studentdebtcrisis.org';
 
diff --git a/scripts/search-sacbee.js b/scripts/search-sacbee.js
index 5b3ddb1..98484d0 100644
--- a/scripts/search-sacbee.js
+++ b/scripts/search-sacbee.js
@@ -3,15 +3,38 @@
  * Uses OAuth tokens from the Norma project's mailboxes table
  */
 const { google } = require('googleapis');
+const fs = require('fs');
+const path = require('path');
+
+// Zero-dependency loader for the gitignored Norma/.env.local so OAuth creds resolve
+// from env instead of being hardcoded (TK-11783 secret-strip, 2026-09-20). Rotation of
+// these keys stays Steve's console action; this only moves the value out of tracked source.
+(function loadEnvLocal() {
+  try {
+    const envPath = path.join(__dirname, '..', '.env.local');
+    for (const line of fs.readFileSync(envPath, 'utf8').split('\n')) {
+      const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
+      if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
+    }
+  } catch { /* .env.local optional — vars may already be in the shell env */ }
+})();
 
 const CLIENT_ID = '462032529853-ef07ls3dgvm61l4esuefjofv8s1eimkk.apps.googleusercontent.com';
-const CLIENT_SECRET = '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';
+const CLIENT_SECRET = process.env.GOOGLE_CLIENT_SECRET;
+if (!CLIENT_SECRET) {
+  console.error('Missing GOOGLE_CLIENT_SECRET — set it in Norma/.env.local (see TK-11783).');
+  process.exit(1);
+}
 
 const TOKENS = {
-  access_token: 'ya29.a0Aa7MYipxcXx34Wf05kyhwL9VJQGMXoqtYAo0AcOW0W8F8Gw6g6YV8zbCvXvu153ipwkWpjG-3M0zuulNtdS-dgnAAIogaw2fy3KLfjLbK2o_rYrJhf5_YIiKRfCZh0S2YYGncmOYjYZVXtQ7ZlHvJe5SspKE8G4j5V6YAVN6UYAIuYsoTrw6zNB7MiW43X_Y8MJTP5kFaCgYKAaUSARcSFQHGX2Mi_SCrAKRPthtGuGJskzAegw0207',
-  refresh_token: '1//06Gd4dnQNwn7yCgYIARAAGAYSNwF-L9IrpnkiK3NwY2bPziqhFlDa7hqSbyIh_XY_mdkwOJvrGuf452sRhFi-6Cni5VooiT_hepo',
+  access_token: process.env.GOOGLE_ACCESS_TOKEN,
+  refresh_token: process.env.GOOGLE_REFRESH_TOKEN,
   token_type: 'Bearer',
 };
+if (!TOKENS.refresh_token) {
+  console.error('Missing GOOGLE_REFRESH_TOKEN — set it in Norma/.env.local (see TK-11783).');
+  process.exit(1);
+}
 
 async function main() {
   const oauth2Client = new google.auth.OAuth2(CLIENT_ID, CLIENT_SECRET);

← 909b29d auto-data-snapshot: 2026-09-19T20:05:42 (1 data files) — age  ·  back to Norma  ·  auto-data-snapshot: 2026-09-21T08:49:26 (1 data files) — age f1ed42e →