Skip to content

Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.

11.3 KB
Raw Blame
// SPDX-License-Identifier: AGPL-3.0-only
// Carbon (github.com/crbnos/carbon). Modified or adapted versions of this file,
// including ports, remain AGPLv3; serving them over a network requires releasing their source.

import { createHash } from "node:crypto";
import { readdirSync, readFileSync } from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { parse } from "libpg-query";
import type { Attachments } from "../event-system/attachments";
import { renderFor } from "../event-system/attachments-sync";
import { type Helper, helperKey, RETIRED_HELPERS } from "./helpers";
import { type AnyRule, locate, type Manifest, render } from "./rules";
import { assertOnlyPolicies } from "./sync";

/**
 * Production does not run `authz sync` yet, so a manifest or helper change reaches it only
 * through a migration rendered by `authz migration`. `unshipped()` (pinned by
 * migration.test.ts, which runs in CI with no database) fails any rule or helper that
 * no generated migration ships:
 *
 * - a table or helper in a generated migration must equal what the manifest renders now,
 *   as of the LATEST generated migration that names it;
 * - anything never shipped that way must hash to baseline.json — what production had when
 *   the manifest took over (text may differ; the meaning was proven equal). The baseline is
 *   written once and never regenerated: a change goes into a generated migration instead.
 *
 * A new table appears in neither, so it cannot reach production without its policies.
 */

const HERE = path.dirname(fileURLToPath(import.meta.url));
export const MIGRATIONS_DIR = path.resolve(HERE, "../../supabase/migrations");
const BASELINE = path.join(HERE, "baseline.json");

/**
 * The first line of every generated authz migration. `@carbon/checks`
 * (no-authz-ddl-in-migrations) exempts the files that start with it; `unshipped()` checks
 * their contents, so the header cannot be borrowed by a hand-written file.
 */
export const GENERATED_HEADER =
  "-- Generated by `pnpm --filter @carbon/database authz migration` from packages/database/src/authz. Do not edit.";

const LOCK_TIMEOUT = "SET lock_timeout = '5s';";

const literal = (s: string) => `'${s.replaceAll("'", "''")}'`;
const ident = (s: string) => `"${s.replaceAll('"', '""')}"`;
export const hash = (sql: string) =>
  createHash("sha256").update(sql).digest("hex").slice(0, 16);

// The public form is kept byte for byte: `readShipped` compares it with the text
// of every generated migration already in the repository.
const dropAllPolicies = (table: string, schema = "public") =>
  [
    "DO $authz$",
    "DECLARE p record;",
    "BEGIN",
    `  FOR p IN SELECT policyname FROM pg_policies WHERE schemaname = ${literal(schema)} AND tablename = ${literal(table)} LOOP`,
    schema === "public"
      ? `    EXECUTE format('DROP POLICY %I ON public.%I', p.policyname, ${literal(table)});`
      : `    EXECUTE format('DROP POLICY %I ON %I.%I', p.policyname, ${literal(schema)}, ${literal(table)});`,
    "  END LOOP;",
    "END",
    "$authz$;"
  ].join("\n");

/**
 * Chosen helpers and tables rendered from the manifest as one migration. Each table's
 * policies are dropped whatever their names (legacy names included) and recreated from its
 * rule, so the migration lands the manifest's exact rule; `authz sync` after it changes
 * nothing.
 */
export async function renderMigration(
  manifest: Manifest,
  helpers: Helper[],
  tables: string[],
  attached: { attachments: Attachments; tables: string[] } = {
    attachments: {},
    tables: []
  }
): Promise<string> {
  const parts = [
    GENERATED_HEADER,
    "-- Ships these rules to databases where `authz sync` does not run after migrations yet."
  ];

  for (const helper of helpers) parts.push(helper.sql.trim());

  for (const key of tables) {
    const rule = (manifest as Record<string, AnyRule>)[key];
    if (!rule)
      throw new Error(`authz migration: no rule for "${key}" in manifest.ts`);
    const { schema, table } = locate(key);
    const sql = render(rule, table, schema);
    if (sql) await assertOnlyPolicies(sql, schema, table);
    parts.push(
      [
        `-- ${key}`,
        dropAllPolicies(table, schema),
        // An external table's RLS switch belongs to its owner.
        schema === "public" &&
          `ALTER TABLE public.${ident(table)} ENABLE ROW LEVEL SECURITY;`,
        sql
      ]
        .filter(Boolean)
        .join("\n")
    );
  }

  // After the functions and policies: the triggers call the functions above.
  if (attached.tables.length) {
    parts.push(
      [
        "-- event triggers (packages/database/src/event-system/attachments.ts)",
        // Creating a trigger locks its table against writes. Behind a long
        // transaction it would wait, and every writer would queue behind it:
        // fail instead, and let the migration be run again.
        LOCK_TIMEOUT,
        ...attached.tables.map((table) =>
          renderFor(attached.attachments, table)
        ),
        "RESET lock_timeout;"
      ].join("\n")
    );
  }

  return `${parts.join("\n\n")}\n`;
}

// biome-ignore lint/suspicious/noExplicitAny: libpg-query's AST is untyped JSON
type Ast = any;

/** Each statement's AST and its own text (comments before it and the `;` excluded). */
async function statements(sql: string): Promise<{ node: Ast; text: string }[]> {
  const tree: Ast = await parse(sql);
  const bytes = Buffer.from(sql);
  return (tree.stmts ?? []).map((s: Ast) => {
    const start = s.stmt_location ?? 0;
    const end = s.stmt_len ? start + s.stmt_len : bytes.length;
    const text = bytes
      .subarray(start, end)
      .toString()
      .replace(/^(\s*--[^\n]*\n)+/, "")
      .trim();
    return { node: s.stmt, text };
  });
}

