schema/canonical-baseline-generator.test.ts
Rama main · réplica de lectura del repositorio original.
import assert from "node:assert/strict";
import test from "node:test";
import {
canonicalizePgDump,
extractHandwrittenAnnotations,
} from "../scripts/regenerate-canonical-baseline.mjs";
/**
* Every relation that can hold a secret is rebuilt from an explicit column
* list, so the fixture has to carry those curated views: the generator refuses
* a dump where a curated name is missing, widened to `select *`, or moved into
* the generic allowlist. `employee_payroll_profiles` is the one that actually
* regressed — it holds `sin_encrypted`.
*/
const curatedViews = ` create view openbooks_query.parties with (security_barrier=true) as
select id, org_id, display_name
from public.parties
where org_id = public.openbooks_query_org_id();
create view openbooks_query.party_bank_accounts with (security_barrier=true) as
select id, org_id, bank_name, account_last_four
from public.party_bank_accounts
where org_id = public.openbooks_query_org_id();
create view openbooks_query.vendor_roles with (security_barrier=true) as
select id, org_id, party_id, tin_last4
from public.vendor_roles
where org_id = public.openbooks_query_org_id();
create view openbooks_query.employee_payroll_profiles with (security_barrier=true) as
select id, org_id, employee_party_id, sin_last3
from public.employee_payroll_profiles
where org_id = public.openbooks_query_org_id();
create view openbooks_query.employee_roles with (security_barrier=true) as
select id, org_id, party_id, employee_number
from public.employee_roles
where org_id = public.openbooks_query_org_id();`;
const queryFunction = `CREATE FUNCTION public.openbooks_refresh_query_catalog() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
AS $_$
declare
safe_relations constant text[] := array[
'dunning_log', 'party_subsidiaries', 'pay_application_lines',
'pay_applications', 'transfer_order_lines', 'transfer_orders',
'vendor_pay_application_lines'
];
begin
${curatedViews}
end
$_$;`;
function syntheticDump(extra = "", fn = queryFunction) {
return `--
-- PostgreSQL database dump
--
-- Dumped from database version 16.9
-- Dumped by pg_dump version 16.9
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: openbooks_query; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA openbooks_query;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA public;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON SCHEMA public IS 'standard public schema';
--
-- Name: tenant_retirement; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA tenant_retirement;
${fn}
--
-- Name: orgs; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.orgs (id uuid);
CREATE TABLE public.pay_run_adjustments (id uuid);
CREATE TABLE public.pay_runs (id uuid);
CREATE TABLE public.auth_sessions (id uuid);
ALTER TABLE ONLY public.user_org_access FORCE ROW LEVEL SECURITY;
REVOKE ALL ON FUNCTION public.openbooks_refresh_query_catalog() FROM PUBLIC;
${extra}
--
-- PostgreSQL database dump complete
--
`;
}
test("canonical dump transformation is deterministic and restores reviewed payroll views", () => {
const first = canonicalizePgDump(syntheticDump());
const second = canonicalizePgDump(syntheticDump());
assert.equal(first, second);
assert.match(first, /^-- OpenBooks canonical PostgreSQL baseline\./);
assert.match(first, /CREATE EXTENSION IF NOT EXISTS pg_trgm/);
assert.match(first, /'employee_pay_components'/);
assert.match(first, /'pay_run_adjustments'/);
assert.match(first, /'payroll_filing_accounts'/);
assert.match(first, /'payroll_holidays'/);
assert.match(first, /'payroll_opening_balances'/);
assert.match(first, /'union_fringes'/);
assert.match(first, /SELECT public\.openbooks_refresh_query_catalog\(\);/);
assert.doesNotMatch(first, /\\restrict|\\unrestrict/);
assert.doesNotMatch(first, /CREATE SCHEMA public/);
assert.doesNotMatch(first, /SET row_security = off/);
assert.ok(first.endsWith("\n"));
});
test("restoring payroll relations never adds a curated relation to the generic allowlist", () => {
// employee_payroll_profiles holds sin_encrypted. The restore path used to
// list it alongside the generic payroll relations, which would have put the
// sealed SIN back on the `select *` path the curation removed it from.
const allowlist = canonicalizePgDump(syntheticDump()).match(
/safe_relations constant text\[\] := array\[([\s\S]*?)\n \];/,
)?.[1];
assert.ok(allowlist);
assert.doesNotMatch(allowlist, /'employee_payroll_profiles'/);
assert.match(allowlist, /'employee_pay_components'/);
});
test("canonical dump requires the pinned deterministic pg_dump source", () => {
assert.throws(
() => canonicalizePgDump(syntheticDump().replaceAll("16.9", "16.14")),
/pinned PostgreSQL 16\.9/,
);
});
test("canonical dump rejects auth query exposure", () => {
assert.throws(
() =>
canonicalizePgDump(
syntheticDump().replace("'dunning_log'", "'auth_sessions', 'dunning_log'"),
),
/authentication tables/,
);
});
test("canonical dump rejects a partial payroll query allowlist", () => {
assert.throws(
() => canonicalizePgDump(syntheticDump().replace("'dunning_log'", "'pay_runs', 'dunning_log'")),
/partially reviewed/,
);
});
test("a table-driven query catalog preserves registry membership without inventing a function allowlist", () => {
const relations = [
"pay_schedules",
"pay_components",
"pay_derived_rules",
"pay_run_adjustments",
"employee_pay_components",
"pay_runs",
"pay_stubs",
"pay_stub_lines",
"payroll_filing_accounts",
"payroll_holidays",
"payroll_opening_balance_components",
"payroll_opening_balances",
"union_agreements",
"union_classifications",
"union_fringes",
];
const tableDriven = queryFunction
.replace(/ safe_relations constant text\[\] := array\[[\s\S]*?\n \];/, "")
.replace(
"begin\n",
"begin\n for relation_name in select relation from public.openbooks_query_catalog_relations order by relation loop\n null;\n end loop;\n",
);
const result = canonicalizePgDump(syntheticDump("", tableDriven), {
registryRelations: relations,
});
assert.match(result, /from public\.openbooks_query_catalog_relations/);
assert.doesNotMatch(result, /safe_relations/);
assert.throws(
() =>
canonicalizePgDump(syntheticDump("", tableDriven), {
registryRelations: [...relations, "employee_payroll_profiles"],
}),
/curated query relation/,
);
assert.throws(
() =>
canonicalizePgDump(syntheticDump("", tableDriven), {
registryRelations: [...relations, "auth_sessions"],
}),
/authentication tables/,
);
assert.throws(
() =>
canonicalizePgDump(syntheticDump("", tableDriven), {
registryRelations: relations.filter((name) => name !== "pay_runs"),
}),
/missing: pay_runs/,
);
assert.throws(
() => canonicalizePgDump(syntheticDump(), { registryRelations: relations }),
/must read the module registry/,
);
});
test("stored functions may insert tenant rows while top-level tenant data remains forbidden", () => {
const functionSql =
"CREATE FUNCTION public.record_event() RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO public.audit_log DEFAULT VALUES; END $$;";
assert.doesNotThrow(() => canonicalizePgDump(syntheticDump(functionSql)));
assert.throws(
() =>
canonicalizePgDump(
syntheticDump(functionSql + "\nINSERT INTO public.audit_log DEFAULT VALUES;"),
),
/forbidden data statement/,
);
});
test("canonical dump rejects a curated relation on the generic allowlist", () => {
assert.throws(
() =>
canonicalizePgDump(
syntheticDump().replace("'dunning_log'", "'dunning_log', 'employee_payroll_profiles'"),
),
/curated query relation must not take the generic select \* path: employee_payroll_profiles/,
);
});
test("canonical dump rejects a curated view widened back to select *", () => {
const widened = queryFunction.replace(
" select id, org_id, employee_party_id, sin_last3\n from public.employee_payroll_profiles",
" select *\n from public.employee_payroll_profiles",
);
assert.throws(
() => canonicalizePgDump(syntheticDump("", widened)),
/rebuilt with select \*: employee_payroll_profiles/,
);
});
test("canonical dump rejects a curated view that names a secret column", () => {
const leaking = queryFunction.replace(
"select id, org_id, employee_party_id, sin_last3",
"select id, org_id, employee_party_id, sin_last3, sin_encrypted",
);
assert.throws(
() => canonicalizePgDump(syntheticDump("", leaking)),
/exposes a secret column: employee_payroll_profiles\.sin_encrypted/,
);
});
test("canonical dump rejects a curated relation with no explicit view at all", () => {
const dropped = queryFunction.replace(
/ create view openbooks_query\.employee_payroll_profiles[\s\S]*?;\n/,
"",
);
assert.throws(
() => canonicalizePgDump(syntheticDump("", dropped)),
/no explicit view: employee_payroll_profiles/,
);
});
test("canonical dump rejects environment-specific or data-bearing SQL", () => {
for (const [sql, expected] of [
["GRANT SELECT ON public.orgs TO openbooks_app;", /runtime-role ACL/],
["ALTER TABLE public.orgs OWNER TO postgres;", /owner assignment/],
["CREATE TABLE public._applied_migrations (name text);", /migration ledger DDL/],
["INSERT INTO public.orgs VALUES ('00000000-0000-0000-0000-000000000000');", /data statement/],
] as const) {
assert.throws(() => canonicalizePgDump(syntheticDump(sql)), expected);
}
});
test("hand-written prose survives a regeneration", () => {
// pg_dump cannot emit the design-rationale blocks the baseline carries, so
// they are lifted off the previous file and re-anchored on the same headers.
// Without this the round trip silently deletes documentation.
const previous = canonicalizePgDump(syntheticDump()).replace(
"--\n-- Name: orgs; Type: TABLE; Schema: public; Owner: -\n--\n\n",
"--\n-- Name: orgs; Type: TABLE; Schema: public; Owner: -\n--\n-- The tenant root.\n--\n\n",
);
const annotations = extractHandwrittenAnnotations(previous);
assert.deepEqual([...annotations.keys()], ["orgs; Type: TABLE; Schema: public; Owner: -"]);
const regenerated = canonicalizePgDump(syntheticDump(), { annotations });
assert.equal(regenerated, previous);
assert.equal(canonicalizePgDump(syntheticDump(), { annotations }), regenerated);
});
test("hand-written prose is never silently dropped when its object disappears", () => {
const annotations = new Map([
["retired_table; Type: TABLE; Schema: public; Owner: -", "-- Gone.\n"],
]);
assert.throws(
() => canonicalizePgDump(syntheticDump(), { annotations }),
/no object in this dump[\s\S]*retired_table/,
);
});
test("prose is restored after the content guards, so it cannot satisfy one", () => {
// A required marker written only in a comment must not pass the check.
const annotations = new Map([
[
"orgs; Type: TABLE; Schema: public; Owner: -",
"-- Mentions TABLESPACE and GRANT SELECT ... TO openbooks_app in prose.\n",
],
]);
const regenerated = canonicalizePgDump(syntheticDump(), { annotations });
assert.match(regenerated, /-- Mentions TABLESPACE and GRANT SELECT/);
});