spb/tools-spboucher-ai

1/** Creates/rotates the two application roles. Usage: DATABASE_URL_ADMIN=postgres://<superuser>@host/db pnpm db:roles [--rotate]2 *  Prints the generated passwords ONCE to stdout (write them into .env) — never stored anywhere else. */3import "./_env";4import postgres from "postgres";5import { randomBytes } from "node:crypto";6const admin = process.env.DATABASE_URL_ADMIN; if (!admin) { console.error("DATABASE_URL_ADMIN required (superuser/owner connection)"); process.exit(1); }7const sql = postgres(admin, { max: 1 });8const rotate = process.argv.includes("--rotate");9const pw = () => randomBytes(24).toString("base64url");10const out: Record<string, string> = {};11for (const role of ["tools_app", "tools_reader"]) {12  const exists = (await sql`SELECT 1 FROM pg_roles WHERE rolname=${role}`).length > 0;13  if (!exists) { const p = pw(); await sql.unsafe(`CREATE ROLE ${role} LOGIN PASSWORD '${p}' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT`); out[role] = p; }14  else if (rotate) { const p = pw(); await sql.unsafe(`ALTER ROLE ${role} PASSWORD '${p}'`); out[role] = p; }15}16await sql.unsafe(`ALTER ROLE tools_reader SET default_transaction_read_only = on`);17await sql.unsafe(`ALTER ROLE tools_reader SET statement_timeout = '5s'`);18await sql.unsafe(`ALTER ROLE tools_app SET statement_timeout = '60s'`);19const db = (await sql`SELECT current_database() AS d`)[0].d;20await sql.unsafe(`GRANT CONNECT ON DATABASE "${db}" TO tools_app, tools_reader`);21console.log(Object.keys(out).length ? JSON.stringify(out, null, 2) : "roles exist (use --rotate to rotate passwords)");22await sql.end();23