type Shipped = {
  tables: Map<string, { file: string; policies: string[] }>;
  helpers: Map<string, { file: string; text: string }>;
  /** Each table's last shipped `set_event_triggers` statement. */
  attachments: Map<string, string>;
  problems: string[];
};

const SET_EVENT_TRIGGERS = /^SELECT set_event_triggers\('((?:[^']|'')+)'[,)]/;

/**
 * What the generated migrations ship, latest file winning. A generated file may contain
 * only managed helpers and, per table, the drop-all block, ENABLE ROW LEVEL SECURITY and
 * policies on that table — anything else is reported.
 */
async function readShipped(dir: string, managed: Set<string>) {
  const shipped: Shipped = {
    tables: new Map(),
    helpers: new Map(),
    attachments: new Map(),
    problems: []
  };
  const files = readdirSync(dir)
    .filter((f) => f.endsWith(".sql"))
    .sort();
  for (const file of files) {
    const sql = readFileSync(path.join(dir, file), "utf8");
    if (!sql.startsWith(GENERATED_HEADER)) continue;
    // The manifest key of the block being read, and where that table lives.
    let table: string | undefined;
    let at = { schema: "public", table: "" };
    for (const { node, text } of await statements(sql)) {
      const attached = node.SelectStmt && SET_EVENT_TRIGGERS.exec(text);
      if (attached) {
        shipped.attachments.set(
          (attached[1] as string).replaceAll("''", "'"),
          `${text};`
        );
        continue;
      }
      if (node.VariableSetStmt?.name === "lock_timeout") continue;
      const fn = node.CreateFunctionStmt;
      const alter = node.AlterTableStmt;
      const policy = node.CreatePolicyStmt?.table;
      if (fn) {
        const [schema, name] = fn.funcname.map((n: Ast) => n.String?.sval);
        const key = schema === "public" ? name : `${schema}.${name}`;
        if (managed.has(key)) {
          shipped.helpers.set(key, { file, text });
          continue;
        }
        if (RETIRED_HELPERS[key] === file) continue;
      } else if (node.DoStmt) {
        const named =
          /schemaname = '((?:[^']|'')+)' AND tablename = '((?:[^']|'')+)'/.exec(
            text
          );
        table = undefined;
        if (named) {
          at = {
            schema: (named[1] as string).replaceAll("''", "'"),
            table: (named[2] as string).replaceAll("''", "'")
          };
          table =
            at.schema === "public" ? at.table : `${at.schema}.${at.table}`;
        }
        if (
          table &&
          text ===
            (await statements(dropAllPolicies(at.table, at.schema)))[0]?.text
        ) {
          // No ENABLE ROW LEVEL SECURITY follows for an external table, so
          // its block opens here.
          if (at.schema !== "public")
            shipped.tables.set(table, { file, policies: [] });
          continue;
        }
      } else if (alter) {
        const cmds = alter.cmds ?? [];
        if (
          table &&
          at.schema === "public" &&
          alter.relation?.schemaname === "public" &&
          alter.relation?.relname === table &&
          cmds.length === 1 &&
          cmds[0].AlterTableCmd?.subtype === "AT_EnableRowSecurity"
        ) {
          shipped.tables.set(table, { file, policies: [] });
          continue;
        }
      } else if (
        table &&
        policy?.schemaname === at.schema &&
        policy?.relname === at.table &&
        shipped.tables.get(table)?.file === file
      ) {
        shipped.tables.get(table)?.policies.push(text);
        continue;
      }
      shipped.problems.push(
        `${file}: not something \`authz migration\` writes: ${text.slice(0, 80)}`
      );
    }
  }
  return shipped;
}

export type Unshipped = {
  tables: string[];
  helpers: string[];
  /** Tables whose event triggers production does not have as attachments.ts declares them. */
  attachments: string[];
  problems: string[];
};

/** Every rule and helper production does not have yet, and any tampered generated file. */
export async function unshipped(
  manifest: Manifest,
  helpers: Helper[],
  dir = MIGRATIONS_DIR,
  attachments: Attachments = {}
): Promise<Unshipped> {
  const baseline: {
    tables: Record<string, string>;
    helpers: Record<string, string>;
  } = JSON.parse(readFileSync(BASELINE, "utf8"));
  const shipped = await readShipped(dir, new Set(helpers.map(helperKey)));
  const result: Unshipped = {
    tables: [],
    helpers: [],
    attachments: [],
    problems: shipped.problems
  };

  // A declared table must be shipped as declared; a table shipped once and no
  // longer declared must be shipped again, detached.
  const attachedTables = new Set([
    ...Object.keys(attachments),
    ...shipped.attachments.keys()
  ]);
  for (const table of [...attachedTables].sort()) {
    if (shipped.attachments.get(table) !== renderFor(attachments, table)) {
      // Never declared and never shipped attached: nothing to detach.
      if (!(table in attachments) && !shipped.attachments.has(table)) continue;
      result.attachments.push(table);
    }
  }

  for (const [table, rule] of Object.entries(manifest) as [string, AnyRule][]) {
    const at = locate(table);
    const sql = render(rule, at.table, at.schema);
    const got = shipped.tables.get(table);
    const same = got
      ? JSON.stringify(got.policies) ===
        JSON.stringify(sql ? (await statements(sql)).map((s) => s.text) : [])
      : baseline.tables[table] === hash(sql);
    if (!same) result.tables.push(table);
  }

  for (const helper of helpers) {
    const key = helperKey(helper);
    const got = shipped.helpers.get(key);
    const same = got
      ? got.text === (await statements(helper.sql))[0]?.text
      : baseline.helpers[key] === hash(helper.sql);
    if (!same) result.helpers.push(key);
  }

  return result;
}