schema/active-user-role-guard.integration.test.ts
Rama main · réplica de lectura del repositorio original.
import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import test from "node:test";
import { sql } from "drizzle-orm";
import { db, pool } from "@openbooks/engine/platform/db";
import {
createScratchOrg,
createScratchUser,
dropScratchOrg,
} from "@openbooks/engine/testing/fixtures";
const skip = !process.env.OPENBOOKS_DB_URL;
async function seed() {
const org = await createScratchOrg();
const userId = await createScratchUser(org.orgId, "Guard target", "guard_target");
const otherUserId = await createScratchUser(org.orgId, "Guard other", "guard_other");
const firstRole = (
await db.execute<{ id: string }>(sql`select id from app_roles
where org_id = ${org.orgId} and key = 'guard_target'`)
).rows[0]!.id;
const extraRole = (
await db.execute<{ id: string }>(sql`insert into app_roles(org_id, key, name, permissions)
values (${org.orgId}, 'guard_extra', 'Extra', '[]'::jsonb) returning id`)
).rows[0]!.id;
return { orgId: org.orgId, userId, otherUserId, firstRole, extraRole };
}
function code(error: unknown): string | undefined {
const value = error as { code?: string; cause?: { code?: string } };
return value.cause?.code ?? value.code;
}
for (const isolation of ["read committed", "repeatable read"] as const) {
test(
`database guard rejects concurrent removal of every role (${isolation})`,
{ skip },
async () => {
const f = await seed();
const first = await pool.connect();
const second = await pool.connect();
const gate = await pool.connect();
const trigger = `zz_role_guard_${randomUUID().replaceAll("-", "")}`;
const pending: Promise<unknown>[] = [];
let installed = false;
try {
await db.execute(
sql`insert into role_assignments(org_id, user_id, role_id) values (${f.orgId}, ${f.userId}, ${f.extraRole})`,
);
// Force an unlocked guard to let both commits reach this barrier. A
// locking guard serializes them or rejects a deadlock/stale snapshot.
await db.execute(
sql.raw(`create function ${trigger}() returns trigger language plpgsql as $$ begin
if OLD.org_id = '${f.orgId}'::uuid and OLD.user_id = '${f.userId}'::uuid then
perform pg_advisory_xact_lock(hashtextextended('${trigger}' || OLD.role_id::text, 0));
end if; return NULL; end $$`),
);
await db.execute(
sql.raw(`create constraint trigger ${trigger} after delete on role_assignments
deferrable initially deferred for each row execute function ${trigger}()`),
);
installed = true;
await gate.query("begin");
await gate.query(
"select pg_advisory_xact_lock(hashtextextended($1, 0)), pg_advisory_xact_lock(hashtextextended($2, 0))",
[trigger + f.firstRole, trigger + f.extraRole],
);
const pid = (await gate.query<{ pid: number }>("select pg_backend_pid() as pid")).rows[0]!
.pid;
await Promise.all(
[first, second].map(async (client, index) => {
await client.query(`begin isolation level ${isolation}`);
await client.query(
"select set_config('app.current_org', $1, true), set_config('app.bypass_rls', 'off', true)",
[f.orgId],
);
await client.query(
"delete from role_assignments where org_id = $1 and user_id = $2 and role_id = $3",
[f.orgId, f.userId, index === 0 ? f.firstRole : f.extraRole],
);
}),
);
let settled = 0;
pending.push(first.query("commit"), second.query("commit"));
for (const operation of pending)
void operation.then(
() => {
settled++;
},
() => {
settled++;
},
);
let reached = false;
for (let attempt = 0; attempt < 600; attempt++) {
const result = await pool.query<{ n: number }>(
"select count(*)::int as n from pg_stat_activity where $1::int = any(pg_blocking_pids(pid))",
[pid],
);
const blocked = result.rows[0]!.n;
if (blocked >= 2 || (blocked >= 1 && settled >= 1)) {
reached = true;
break;
}
await new Promise((resolve) => setTimeout(resolve, 10));
}
assert.ok(reached, "commits reached the controlled post-guard barrier");
await gate.query("commit");
const results = await Promise.allSettled(pending);
assert.equal(results.filter((result) => result.status === "fulfilled").length, 1);
const rejected = results.find((result) => result.status === "rejected");
assert.ok(rejected?.status === "rejected");
assert.ok(["23514", "40P01", "40001"].includes(code(rejected.reason) ?? ""));
const assignments = await db.execute(
sql`select id from role_assignments where org_id = ${f.orgId} and user_id = ${f.userId}`,
);
assert.equal(assignments.rows.length, 1);
const active = await db.execute(
sql`select is_active from users where org_id = ${f.orgId} and id = ${f.userId}`,
);
assert.equal(active.rows[0]!.is_active, true);
} finally {
await gate.query("rollback");
gate.release();
await Promise.allSettled(pending);
await first.query("rollback");
first.release();
await second.query("rollback");
second.release();
if (installed) await db.execute(sql.raw(`drop trigger ${trigger} on role_assignments`));
await db.execute(sql.raw(`drop function if exists ${trigger}()`));
await dropScratchOrg(f.orgId);
}
},
);
}
test(
"moving an assignment cannot strand its former active user, and controlled replacements remain valid",
{ skip },
async () => {
const f = await seed();
try {
const snapshot = async () =>
(
await db.execute(
sql`select * from role_assignments where org_id = ${f.orgId} order by id`,
)
).rows;
const before = await snapshot();
await assert.rejects(
db.transaction(async (tx) => {
await tx.execute(sql`update role_assignments set user_id = ${f.otherUserId}
where org_id = ${f.orgId} and user_id = ${f.userId}`);
}),
(error) => code(error) === "23514",
);
assert.deepEqual(await snapshot(), before);
await db.transaction(async (tx) => {
await tx.execute(
sql`insert into role_assignments(org_id, user_id, role_id) values (${f.orgId}, ${f.userId}, ${f.extraRole})`,
);
await tx.execute(sql`update role_assignments set user_id = ${f.otherUserId}
where org_id = ${f.orgId} and user_id = ${f.userId} and role_id = ${f.firstRole}`);
});
const remaining = await db.execute<{ role_id: string }>(
sql`select role_id from role_assignments where org_id = ${f.orgId} and user_id = ${f.userId}`,
);
assert.deepEqual(
remaining.rows.map((row) => row.role_id),
[f.extraRole],
);
// Ordinary metadata updates do not fire a removal check or change access.
await db.execute(
sql`update role_assignments set updated_at = now() where org_id = ${f.orgId} and user_id = ${f.userId}`,
);
await db.transaction(async (tx) => {
await tx.execute(
sql`update users set is_active = false where org_id = ${f.orgId} and id = ${f.userId}`,
);
await tx.execute(
sql`delete from role_assignments where org_id = ${f.orgId} and user_id = ${f.userId}`,
);
});
await assert.rejects(
db.execute(
sql`update users set is_active = true where org_id = ${f.orgId} and id = ${f.userId}`,
),
(error) => code(error) === "23514",
);
await db.transaction(async (tx) => {
await tx.execute(
sql`insert into role_assignments(org_id, user_id, role_id) values (${f.orgId}, ${f.userId}, ${f.extraRole})`,
);
await tx.execute(
sql`update users set is_active = true where org_id = ${f.orgId} and id = ${f.userId}`,
);
});
} finally {
await dropScratchOrg(f.orgId);
}
},
);