scripts/provision_roles.js

Rama main · réplica de lectura del repositorio original.

require("dotenv").config();

const { Client } = require("pg");

/**
 * Declarative provisioning for the Postgres roles the analytics backend uses.
 *
 * This manifest is the single source of truth for which role may touch which table.
 *
 * `grants` lists per-table privileges in the public schema. `schemaGrants` lists privileges
 * on every table of a schema, present and future; use it for schemas whose tables are created
 * at runtime by the application rather than by Prisma migrations (pg-boss).
 */
const ROLES = [
  {
    name: "siteconfig_ro",
    passwordEnv: "POSTGRES_SITECONFIG_RO_PASSWORD",
    // Read-only: the backend's site-config cache.
    grants: {
      SiteConfig: ["SELECT"],
      Dashboard: ["SELECT"],
      DashboardIntegration: ["SELECT"],
    },
  },
  {
    name: "monitoring_ro",
    passwordEnv: "POSTGRES_MONITORING_RO_PASSWORD",
    // Read-only: uptime monitor configuration.
    grants: {
      MonitorCheck: ["SELECT"],
      Dashboard: ["SELECT"],
    },
  },
  {
    name: "salts_rw",
    passwordEnv: "POSTGRES_SALTS_RW_PASSWORD",
    // Read-write: secret fingerprint salts.
    grants: {
      AnalyticsSalt: ["SELECT", "INSERT", "DELETE"],
    },
  },
  {
    name: "jobqueue_rw",
    passwordEnv: "POSTGRES_JOBQUEUE_RW_PASSWORD",
    // pg-boss migrations (scripts/migrate_pgboss.js) and queue creation on worker start add
    // tables over time, so the grant covers the whole schema including future tables.
    grants: {},
    schemaGrants: {
      pgboss: ["SELECT", "INSERT"],
    },
  },
];

// Postgres has no parameter binding for identifiers or for DDL like CREATE ROLE, so the
// few dynamic values are quoted by hand.
function quoteIdent(name) {
  return `"${String(name).replace(/"/g, '""')}"`;
}

function quoteLiteral(value) {
  return `'${String(value).replace(/'/g, "''")}'`;
}

async function provisionRole(client, dbName, role) {
  const password = process.env[role.passwordEnv];
  if (!password) {
    throw new Error(
      `${role.passwordEnv} must be set for role '${role.name}' (no fallback to POSTGRES_PASSWORD).`
    );
  }

  const roleIdent = quoteIdent(role.name);

  await client.query("BEGIN");
  try {
    const exists = await client.query(
      "SELECT 1 FROM pg_roles WHERE rolname = $1",
      [role.name]
    );

    if (exists.rowCount === 0) {
      await client.query(
        `CREATE ROLE ${roleIdent} LOGIN PASSWORD ${quoteLiteral(password)}`
      );
    } else {
      await client.query(
        `ALTER ROLE ${roleIdent} WITH LOGIN PASSWORD ${quoteLiteral(password)}`
      );
    }

    await client.query(
      `GRANT CONNECT ON DATABASE ${quoteIdent(dbName)} TO ${roleIdent}`
    );
    await client.query(`GRANT USAGE ON SCHEMA public TO ${roleIdent}`);

    await client.query(
      `REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ${roleIdent}`
    );
    for (const [table, privileges] of Object.entries(role.grants ?? {})) {
      await client.query(
        `GRANT ${privileges.join(", ")} ON TABLE ${quoteIdent(table)} TO ${roleIdent}`
      );
    }

    for (const [schema, privileges] of Object.entries(role.schemaGrants ?? {})) {
      const schemaIdent = quoteIdent(schema);
      const privs = privileges.join(", ");
      // Tables added later are created as the POSTGRES_URL user (the same user running this
      // script), so the default privileges declared here apply to them too.
      await client.query(`GRANT USAGE ON SCHEMA ${schemaIdent} TO ${roleIdent}`);
      await client.query(
        `GRANT ${privs} ON ALL TABLES IN SCHEMA ${schemaIdent} TO ${roleIdent}`
      );
      await client.query(
        `ALTER DEFAULT PRIVILEGES IN SCHEMA ${schemaIdent} GRANT ${privs} ON TABLES TO ${roleIdent}`
      );
    }

    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  }

  const summary = [
    ...Object.entries(role.grants ?? {}).map(
      ([table, privileges]) => `${table}(${privileges.join("/")})`
    ),
    ...Object.entries(role.schemaGrants ?? {}).map(
      ([schema, privileges]) => `schema ${schema}.*(${privileges.join("/")})`
    ),
  ].join(", ");

  console.log(`provision_roles: '${role.name}' -> ${summary}`);
}

async function main() {
  const databaseUrl = process.env.POSTGRES_URL;
  if (!databaseUrl) {
    console.error("provision_roles: POSTGRES_URL must be set in the environment.");
    process.exit(1);
  }

  let dbName;
  try {
    const url = new URL(databaseUrl);
    dbName = url.pathname.replace(/^\//, "").split("/")[0];
  } catch (error) {
    console.error(
      "provision_roles: Failed to parse POSTGRES_URL:",
      error instanceof Error ? error.message : String(error)
    );
    process.exit(1);
  }

  if (!dbName) {
    console.error(
      "provision_roles: Could not determine database name from POSTGRES_URL."
    );
    process.exit(1);
  }

  const client = new Client({ connectionString: databaseUrl });
  let hadError = false;

  try {
    await client.connect();
    for (const role of ROLES) {
      try {
        await provisionRole(client, dbName, role);
      } catch (error) {
        hadError = true;
        console.error(
          `provision_roles: failed for role '${role.name}':`,
          error instanceof Error ? error.message : String(error)
        );
      }
    }
  } catch (error) {
    console.error(
      "provision_roles: fatal error:",
      error instanceof Error ? error.message : String(error)
    );
    process.exit(1);
  } finally {
    try {
      await client.end();
    } catch {}
  }

  if (hadError) {
    process.exit(1);
  }
  console.log("provision_roles: all roles provisioned successfully.");
}

main();