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

#!/usr/bin/env node
/**
 * Verify the database least-privilege setup (scripts/db-permissions.sql).
 *
 * Connects twice:
 *   1. as the OWNER (DATABASE_URL from env, .env.local or .env) to inspect the
 *      role attributes and privilege catalog, and
 *   2. as the RUNTIME role `receipt_app` (APP_DATABASE_URL if set, otherwise
 *      derived from DATABASE_URL with user receipt_app and the runtime
 *      password) to prove the role can do what the app needs — and cannot do
 *      DDL.
 *
 * Prints one PASS/FAIL line per check and exits 1 if any check fails.
 *
 * Usage:
 *   node scripts/verify-db-permissions.mjs
 *   APP_DATABASE_URL=... node scripts/verify-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";
const RUNTIME_ROLE = "receipt_app";

let passed = 0;
let failed = 0;

function check(name, ok, detail = "") {
  if (ok) {
    passed++;
    console.log(`[PASS] ${name}${detail ? ` - ${detail}` : ""}`);
  } else {
    failed++;
    console.log(`[FAIL] ${name}${detail ? ` - ${detail}` : ""}`);
  }
}

/** 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;
}

// 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 || DEFAULT_RUNTIME_PASSWORD;

/** Runtime URL: APP_DATABASE_URL when provided, else derived from the owner URL. */
function runtimeUrl() {
  const explicit =
    process.env.APP_DATABASE_URL || fileEnv.APP_DATABASE_URL;
  if (explicit) return explicit;
  const u = new URL(ownerUrl);
  u.username = RUNTIME_ROLE;
  u.password = runtimePassword;
  return u.toString();
}

console.log(`Verifying least-privilege setup for role "${RUNTIME_ROLE}" ...\n`);

// ---------------------------------------------------------------------------
// 1) Owner connection: role attributes + privilege catalog
// ---------------------------------------------------------------------------
const owner = new Client({ connectionString: ownerUrl });
await owner.connect();

const roleRes = await owner.query(
  `SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
     FROM pg_roles WHERE rolname = $1`,
  [RUNTIME_ROLE]
);
if (roleRes.rows.length === 0) {
  check(`${RUNTIME_ROLE} exists`, false, "role not found");
  console.error("\nAborting: runtime role is missing. Run: node scripts/apply-db-permissions.mjs");
  await owner.end();
  process.exit(1);
}
const r = roleRes.rows[0];
check(`${RUNTIME_ROLE} exists with LOGIN`, r.rolcanlogin === true);
check(`${RUNTIME_ROLE} is NOT a superuser`, r.rolsuper === false);
check(`${RUNTIME_ROLE} has no CREATEDB`, r.rolcreatedb === false);
check(`${RUNTIME_ROLE} has no CREATEROLE`, r.rolcreaterole === false);

const privRes = await owner.query(
  `SELECT has_database_privilege($1, current_database(), 'CONNECT') AS db_connect,
          has_database_privilege($1, current_database(), 'TEMP')    AS db_temp,
          has_schema_privilege($1, 'public', 'USAGE')               AS schema_usage,
          has_schema_privilege($1, 'public', 'CREATE')              AS schema_create`,
  [RUNTIME_ROLE]
);
const p = privRes.rows[0];
check(`${RUNTIME_ROLE} has CONNECT on the database`, p.db_connect === true);
check(`${RUNTIME_ROLE} has USAGE on schema public`, p.schema_usage === true);
check(`${RUNTIME_ROLE} has NO CREATE on schema public (no DDL)`, p.schema_create === false);

const publicRes = await owner.query(
  `SELECT has_schema_privilege('public', 'public', 'CREATE') AS public_create`
);
check(
  "PUBLIC pseudo-role has NO CREATE on schema public (schema hardened)",
  publicRes.rows[0].public_create === false
);

// All current public tables: DML granted, DDL (TRUNCATE/REFERENCES) NOT granted.
const tableRes = await owner.query(
  `SELECT c.relname,
          has_table_privilege($1, c.oid, 'SELECT')    AS can_select,
          has_table_privilege($1, c.oid, 'INSERT')    AS can_insert,
          has_table_privilege($1, c.oid, 'UPDATE')    AS can_update,
          has_table_privilege($1, c.oid, 'DELETE')    AS can_delete,
          has_table_privilege($1, c.oid, 'TRUNCATE')  AS can_truncate,
          has_table_privilege($1, c.oid, 'REFERENCES') AS can_references
     FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND c.relkind = 'r'
    ORDER BY c.relname`,
  [RUNTIME_ROLE]
);
const missingDml = tableRes.rows.filter((t) => !(t.can_select && t.can_insert && t.can_update && t.can_delete));
check(
  `${RUNTIME_ROLE} has SELECT/INSERT/UPDATE/DELETE on ALL public tables`,
  missingDml.length === 0,
  `${tableRes.rows.length} table(s)` +
    (missingDml.length ? `; missing on: ${missingDml.map((t) => t.relname).join(", ")}` : "")
);
const grantedDdl = tableRes.rows.filter((t) => t.can_truncate || t.can_references);
check(
  `${RUNTIME_ROLE} has NO TRUNCATE/REFERENCES on tables (no DDL)`,
  grantedDdl.length === 0,
  grantedDdl.length ? `granted on: ${grantedDdl.map((t) => t.relname).join(", ")}` : `${tableRes.rows.length} table(s) checked`
);

