Files

1019 lines
36 KiB
TypeScript

const PORTAL_APP_WORKER_ROLES = ["hivegrid_app", "hivegrid_worker"] as const;
export const PORTAL_APP_WORKER_DELTA_TABLES = [
"Portal",
"PortalActivityRoutingRule",
"PortalComplaint",
"PortalConfiguration",
"PortalDeletionRequest",
"PortalDocumentAutoReleaseRule",
"PortalEmailTemplate",
"PortalGdprExportJob",
"PortalNotificationPreference",
"PortalProjectAccess",
"PortalProjectDocument",
"PortalSession",
"PortalServiceInboxItem",
"PortalStageVisibility",
"PortalSubcontractorAccess",
"PortalToken",
"PortalUpload",
"PortalUser",
] as const;
const TABLE_PRIVILEGE_CANDIDATES = ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER"] as const;
const REQUIRED_DML_PRIVILEGES = ["SELECT", "INSERT", "UPDATE", "DELETE"] as const;
type PortalAppWorkerRole = (typeof PORTAL_APP_WORKER_ROLES)[number];
type PortalDeltaTable = (typeof PORTAL_APP_WORKER_DELTA_TABLES)[number];
type RequiredDmlPrivilege = (typeof REQUIRED_DML_PRIVILEGES)[number];
type SqlToken = {
kind: "identifier" | "number" | "string" | "symbol" | "word";
value: string;
};
export type PortalAppWorkerPolicyContract = {
tables: Array<{
table: PortalDeltaTable;
rowSecurity: true;
forceRowSecurity: true;
}>;
policies: Array<{
table: PortalDeltaTable;
policy: string;
permissive: boolean;
command: string;
roles: string[];
usingExpression: string | null;
withCheckExpression: string | null;
}>;
grants: Array<{
table: PortalDeltaTable;
role: PortalAppWorkerRole;
privilege: RequiredDmlPrivilege;
}>;
};
export type PortalAppWorkerPolicyState = {
tables: Array<{
table: string;
rowSecurity: boolean;
forceRowSecurity: boolean;
}>;
policies: Array<{
table: string;
policy: string;
permissive: boolean;
command: string;
roles: string[];
usingExpression: string | null;
withCheckExpression: string | null;
}>;
grants: Array<{
table: string;
role: string;
privilege: string;
}>;
columnGrants: Array<{
table: string;
column: string;
role: string;
privilege: string;
}>;
};
type RawQueryClient = {
$queryRawUnsafe<T = unknown>(query: string): Promise<T>;
};
const TARGET_TABLE_SET = new Set<string>(PORTAL_APP_WORKER_DELTA_TABLES);
const TARGET_ROLE_SET = new Set<string>(PORTAL_APP_WORKER_ROLES);
const TABLE_PRIVILEGE_SET = new Set<string>(TABLE_PRIVILEGE_CANDIDATES);
const REQUIRED_DML_PRIVILEGE_SET = new Set<string>(REQUIRED_DML_PRIVILEGES);
const GROUPING_PREDECESSOR_WORDS = new Set(["AND", "ELSE", "HAVING", "NOT", "ON", "OR", "THEN", "WHEN", "WHERE"]);
const POLICY_COMMANDS: Readonly<Record<string, string>> = Object.freeze({
ALL: "*",
DELETE: "d",
INSERT: "a",
SELECT: "r",
UPDATE: "w",
});
function sqlLiteral(value: string): string {
return `'${value.replaceAll("'", "''")}'`;
}
const TARGET_TABLE_VALUES_SQL = PORTAL_APP_WORKER_DELTA_TABLES.map((table) => `(${sqlLiteral(table)})`).join(",\n ");
const TARGET_TABLE_ARRAY_SQL = PORTAL_APP_WORKER_DELTA_TABLES.map(sqlLiteral).join(", ");
const TARGET_ROLE_VALUES_SQL = PORTAL_APP_WORKER_ROLES.map((role) => `(${sqlLiteral(role)})`).join(",\n ");
const TARGET_ROLE_ARRAY_SQL = PORTAL_APP_WORKER_ROLES.map(sqlLiteral).join(", ");
const PRIVILEGE_VALUES_SQL = TABLE_PRIVILEGE_CANDIDATES.map((privilege) => `(${sqlLiteral(privilege)})`).join(",\n ");
/**
* Parses the generated tenant isolation SQL rather than maintaining a second
* copy of its policy expressions. The parser deliberately accepts only the
* CREATE POLICY / table-GRANT grammar emitted by the generator.
*/
export function parsePortalAppWorkerPolicyContract(sql: string): PortalAppWorkerPolicyContract {
const statements = splitSqlStatements(sql);
const tableFlags = new Map<PortalDeltaTable, { enable: number; force: number }>(
PORTAL_APP_WORKER_DELTA_TABLES.map((table) => [table, { enable: 0, force: 0 }])
);
const policies = new Map<string, PortalAppWorkerPolicyContract["policies"][number]>();
const grants = new Map<string, PortalAppWorkerPolicyContract["grants"][number]>();
for (const statement of statements) {
const tokens = tokenizeSql(statement);
if (tokens.length === 0) continue;
if (isWord(tokens[0], "ALTER") && isWord(tokens[1], "TABLE")) {
parseTargetRlsAlter(tokens, tableFlags);
continue;
}
if (isWord(tokens[0], "CREATE") && isWord(tokens[1], "POLICY")) {
const policy = parseCreatePolicy(tokens);
if (!TARGET_TABLE_SET.has(policy.table)) continue;
const targetPolicy = {
...policy,
table: policy.table as PortalDeltaTable,
};
const key = `${targetPolicy.table}\u0000${targetPolicy.policy}`;
if (policies.has(key)) {
throw new Error(`Canonical tenant policy artifact duplicates ${targetPolicy.table}.${targetPolicy.policy}`);
}
policies.set(key, targetPolicy);
continue;
}
if (isWord(tokens[0], "GRANT")) {
for (const grant of parseTableGrant(tokens)) {
if (!TARGET_TABLE_SET.has(grant.table) || !TARGET_ROLE_SET.has(grant.role)) {
continue;
}
if (!REQUIRED_DML_PRIVILEGE_SET.has(grant.privilege)) {
throw new Error(`Canonical tenant policy artifact grants forbidden ${grant.privilege} on ${grant.table} to ${grant.role}`);
}
const targetGrant = {
table: grant.table as PortalDeltaTable,
role: grant.role as PortalAppWorkerRole,
privilege: grant.privilege as RequiredDmlPrivilege,
};
const key = `${targetGrant.table}\u0000${targetGrant.role}\u0000${targetGrant.privilege}`;
if (grants.has(key)) {
throw new Error(`Canonical tenant policy artifact duplicates ${targetGrant.privilege} on ${targetGrant.table} to ${targetGrant.role}`);
}
grants.set(key, targetGrant);
}
}
}
const tables = PORTAL_APP_WORKER_DELTA_TABLES.map((table) => {
const flags = tableFlags.get(table);
if (!flags || flags.enable !== 1 || flags.force !== 1) {
throw new Error(`Canonical tenant policy artifact must ENABLE and FORCE RLS exactly once for ${table}`);
}
return { table, rowSecurity: true as const, forceRowSecurity: true as const };
});
for (const policy of policies.values()) {
if (!policy.permissive) {
throw new Error(`Canonical tenant policy artifact unexpectedly declares restrictive policy ${policy.table}.${policy.policy}`);
}
if (!sameStringSet(policy.roles, PORTAL_APP_WORKER_ROLES)) {
throw new Error(`Canonical tenant policy artifact policy ${policy.table}.${policy.policy} must target exactly hivegrid_app and hivegrid_worker`);
}
}
for (const table of PORTAL_APP_WORKER_DELTA_TABLES) {
if (![...policies.values()].some((policy) => policy.table === table)) {
throw new Error(`Canonical tenant policy artifact has no policy for ${table}`);
}
for (const role of PORTAL_APP_WORKER_ROLES) {
for (const privilege of REQUIRED_DML_PRIVILEGES) {
const key = `${table}\u0000${role}\u0000${privilege}`;
if (!grants.has(key)) {
throw new Error(`Canonical tenant policy artifact is missing ${privilege} on ${table} to ${role}`);
}
}
}
}
return {
tables,
policies: [...policies.values()].sort(comparePolicy),
grants: [...grants.values()].sort(compareGrant),
};
}
export async function readPortalAppWorkerPolicyState(client: RawQueryClient): Promise<PortalAppWorkerPolicyState> {
const tableRows = await client.$queryRawUnsafe<unknown[]>(`
/* portal-app-worker-policy-parity:tables */
SELECT
class.relname::text AS table_name,
class.relrowsecurity AS row_security,
class.relforcerowsecurity AS force_row_security
FROM pg_class AS class
JOIN pg_namespace AS namespace ON namespace.oid = class.relnamespace
WHERE namespace.nspname = 'public'
AND class.relkind IN ('r', 'p')
AND class.relname = ANY (ARRAY[${TARGET_TABLE_ARRAY_SQL}]::text[])
ORDER BY class.relname
`);
const policyRows = await client.$queryRawUnsafe<unknown[]>(`
/* portal-app-worker-policy-parity:policies */
SELECT
class.relname::text AS table_name,
policy.polname::text AS policy_name,
policy.polpermissive AS permissive,
policy.polcmd::text AS command,
ARRAY(
SELECT CASE
WHEN policy_role.role_oid = 0 THEN 'PUBLIC'
ELSE role.rolname::text
END
FROM unnest(policy.polroles) AS policy_role(role_oid)
LEFT JOIN pg_roles AS role ON role.oid = policy_role.role_oid
ORDER BY 1
)::text[] AS roles,
pg_get_expr(policy.polqual, policy.polrelid, false) AS using_expression,
pg_get_expr(policy.polwithcheck, policy.polrelid, false) AS with_check_expression
FROM pg_policy AS policy
JOIN pg_class AS class ON class.oid = policy.polrelid
JOIN pg_namespace AS namespace ON namespace.oid = class.relnamespace
WHERE namespace.nspname = 'public'
AND class.relname = ANY (ARRAY[${TARGET_TABLE_ARRAY_SQL}]::text[])
AND EXISTS (
SELECT 1
FROM unnest(policy.polroles) AS policy_role(role_oid)
LEFT JOIN pg_roles AS role ON role.oid = policy_role.role_oid
WHERE policy_role.role_oid = 0
OR role.rolname = ANY (ARRAY[${TARGET_ROLE_ARRAY_SQL}]::text[])
OR EXISTS (
SELECT 1
FROM pg_roles AS runtime_role
WHERE runtime_role.rolname = ANY (ARRAY[${TARGET_ROLE_ARRAY_SQL}]::text[])
AND pg_has_role(runtime_role.oid, role.oid, 'MEMBER')
)
)
ORDER BY class.relname, policy.polname
`);
const privilegeRows = await client.$queryRawUnsafe<unknown[]>(`
/* portal-app-worker-policy-parity:privileges */
WITH target_table(table_name) AS (
VALUES
${TARGET_TABLE_VALUES_SQL}
),
runtime_role(role_name) AS (
VALUES
${TARGET_ROLE_VALUES_SQL}
),
candidate(privilege_type) AS (
VALUES
${PRIVILEGE_VALUES_SQL}
)
SELECT
target_table.table_name,
runtime_role.role_name,
candidate.privilege_type,
CASE
WHEN class.oid IS NULL OR role.oid IS NULL THEN false
ELSE has_table_privilege(role.oid, class.oid, candidate.privilege_type)
END AS granted
FROM target_table
CROSS JOIN runtime_role
CROSS JOIN candidate
LEFT JOIN pg_class AS class
ON class.relname = target_table.table_name
AND class.relnamespace = 'public'::regnamespace
AND class.relkind IN ('r', 'p')
LEFT JOIN pg_roles AS role ON role.rolname = runtime_role.role_name
ORDER BY target_table.table_name, runtime_role.role_name, candidate.privilege_type
`);
const columnGrantRows = await client.$queryRawUnsafe<unknown[]>(`
/* portal-app-worker-policy-parity:column-grants */
SELECT
class.relname::text AS table_name,
attribute.attname::text AS column_name,
CASE
WHEN access.grantee = 0 THEN 'PUBLIC'
ELSE grantee.rolname::text
END AS role_name,
access.privilege_type::text AS privilege_type
FROM pg_class AS class
JOIN pg_namespace AS namespace ON namespace.oid = class.relnamespace
JOIN pg_attribute AS attribute
ON attribute.attrelid = class.oid
AND attribute.attnum > 0
AND NOT attribute.attisdropped
CROSS JOIN LATERAL aclexplode(attribute.attacl) AS access
LEFT JOIN pg_roles AS grantee ON grantee.oid = access.grantee
WHERE namespace.nspname = 'public'
AND class.relkind IN ('r', 'p')
AND class.relname = ANY (ARRAY[${TARGET_TABLE_ARRAY_SQL}]::text[])
AND (
access.grantee = 0
OR grantee.rolname = ANY (ARRAY[${TARGET_ROLE_ARRAY_SQL}]::text[])
OR EXISTS (
SELECT 1
FROM pg_roles AS runtime_role
WHERE runtime_role.rolname = ANY (ARRAY[${TARGET_ROLE_ARRAY_SQL}]::text[])
AND pg_has_role(runtime_role.oid, grantee.oid, 'MEMBER')
)
)
ORDER BY class.relname, attribute.attname, role_name, access.privilege_type
`);
return {
tables: parseTableStateRows(tableRows),
policies: parsePolicyStateRows(policyRows),
grants: parsePrivilegeStateRows(privilegeRows),
columnGrants: parseColumnGrantStateRows(columnGrantRows),
};
}
export function assertPortalAppWorkerPolicyParity(contract: PortalAppWorkerPolicyContract, state: PortalAppWorkerPolicyState): void {
const diagnostics: string[] = [];
const expectedTables = new Map<string, PortalAppWorkerPolicyContract["tables"][number]>(contract.tables.map((table) => [table.table, table]));
const actualTables = uniqueMap(state.tables, (table) => table.table, "database table state");
for (const [table, expected] of expectedTables) {
const actual = actualTables.get(table);
if (!actual) {
diagnostics.push(`${table}:table_missing`);
continue;
}
if (actual.rowSecurity !== expected.rowSecurity) {
diagnostics.push(`${table}:rls_not_enabled`);
}
if (actual.forceRowSecurity !== expected.forceRowSecurity) {
diagnostics.push(`${table}:rls_not_forced`);
}
}
for (const table of actualTables.keys()) {
if (!expectedTables.has(table)) diagnostics.push(`${table}:unexpected_table_state`);
}
const expectedPolicies = uniqueMap(contract.policies, policyKey, "canonical policy contract");
const actualPolicies = uniqueMap(state.policies, policyKey, "database policy state");
for (const [key, expected] of expectedPolicies) {
const actual = actualPolicies.get(key);
const label = `${expected.table}.${expected.policy}`;
if (!actual) {
diagnostics.push(`${label}:policy_missing`);
continue;
}
if (actual.permissive !== expected.permissive) {
diagnostics.push(`${label}:permissiveness_mismatch`);
}
if (actual.command !== expected.command) {
diagnostics.push(`${label}:command_mismatch`);
}
if (!sameStringSet(actual.roles, expected.roles)) {
diagnostics.push(`${label}:roles_mismatch`);
}
if (actual.usingExpression !== expected.usingExpression) {
diagnostics.push(`${label}:using_expression_mismatch`);
}
if (actual.withCheckExpression !== expected.withCheckExpression) {
diagnostics.push(`${label}:with_check_expression_mismatch`);
}
}
for (const [key, actual] of actualPolicies) {
if (!expectedPolicies.has(key)) {
diagnostics.push(`${actual.table}.${actual.policy}:unexpected_policy`);
}
}
const expectedGrants = new Set(contract.grants.map(grantKey));
const actualGrants = new Set(state.grants.map(grantKey));
for (const grant of contract.grants) {
const key = grantKey(grant);
if (!actualGrants.has(key)) {
diagnostics.push(`${grant.table}:${grant.role}:${grant.privilege}:grant_missing`);
}
}
for (const grant of state.grants) {
const key = grantKey(grant);
if (!expectedGrants.has(key)) {
diagnostics.push(`${grant.table}:${grant.role}:${grant.privilege}:unexpected_grant`);
}
}
for (const grant of state.columnGrants) {
diagnostics.push(`${grant.table}.${grant.column}:${grant.role}:${grant.privilege}:unexpected_column_grant`);
}
if (diagnostics.length > 0) {
const detail = diagnostics.slice(0, 20).join(", ");
const suffix = diagnostics.length > 20 ? `, +${diagnostics.length - 20} more` : "";
throw new Error(`Portal app/worker policy parity failed: ${detail}${suffix}`);
}
}
export async function verifyPortalAppWorkerPolicyParity(client: RawQueryClient, canonicalArtifactSql: string): Promise<PortalAppWorkerPolicyState> {
const contract = parsePortalAppWorkerPolicyContract(canonicalArtifactSql);
const state = await readPortalAppWorkerPolicyState(client);
assertPortalAppWorkerPolicyParity(contract, state);
return state;
}
export function normalizePolicyExpression(expression: string, policyTable?: string): string {
return normalizeExpressionTokens(tokenizeSql(expression), policyTable);
}
function parseTargetRlsAlter(tokens: SqlToken[], flags: Map<PortalDeltaTable, { enable: number; force: number }>): void {
if (tokens.length < 3 || !isIdentifier(tokens[2])) return;
const table = tokens[2].value;
if (!TARGET_TABLE_SET.has(table)) return;
const suffix = tokens
.slice(3)
.map((token) => token.value.toUpperCase())
.join(" ");
const targetFlags = flags.get(table as PortalDeltaTable);
if (!targetFlags) throw new Error(`Unknown portal delta table ${table}`);
if (suffix === "ENABLE ROW LEVEL SECURITY") targetFlags.enable += 1;
if (suffix === "FORCE ROW LEVEL SECURITY") targetFlags.force += 1;
}
function parseCreatePolicy(tokens: SqlToken[]): {
table: string;
policy: string;
permissive: boolean;
command: string;
roles: string[];
usingExpression: string | null;
withCheckExpression: string | null;
} {
let index = 2;
const policy = consumeIdentifier(tokens, index, "policy name");
index += 1;
expectWord(tokens[index], "ON", "CREATE POLICY ON");
index += 1;
const table = consumeIdentifier(tokens, index, "policy table");
index += 1;
let permissive = true;
if (isWord(tokens[index], "AS")) {
index += 1;
if (isWord(tokens[index], "PERMISSIVE")) permissive = true;
else if (isWord(tokens[index], "RESTRICTIVE")) permissive = false;
else throw new Error(`Malformed CREATE POLICY ${table}.${policy}: invalid AS mode`);
index += 1;
}
expectWord(tokens[index], "FOR", `CREATE POLICY ${table}.${policy} FOR`);
index += 1;
const commandToken = tokens[index];
if (!commandToken || commandToken.kind !== "word") {
throw new Error(`Malformed CREATE POLICY ${table}.${policy}: missing command`);
}
const command = POLICY_COMMANDS[commandToken.value.toUpperCase()];
if (!command) {
throw new Error(`Malformed CREATE POLICY ${table}.${policy}: unsupported command`);
}
index += 1;
expectWord(tokens[index], "TO", `CREATE POLICY ${table}.${policy} TO`);
index += 1;
const roles: string[] = [];
while (index < tokens.length && !isWord(tokens[index], "USING") && !isWord(tokens[index], "WITH")) {
roles.push(consumeIdentifier(tokens, index, `${table}.${policy} role`));
index += 1;
if (tokens[index]?.value === ",") index += 1;
else if (index < tokens.length && !isWord(tokens[index], "USING") && !isWord(tokens[index], "WITH")) {
throw new Error(`Malformed CREATE POLICY ${table}.${policy}: invalid role list`);
}
}
if (roles.length === 0) {
throw new Error(`Malformed CREATE POLICY ${table}.${policy}: empty role list`);
}
let usingExpression: string | null = null;
let withCheckExpression: string | null = null;
if (isWord(tokens[index], "USING")) {
const clause = consumeParenthesized(tokens, index + 1, `${table}.${policy} USING`);
usingExpression = normalizeExpressionTokens(clause.tokens, table);
index = clause.nextIndex;
}
if (isWord(tokens[index], "WITH")) {
expectWord(tokens[index + 1], "CHECK", `CREATE POLICY ${table}.${policy} WITH CHECK`);
const clause = consumeParenthesized(tokens, index + 2, `${table}.${policy} WITH CHECK`);
withCheckExpression = normalizeExpressionTokens(clause.tokens, table);
index = clause.nextIndex;
}
if (index !== tokens.length) {
throw new Error(`Malformed CREATE POLICY ${table}.${policy}: unexpected trailing SQL`);
}
return {
table,
policy,
permissive,
command,
roles: [...roles].sort(),
usingExpression,
withCheckExpression,
};
}
function parseTableGrant(tokens: SqlToken[]): Array<{
table: string;
role: string;
privilege: string;
}> {
const onIndex = tokens.findIndex((token, index) => index > 0 && isWord(token, "ON"));
if (onIndex === -1) return [];
const toIndex = tokens.findIndex((token, index) => index > onIndex && isWord(token, "TO"));
if (toIndex === -1) {
if (tokens.some((token) => isIdentifier(token) && TARGET_TABLE_SET.has(token.value))) {
throw new Error("Malformed target-table GRANT: missing TO clause");
}
return [];
}
const objectType = tokens[onIndex + 1];
if (objectType?.kind === "word" && ["ALL", "DATABASE", "FUNCTION", "PROCEDURE", "SCHEMA", "SEQUENCE"].includes(objectType.value.toUpperCase())) {
return [];
}
const privilegeTokens = tokens.slice(1, onIndex);
const objectTokens = tokens.slice(onIndex + (isWord(objectType, "TABLE") ? 2 : 1), toIndex);
const roleTokens = tokens.slice(toIndex + 1);
const tables = consumeCommaSeparatedIdentifiers(objectTokens, "GRANT table list");
const touchesTarget = tables.some((table) => TARGET_TABLE_SET.has(table));
if (!touchesTarget) return [];
if (privilegeTokens.some((token) => token.value === "(" || token.value === ")")) {
throw new Error("Canonical tenant policy artifact uses unsupported column-level target grant");
}
const privileges = consumeCommaSeparatedIdentifiers(privilegeTokens, "GRANT privilege list").map((privilege) => privilege.toUpperCase());
const roles = consumeCommaSeparatedIdentifiers(roleTokens, "GRANT role list");
return tables.flatMap((table) => roles.flatMap((role) => privileges.map((privilege) => ({ table, role, privilege }))));
}
function consumeCommaSeparatedIdentifiers(tokens: SqlToken[], label: string): string[] {
if (tokens.length === 0) throw new Error(`Malformed ${label}: empty list`);
const values: string[] = [];
let expectValue = true;
for (const token of tokens) {
if (expectValue) {
if (!isIdentifier(token)) throw new Error(`Malformed ${label}: expected identifier`);
values.push(token.value);
expectValue = false;
} else {
if (token.value !== ",") throw new Error(`Malformed ${label}: expected comma`);
expectValue = true;
}
}
if (expectValue) throw new Error(`Malformed ${label}: trailing comma`);
return values;
}
function consumeParenthesized(tokens: SqlToken[], index: number, label: string): { tokens: SqlToken[]; nextIndex: number } {
if (tokens[index]?.value !== "(") throw new Error(`Malformed ${label}: missing opening parenthesis`);
let depth = 0;
for (let cursor = index; cursor < tokens.length; cursor += 1) {
if (tokens[cursor].value === "(") depth += 1;
if (tokens[cursor].value === ")") depth -= 1;
if (depth < 0) throw new Error(`Malformed ${label}: unbalanced parenthesis`);
if (depth === 0) {
return { tokens: tokens.slice(index + 1, cursor), nextIndex: cursor + 1 };
}
}
throw new Error(`Malformed ${label}: missing closing parenthesis`);
}
function normalizeExpressionTokens(input: SqlToken[], policyTable?: string): string {
const tokens: SqlToken[] = [];
for (let index = 0; index < input.length; index += 1) {
const token = input[index];
const previous = tokens.at(-1);
const next = input[index + 1];
// pg_get_expr elides the outer policy-table qualifier from a nullable
// column check even though it retains the qualifier for correlated
// references inside the following EXISTS. Canonicalize only that exact
// outer-table pattern; aliases and subquery ownership paths stay strict.
if (
policyTable &&
isIdentifier(token) &&
token.value === policyTable &&
next?.value === "." &&
isIdentifier(input[index + 2]) &&
isWord(input[index + 3], "IS") &&
isWord(input[index + 4], "NULL")
) {
tokens.push(input[index + 2]);
index += 2;
continue;
}
if (isWord(token, "AS") && previous && isIdentifier(previous) && next && isIdentifier(next)) {
continue;
}
if (token.value === "::" && previous?.kind === "string" && isWord(next, "TEXT")) {
index += 1;
continue;
}
tokens.push(token);
}
const normalized: string[] = [];
const parenthesisStack: boolean[] = [];
for (let index = 0; index < tokens.length; index += 1) {
const token = tokens[index];
if (token.value === "(") {
const previous = tokens[index - 1];
const structural = Boolean(previous && isIdentifier(previous) && !GROUPING_PREDECESSOR_WORDS.has(previous.value.toUpperCase()));
parenthesisStack.push(structural);
if (structural) normalized.push("(");
continue;
}
if (token.value === ")") {
const structural = parenthesisStack.pop();
if (structural === undefined) throw new Error("Policy expression has unbalanced parenthesis");
if (structural) normalized.push(")");
continue;
}
normalized.push(normalizeToken(token));
}
if (parenthesisStack.length > 0) {
throw new Error("Policy expression has unbalanced parenthesis");
}
if (normalized.length === 0) throw new Error("Policy expression must not be empty");
return JSON.stringify(normalized);
}
function normalizeToken(token: SqlToken): string {
if (token.kind === "word") return token.value.toLowerCase();
if (token.kind === "identifier") {
if (/^[a-z_][a-z0-9_$]*$/.test(token.value)) return token.value;
return `"${token.value.replaceAll('"', '""')}"`;
}
if (token.kind === "string") return JSON.stringify({ string: token.value });
return token.value;
}
function parseTableStateRows(rows: unknown[]): PortalAppWorkerPolicyState["tables"] {
if (!Array.isArray(rows)) throw new Error("Portal table-state probe returned a non-array result");
return rows.map((value) => {
const row = asRecord(value, "portal table-state row");
if (typeof row.table_name !== "string" || typeof row.row_security !== "boolean" || typeof row.force_row_security !== "boolean") {
throw new Error("Portal table-state probe returned an invalid row");
}
return {
table: row.table_name,
rowSecurity: row.row_security,
forceRowSecurity: row.force_row_security,
};
});
}
function parsePolicyStateRows(rows: unknown[]): PortalAppWorkerPolicyState["policies"] {
if (!Array.isArray(rows)) throw new Error("Portal policy-state probe returned a non-array result");
return rows.map((value) => {
const row = asRecord(value, "portal policy-state row");
if (
typeof row.table_name !== "string" ||
typeof row.policy_name !== "string" ||
typeof row.permissive !== "boolean" ||
typeof row.command !== "string" ||
!Array.isArray(row.roles) ||
!row.roles.every((role) => typeof role === "string") ||
(row.using_expression !== null && typeof row.using_expression !== "string") ||
(row.with_check_expression !== null && typeof row.with_check_expression !== "string")
) {
throw new Error("Portal policy-state probe returned an invalid row");
}
return {
table: row.table_name,
policy: row.policy_name,
permissive: row.permissive,
command: row.command,
roles: [...row.roles].sort(),
usingExpression: row.using_expression === null ? null : normalizePolicyExpression(row.using_expression, row.table_name),
withCheckExpression: row.with_check_expression === null ? null : normalizePolicyExpression(row.with_check_expression, row.table_name),
};
});
}
function parsePrivilegeStateRows(rows: unknown[]): PortalAppWorkerPolicyState["grants"] {
if (!Array.isArray(rows)) throw new Error("Portal privilege probe returned a non-array result");
const expectedRows = PORTAL_APP_WORKER_DELTA_TABLES.length * PORTAL_APP_WORKER_ROLES.length * TABLE_PRIVILEGE_CANDIDATES.length;
if (rows.length !== expectedRows) {
throw new Error(`Portal privilege probe returned ${rows.length} rows instead of ${expectedRows}`);
}
const seen = new Set<string>();
const grants: PortalAppWorkerPolicyState["grants"] = [];
for (const value of rows) {
const row = asRecord(value, "portal privilege row");
if (
typeof row.table_name !== "string" ||
typeof row.role_name !== "string" ||
typeof row.privilege_type !== "string" ||
typeof row.granted !== "boolean" ||
!TARGET_TABLE_SET.has(row.table_name) ||
!TARGET_ROLE_SET.has(row.role_name) ||
!TABLE_PRIVILEGE_SET.has(row.privilege_type)
) {
throw new Error("Portal privilege probe returned an invalid row");
}
const key = `${row.table_name}\u0000${row.role_name}\u0000${row.privilege_type}`;
if (seen.has(key)) throw new Error("Portal privilege probe returned a duplicate row");
seen.add(key);
if (row.granted) {
grants.push({
table: row.table_name,
role: row.role_name,
privilege: row.privilege_type,
});
}
}
return grants;
}
function parseColumnGrantStateRows(rows: unknown[]): PortalAppWorkerPolicyState["columnGrants"] {
if (!Array.isArray(rows)) {
throw new Error("Portal column-grant probe returned a non-array result");
}
const seen = new Set<string>();
return rows.map((value) => {
const row = asRecord(value, "portal column-grant row");
if (
typeof row.table_name !== "string" ||
typeof row.column_name !== "string" ||
typeof row.role_name !== "string" ||
typeof row.privilege_type !== "string" ||
!TARGET_TABLE_SET.has(row.table_name)
) {
throw new Error("Portal column-grant probe returned an invalid row");
}
const key = `${row.table_name}\u0000${row.column_name}\u0000${row.role_name}\u0000${row.privilege_type}`;
if (seen.has(key)) {
throw new Error("Portal column-grant probe returned a duplicate row");
}
seen.add(key);
return {
table: row.table_name,
column: row.column_name,
role: row.role_name,
privilege: row.privilege_type,
};
});
}
function asRecord(value: unknown, label: string): Record<string, unknown> {
if (!value || typeof value !== "object" || Array.isArray(value)) {
throw new Error(`${label} is not an object`);
}
return value as Record<string, unknown>;
}
function uniqueMap<T>(values: readonly T[], keyFor: (value: T) => string, label: string): Map<string, T> {
const result = new Map<string, T>();
for (const value of values) {
const key = keyFor(value);
if (result.has(key)) throw new Error(`${label} contains duplicate key ${key}`);
result.set(key, value);
}
return result;
}
function policyKey(value: { table: string; policy: string }): string {
return `${value.table}\u0000${value.policy}`;
}
function grantKey(value: { table: string; role: string; privilege: string }): string {
return `${value.table}\u0000${value.role}\u0000${value.privilege}`;
}
function comparePolicy(left: PortalAppWorkerPolicyContract["policies"][number], right: PortalAppWorkerPolicyContract["policies"][number]): number {
return policyKey(left).localeCompare(policyKey(right));
}
function compareGrant(left: PortalAppWorkerPolicyContract["grants"][number], right: PortalAppWorkerPolicyContract["grants"][number]): number {
return grantKey(left).localeCompare(grantKey(right));
}
function sameStringSet(left: readonly string[], right: readonly string[]): boolean {
return left.length === right.length && [...left].sort().every((value, index) => value === [...right].sort()[index]);
}
function consumeIdentifier(tokens: SqlToken[], index: number, label: string): string {
const token = tokens[index];
if (!isIdentifier(token)) throw new Error(`Malformed ${label}: expected identifier`);
return token.value;
}
function expectWord(token: SqlToken | undefined, word: string, label: string): void {
if (!isWord(token, word)) throw new Error(`Malformed ${label}`);
}
function isIdentifier(token: SqlToken | undefined): token is SqlToken {
return token?.kind === "identifier" || token?.kind === "word";
}
function isWord(token: SqlToken | undefined, word: string): boolean {
return token?.kind === "word" && token.value.toUpperCase() === word;
}
function tokenizeSql(sql: string): SqlToken[] {
const tokens: SqlToken[] = [];
let index = 0;
while (index < sql.length) {
const char = sql[index];
const next = sql[index + 1];
if (/\s/.test(char)) {
index += 1;
continue;
}
if (char === "-" && next === "-") {
index += 2;
while (index < sql.length && sql[index] !== "\n") index += 1;
continue;
}
if (char === "/" && next === "*") {
const end = sql.indexOf("*/", index + 2);
if (end === -1) throw new Error("Unterminated SQL block comment");
index = end + 2;
continue;
}
if (char === "'") {
const parsed = readQuoted(sql, index, "'", "SQL string literal");
tokens.push({ kind: "string", value: parsed.value });
index = parsed.nextIndex;
continue;
}
if (char === '"') {
const parsed = readQuoted(sql, index, '"', "SQL quoted identifier");
tokens.push({ kind: "identifier", value: parsed.value });
index = parsed.nextIndex;
continue;
}
if (char === "$") {
const tag = sql.slice(index).match(/^\$[A-Za-z_][A-Za-z0-9_]*\$|^\$\$/)?.[0];
if (tag) {
const end = sql.indexOf(tag, index + tag.length);
if (end === -1) throw new Error("Unterminated SQL dollar-quoted string");
tokens.push({
kind: "string",
value: sql.slice(index + tag.length, end),
});
index = end + tag.length;
continue;
}
}
const word = sql.slice(index).match(/^[A-Za-z_][A-Za-z0-9_$]*/)?.[0];
if (word) {
tokens.push({ kind: "word", value: word });
index += word.length;
continue;
}
const number = sql.slice(index).match(/^\d+(?:\.\d+)?/)?.[0];
if (number) {
tokens.push({ kind: "number", value: number });
index += number.length;
continue;
}
const operator = ["::", ">=", "<=", "<>", "!=", "||"].find((candidate) => sql.startsWith(candidate, index));
if (operator) {
tokens.push({ kind: "symbol", value: operator });
index += operator.length;
continue;
}
if ("(),.=*+-/<>[]".includes(char)) {
tokens.push({ kind: "symbol", value: char });
index += 1;
continue;
}
throw new Error(`Unsupported SQL token ${JSON.stringify(char)}`);
}
return tokens;
}
function readQuoted(sql: string, start: number, quote: "'" | '"', label: string): { value: string; nextIndex: number } {
let value = "";
let index = start + 1;
while (index < sql.length) {
if (sql[index] === quote) {
if (sql[index + 1] === quote) {
value += quote;
index += 2;
continue;
}
return { value, nextIndex: index + 1 };
}
if (sql[index] === "\\" && index + 1 < sql.length) {
value += sql[index];
value += sql[index + 1];
index += 2;
continue;
}
value += sql[index];
index += 1;
}
throw new Error(`Unterminated ${label}`);
}
function splitSqlStatements(sql: string): string[] {
const statements: string[] = [];
let start = 0;
let index = 0;
let singleQuoted = false;
let doubleQuoted = false;
let lineComment = false;
let blockCommentDepth = 0;
let dollarTag: string | null = null;
while (index < sql.length) {
if (lineComment) {
if (sql[index] === "\n") lineComment = false;
index += 1;
continue;
}
if (blockCommentDepth > 0) {
if (sql.startsWith("/*", index)) {
blockCommentDepth += 1;
index += 2;
} else if (sql.startsWith("*/", index)) {
blockCommentDepth -= 1;
index += 2;
} else index += 1;
continue;
}
if (dollarTag !== null) {
if (sql.startsWith(dollarTag, index)) {
index += dollarTag.length;
dollarTag = null;
} else index += 1;
continue;
}
if (singleQuoted) {
if (sql[index] === "'" && sql[index + 1] === "'") index += 2;
else if (sql[index] === "\\" && index + 1 < sql.length) index += 2;
else if (sql[index] === "'") {
singleQuoted = false;
index += 1;
} else index += 1;
continue;
}
if (doubleQuoted) {
if (sql[index] === '"' && sql[index + 1] === '"') index += 2;
else if (sql[index] === '"') {
doubleQuoted = false;
index += 1;
} else index += 1;
continue;
}
if (sql.startsWith("--", index)) {
lineComment = true;
index += 2;
continue;
}
if (sql.startsWith("/*", index)) {
blockCommentDepth = 1;
index += 2;
continue;
}
if (sql[index] === "'") {
singleQuoted = true;
index += 1;
continue;
}
if (sql[index] === '"') {
doubleQuoted = true;
index += 1;
continue;
}
if (sql[index] === "$") {
const match = sql.slice(index).match(/^\$[A-Za-z_][A-Za-z0-9_]*\$|^\$\$/)?.[0];
if (match) {
dollarTag = match;
index += match.length;
continue;
}
}
if (sql[index] === ";") {
const statement = sql.slice(start, index).trim();
if (statement) statements.push(statement);
start = index + 1;
}
index += 1;
}
if (singleQuoted || doubleQuoted || dollarTag !== null || blockCommentDepth > 0) {
throw new Error("Canonical tenant policy artifact contains unterminated SQL");
}
const tail = sql.slice(start).trim();
if (tail) statements.push(tail);
return statements;
}