scan-receipts/scripts/apply-db-permissions.mjs

#!/usr/bin/env node
/**
 * Apply scripts/db-permissions.sql to a PostgreSQL database.
 *
 * Connects with the OWNER / migration URL (DATABASE_URL from the environment,
 * .env.local or .env) because creating roles, setting default privileges and
 * revoking CREATE from PUBLIC require owner/superuser rights. The runtime role
 * it provisions (`receipt_app`) is the least-privilege role the application
 * should use at runtime — see scripts/db-permissions.sql for the full grant
 * list.
 *
 * Safe to re-run: the SQL file is idempotent. If APP_DATABASE_PASSWORD is set
 * (env or .env.local), it replaces the documented default password in the SQL
 * and appends an ALTER ROLE so an existing role's password is rotated too.
 *
 * Usage:
 *   node scripts/apply-db-permissions.mjs
 *   APP_DATABASE_PASSWORD=<secret> node scripts/apply-db-permissions.mjs
 */
import { readFileSync } from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { Client } from "pg";

const ROOT = path.join(path.dirname(fileURLToPath(import.meta.url)), "..");

const DEFAULT_RUNTIME_PASSWORD = "receipt_app_secure_password";

/** Minimal .env parser: strips quotes, ignores comments; first key wins. */
function parseEnvFile(filePath) {
  const out = {};
  let text;
  try {
    text = readFileSync(filePath, "utf8");
  } catch {
    return out;
  }
  for (const rawLine of text.split(/\r?\n/)) {
    const line = rawLine.trim();
    if (!line || line.startsWith("#")) continue;
    const eq = line.indexOf("=");
    if (eq < 1) continue;
    const key = line.slice(0, eq).trim();
    let value = line.slice(eq + 1).trim();
    if (
      (value.startsWith('"') && value.endsWith('"')) ||
      (value.startsWith("'") && value.endsWith("'"))
    ) {
      value = value.slice(1, -1);
    }
    if (!(key in out)) out[key] = value;
  }
  return out;
}

/** SQL single-quoted literal (doubles embedded quotes). */
function quoteLiteral(value) {
  return "'" + String(value).replace(/'/g, "''") + "'";
}

// Env precedence: process.env > .env.local > .env.
const fileEnv = { ...parseEnvFile(path.join(ROOT, ".env")), ...parseEnvFile(path.join(ROOT, ".env.local")) };

const ownerUrl = process.env.DATABASE_URL || fileEnv.DATABASE_URL;
if (!ownerUrl) {
  console.error("FATAL: DATABASE_URL not found. Set it in the environment, .env.local or .env.");
  process.exit(1);
}

const runtimePassword = process.env.APP_DATABASE_PASSWORD || fileEnv.APP_DATABASE_PASSWORD || null;

let sql = readFileSync(path.join(ROOT, "scripts", "db-permissions.sql"), "utf8");

// Optional password override: swap the documented default for the provided one
// and ensure the role's password matches APP_DATABASE_PASSWORD (ALTER ROLE is
// always appended when the variable is set, so re-runs converge — including
// rotating a previously custom password back to the default).
let passwordSource = "documented default (receipt_app_secure_password)";
if (runtimePassword) {
  if (runtimePassword === DEFAULT_RUNTIME_PASSWORD) {
    console.log("APP_DATABASE_PASSWORD equals the documented default - CREATE ROLE token unchanged, password confirmed via ALTER ROLE.");
  } else {
    const token = `PASSWORD '${DEFAULT_RUNTIME_PASSWORD}'`;
    if (!sql.includes(token)) {
      console.error("FATAL: could not locate the default password token in db-permissions.sql.");
      process.exit(1);
    }
    sql = sql.split(token).join(`PASSWORD ${quoteLiteral(runtimePassword)}`);
    passwordSource = "APP_DATABASE_PASSWORD (custom)";
  }
  sql +=
    `\n-- Password convergence appended by scripts/apply-db-permissions.mjs\n` +
    `ALTER ROLE receipt_app WITH PASSWORD ${quoteLiteral(runtimePassword)};\n`;
} else {
  console.log("APP_DATABASE_PASSWORD not set - role password left at the documented default from db-permissions.sql.");
}

const client = new Client({ connectionString: ownerUrl });
await client.connect();
try {
  console.log(`Connected as owner (${new URL(ownerUrl).username || "?"}) to execute db-permissions.sql ...`);
  await client.query(sql); // pg executes the multi-statement SQL in one round trip
  console.log("Applied scripts/db-permissions.sql successfully.");
  console.log("  runtime role : receipt_app (LOGIN, NO SUPERUSER, NO CREATEDB, NO CREATEROLE)");
  console.log(`  password     : ${passwordSource}`);
  console.log("  grants       : CONNECT, schema USAGE (no CREATE), table SELECT/INSERT/UPDATE/DELETE,");
  console.log("                 sequence USAGE/SELECT, matching DEFAULT PRIVILEGES for future objects,");
  console.log("                 PUBLIC CREATE on schema public revoked.");
} catch (err) {
  console.error("Failed to apply db-permissions.sql:", err.message);
  process.exitCode = 1;
} finally {
  await client.end();
}