// All current public sequences: USAGE/SELECT granted, UPDATE (setval) NOT granted.
const seqRes = await owner.query(
  `SELECT c.relname,
          has_sequence_privilege($1, c.oid, 'USAGE') AS can_usage,
          has_sequence_privilege($1, c.oid, 'SELECT') AS can_select,
          has_sequence_privilege($1, c.oid, 'UPDATE') AS can_update
     FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND c.relkind = 'S'
    ORDER BY c.relname`,
  [RUNTIME_ROLE]
);
const missingSeq = seqRes.rows.filter((s) => !(s.can_usage && s.can_select));
check(
  `${RUNTIME_ROLE} has USAGE/SELECT on ALL public sequences`,
  missingSeq.length === 0,
  `${seqRes.rows.length} sequence(s)` +
    (missingSeq.length ? `; missing on: ${missingSeq.map((s) => s.relname).join(", ")}` : "")
);
const updateSeq = seqRes.rows.filter((s) => s.can_update);
check(
  `${RUNTIME_ROLE} has NO UPDATE on sequences (no setval/rewind)`,
  updateSeq.length === 0,
  updateSeq.length ? `granted on: ${updateSeq.map((s) => s.relname).join(", ")}` : `${seqRes.rows.length} sequence(s) checked`
);

// Default privileges: future objects created by the owner get the same grants.
const defRes = await owner.query(
  `SELECT p.defaclobjtype, array_to_string(p.defaclacl, ',') AS acl
     FROM pg_default_acl p
     JOIN pg_namespace n ON n.oid = p.defaclnamespace
    WHERE n.nspname = 'public'
      AND pg_get_userbyid(p.defaclrole) = current_user`
);
const aclText = defRes.rows.map((x) => `${x.defaclobjtype}:${x.acl}`).join(" | ");
// Table default ACL is "receipt_app=arwd/<grantor>", sequence is "receipt_app=rU/<grantor>".
check(
  "DEFAULT PRIVILEGES grant DML to receipt_app for future tables",
  aclText.includes("receipt_app=arwd"),
  aclText || "no default ACLs recorded"
);
check(
  "DEFAULT PRIVILEGES grant USAGE/SELECT on future sequences",
  aclText.includes("receipt_app=rU"),
  aclText || "no default ACLs recorded"
);

// Owner must still be able to DDL (migrations/schema init unaffected).
try {
  await owner.query("CREATE TABLE public.__receipt_owner_ddl_check__(id integer)");
  await owner.query("DROP TABLE public.__receipt_owner_ddl_check__");
  check("owner role can still CREATE/DROP tables (migrations unaffected)", true, "CREATE + DROP in public succeeded");
} catch (err) {
  check("owner role can still CREATE/DROP tables (migrations unaffected)", false, err.message);
}

// ---------------------------------------------------------------------------
// 2) Runtime connection as receipt_app
// ---------------------------------------------------------------------------
const appUrl = runtimeUrl();
const app = new Client({ connectionString: appUrl });
let connected = false;
try {
  await app.connect();
  connected = true;
  check("receipt_app can connect with its own credentials", true, "(CONNECT granted; URL from APP_DATABASE_URL or derived from DATABASE_URL)");

  const me = await app.query("SELECT current_user AS u, current_database() AS db");
  check("connected session runs as receipt_app", me.rows[0].u === RUNTIME_ROLE, `current_user=${me.rows[0].u}, db=${me.rows[0].db}`);

  try {
    const cnt = await app.query("SELECT count(*) AS n FROM users");
    check("receipt_app can SELECT from users", true, `count(*) = ${cnt.rows[0].n}`);
  } catch (err) {
    check("receipt_app can SELECT from users", false, err.message);
  }

  // DDL attempt: must be denied (SQLSTATE 42501 = insufficient_privilege).
  try {
    await app.query("CREATE TABLE public.__receipt_app_perm_check__(id integer)");
    await app.query("DROP TABLE public.__receipt_app_perm_check__").catch(() => {});
    check("receipt_app CANNOT create tables (DDL denied)", false, "CREATE TABLE unexpectedly succeeded");
  } catch (err) {
    if (err.code === "42501") {
      check("receipt_app CANNOT create tables (DDL denied)", true, `SQLSTATE 42501 insufficient_privilege`);
    } else {
      check("receipt_app CANNOT create tables (DDL denied)", false, `unexpected error: ${err.message}`);
    }
  }
} catch (err) {
  check("receipt_app can connect with its own credentials", false, err.message);
} finally {
  if (connected) await app.end().catch(() => {});
}

await owner.end();

console.log(`\nResult: ${passed} passed, ${failed} failed`);
process.exit(failed > 0 ? 1 : 0);