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.
TypeScript ai-agentsbill-of-materialserpinventory-managementleanmanufacturingmanufacturing-execution-systemmcp-servermesmrpopen-source-erppostgresqlproduction-planningqmsquality-management-systemreact-routerself-hostedsupabasetraceabilitytypescript
// 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 { parse } from "libpg-query";
import type { Client } from "pg";
import type { Attachments } from "../event-system/attachments";
import { syncAttachments } from "../event-system/attachments-sync";
import { type Helper, loadHelpers, syncHelpers } from "./helpers";
import { type AnyRule, locate, type Manifest, render } from "./rules";
export type Policy = {
policyname: string;
permissive: string;
roles: string;
cmd: string;
qual: string | null;
with_check: string | null;
};
/**
* Per-company tables created at runtime (`searchIndex_<companyId>`, `auditLog_<companyId>`).
* The function that creates each one also creates its policies; sync leaves them alone.
*/
export const DYNAMIC_TABLE_PREFIXES = ["searchIndex_", "auditLog_"];
// Never committed: it only exists inside a savepoint that is always rolled back.
const SCRATCH = "authz_scratch";
const SYNC_LOCK = "carbon.authz.sync";
const rulesOf = (manifest: Manifest) =>
Object.entries(manifest) as [string, AnyRule][];
const ident = (name: string) => `"${name.replaceAll('"', '""')}"`;
export const readPolicies = async (db: Client, schema: string, table: string) =>
(
await db.query<Policy>(
`SELECT policyname, permissive, roles::text AS roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = $1 AND tablename = $2
ORDER BY policyname`,
[schema, table]
)
).rows;
const rlsEnabled = async (db: Client, schema: string, table: string) =>
(
await db.query<{ enabled: boolean }>(
`SELECT relrowsecurity AS enabled FROM pg_class
WHERE oid = to_regclass(format('%I.%I', $1::text, $2::text))`,
[schema, table]
)
).rows[0]?.enabled ?? false;
/**
* Rendered policy SQL may only create policies on the table it is rendered for. Checked
* with Postgres's own parser before anything runs, so a `custom` entry (or a rendering
* bug) cannot drop a table, grant a role, or touch another table's policies.
*/
export async function assertOnlyPolicies(
sql: string,
schema: string,
table: string
): Promise<void> {
// biome-ignore lint/suspicious/noExplicitAny: libpg-query's AST is untyped JSON
const tree: any = await parse(sql);
for (const { stmt } of tree.stmts ?? []) {
const target = stmt?.CreatePolicyStmt?.table;
if (target?.schemaname !== schema || target?.relname !== table) {
throw new Error(
`authz: the rule for "${table}" may only CREATE POLICY on ${schema}.${table}`
);
}
}
}
/**
* The policies each rule produces, as Postgres stores and prints them: rendered into a
* scratch copy of every table inside a savepoint that is always rolled back. Must run
* inside a transaction whose search_path is `public`, so both sides print identically.
*/
export async function desiredPolicies(db: Client, manifest: Manifest) {
// An Error when the rule cannot apply to the table (e.g. `company` on a table with no
// companyId): a savepoint per table keeps one bad rule from hiding the rest.
const desired = new Map<string, Policy[] | Error>();
await db.query("SAVEPOINT authz_scratch");
try {
await db.query(`CREATE SCHEMA ${ident(SCRATCH)}`);
for (const [key, rule] of rulesOf(manifest)) {
const { schema, table } = locate(key);
await db.query("SAVEPOINT authz_rule");
try {
await db.query(
`CREATE TABLE ${ident(SCRATCH)}.${ident(table)} (LIKE ${ident(schema)}.${ident(table)})`
);
const sql = render(rule, table, SCRATCH);
if (sql) {
await assertOnlyPolicies(sql, SCRATCH, table);
await db.query(sql);
}
desired.set(key, await readPolicies(db, SCRATCH, table));
// Dropped so an external table may share its name with a public one.
await db.query(`DROP TABLE ${ident(SCRATCH)}.${ident(table)}`);
await db.query("RELEASE SAVEPOINT authz_rule");
} catch (error) {
await db.query("ROLLBACK TO SAVEPOINT authz_rule");
desired.set(
key,
error instanceof Error ? error : new Error(String(error))
);
}
}
} finally {
await db.query("ROLLBACK TO SAVEPOINT authz_scratch");
}
return desired;
}
/**
* Run `work` in one transaction set up for authz: serialized against other syncs, public
* search_path, short lock timeout. Everything is transaction-scoped, so it behaves the
* same through a transaction-mode connection pooler.
*/
export async function inAuthzTransaction<T>(
db: Client,
work: () => Promise<T>,
{ commit }: { commit: boolean }
): Promise<T> {
await db.query("BEGIN");
try {
await db.query("SELECT pg_advisory_xact_lock(hashtext($1))", [SYNC_LOCK]);
await db.query("SET LOCAL search_path = public");
await db.query("SET LOCAL lock_timeout = '5s'");
const result = await work();
await db.query(commit ? "COMMIT" : "ROLLBACK");
return result;
} catch (error) {
await db.query("ROLLBACK");
throw error;
}
}
export type SyncResult = {
/** Helper functions whose live definition differed from helpers/<name>.sql. */
helpers: string[];
/** Tables whose live policies differed from the manifest (rewritten unless dryRun). */
changed: string[];
/** Public tables the manifest does not cover. */
unmanaged: string[];
/** Tables whose event triggers differed from attachments.ts. */
attachments: string[];
};
/**
* Make every manifest table's policies exactly what the manifest renders, in one
* transaction: every change lands or none does. A table that differs has every existing
* policy dropped (whatever its name), RLS enabled, and the rendered policies created.
*/
export async function syncAuthz(
db: Client,
manifest: Manifest,
{
dryRun = false,
helpers,
attachments
}: { dryRun?: boolean; helpers?: Helper[]; attachments?: Attachments } = {}
): Promise<SyncResult> {
const managedHelpers = helpers ?? (await loadHelpers());
return inAuthzTransaction(
db,
async () => {
// Helpers first: the policies below call them.
const changedHelpers = await syncHelpers(db, managedHelpers, { dryRun });
const desired = await desiredPolicies(db, manifest);
const invalid = [...desired].filter(([, want]) => want instanceof Error);
if (invalid.length) {
throw new Error(
`authz rules that do not apply (nothing was changed):\n${invalid
.map(([table, error]) => ` ${table}: ${(error as Error).message}`)
.join("\n")}`
);
}
const changed: string[] = [];
for (const [key, rule] of rulesOf(manifest)) {
const { schema, table } = locate(key);
const live = await readPolicies(db, schema, table);
const enabled = await rlsEnabled(db, schema, table);
// An external table's RLS switch belongs to its owner. Policies on a
// table without it would authorize nothing, so that is a failure.
if (schema !== "public" && !enabled) {
throw new Error(
`authz: ${key} does not have row level security enabled`
);
}
const same =
JSON.stringify(live) === JSON.stringify(desired.get(key)) && enabled;
if (same) continue;
changed.push(key);
if (dryRun) continue;
for (const policy of live) {
await db.query(
`DROP POLICY ${ident(policy.policyname)} ON ${ident(schema)}.${ident(table)}`
);
}
if (schema === "public") {
await db.query(
`ALTER TABLE public.${ident(table)} ENABLE ROW LEVEL SECURITY`
);
}
const sql = render(rule, table, schema);
if (sql) {
await assertOnlyPolicies(sql, schema, table);
await db.query(sql);
}
}
const unmanaged = (
await db.query<{ table: string }>(
`SELECT relname AS table FROM pg_class
WHERE relnamespace = 'public'::regnamespace AND relkind IN ('r', 'p')
AND NOT (relname = ANY ($1::text[]))
AND NOT (relname LIKE ANY ($2::text[]))
ORDER BY 1`,
[
Object.keys(manifest),
DYNAMIC_TABLE_PREFIXES.map((p) => `${p.replaceAll("_", "\\_")}%`)
]
)
).rows.map((r) => r.table);
// Last: the triggers call the functions synced above.
const changedAttachments = attachments
? await syncAttachments(db, attachments, { dryRun })
: [];
return {
helpers: changedHelpers,
changed,
unmanaged,
attachments: changedAttachments
};
},
{ commit: !dryRun }
);
}