Files

162 lines
7.8 KiB
TypeScript

// @vitest-environment node
import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import pg from "pg";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
// Resolve the repo root from this file's location so the SQL artifacts load
// regardless of the cwd vitest runs under (root vs the @hivegrid/web package dir).
// This file lives at apps/web/app/utils/db/.server/ → six segments below root.
const REPO_ROOT = path.resolve(path.dirname(fileURLToPath(import.meta.url)), "../../../../../..");
// AK-P4b — TASK RLS GATE PROOF. Companion to R-P2b's rls-tenant-isolation and
// AK-P4a's rls-file-isolation integration tests: proves that once the Task RLS
// block (prisma/rls/task-isolation.sql) is applied, the restricted hivegrid_app
// role (NOSUPERUSER, NOBYPASSRLS) is confined to its own tenant's Task /
// TaskWatcher / EntityLink rows, sees nothing without a tenant context, and is
// blocked by WITH CHECK from writing cross-tenant rows. This is what makes the P5
// activation a pure switch: the wrapped taskService/entityLinkService and worker
// crons already set the GUC, so flipping RLS on cannot break Tasks.
//
// Runs ONLY when RLS_TEST_DATABASE_URL is set (a SUPERUSER connection to a
// throwaway DB with the schema pushed). Without it the block is skipped so the
// normal unit suite stays green without a DB.
//
// Setup:
// docker run -d --name hg-rls-pg -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=hivegrid -p 5433:5432 pgvector/pgvector:pg16
// DIRECT_URL=postgresql://postgres:postgres@localhost:5433/hivegrid pnpm exec prisma db push
// RLS_TEST_DATABASE_URL=postgresql://postgres:postgres@localhost:5433/hivegrid pnpm vitest run rls-task-isolation
const ADMIN_URL = process.env.RLS_TEST_DATABASE_URL;
const APP_ROLE = "hivegrid_app";
const APP_PASSWORD = "hivegrid_app_pw";
function appUrlFrom(adminUrl: string): string {
const url = new URL(adminUrl);
url.username = APP_ROLE;
url.password = APP_PASSWORD;
return url.toString();
}
const describeIfDb = ADMIN_URL ? describe : describe.skip;
describeIfDb("Task RLS tenant isolation gate proof (Task, TaskWatcher, EntityLink)", () => {
let admin: pg.Client;
let app: pg.Client;
beforeAll(async () => {
admin = new pg.Client({ connectionString: ADMIN_URL });
await admin.connect();
// 1) Base RLS mechanic + restricted role (creates hivegrid_app, NOBYPASSRLS).
const baseSql = fs.readFileSync(path.join(REPO_ROOT, "prisma/rls/tenant-isolation.sql"), "utf8");
await admin.query(baseSql);
// Production credentials are intentionally not part of the rollout SQL.
// Set a disposable password only inside this throwaway integration database.
await admin.query(`ALTER ROLE ${APP_ROLE} PASSWORD '${APP_PASSWORD}'`);
// 2) Task RLS block: ENABLE/FORCE + tenant_isolation policy + GRANTs on the
// three Task tables (the deferred P5 activation artifact, applied here).
const taskRlsSql = fs.readFileSync(path.join(REPO_ROOT, "prisma/rls/task-isolation.sql"), "utf8");
await admin.query(taskRlsSql);
await admin.query(`GRANT CONNECT ON DATABASE ${new URL(ADMIN_URL!).pathname.slice(1)} TO ${APP_ROLE}`);
// 3) Seed two tenants' tasks + watchers + links as superuser (bypasses RLS).
// Task/TaskWatcher/EntityLink have no FK on tenantId/createdByUserId
// (bare scalars, AD-21), so no User/Tenant/Group rows are required. Each
// task carries exactly one assignee (single-assignee rule).
await admin.query(`INSERT INTO "Task"(id,"updatedAt","tenantId","createdByUserId","title","assignedToUserId") VALUES
('rls_taskA',now(),'rls_tenantA','rls_userA','task A','rls_userA'),
('rls_taskB',now(),'rls_tenantB','rls_userB','task B','rls_userB')
ON CONFLICT (id) DO NOTHING`);
await admin.query(`INSERT INTO "TaskWatcher"(id,"tenantId","taskId","userId") VALUES
('rls_watchA','rls_tenantA','rls_taskA','rls_userA'),
('rls_watchB','rls_tenantB','rls_taskB','rls_userB')
ON CONFLICT (id) DO NOTHING`);
await admin.query(`INSERT INTO "EntityLink"(id,"tenantId","createdByUserId","sourceType","sourceId","targetType","targetId") VALUES
('rls_linkA','rls_tenantA','rls_userA','task','rls_taskA','contact','rls_c1'),
('rls_linkB','rls_tenantB','rls_userB','task','rls_taskB','contact','rls_c2')
ON CONFLICT (id) DO NOTHING`);
app = new pg.Client({ connectionString: appUrlFrom(ADMIN_URL!) });
await app.connect();
});
afterAll(async () => {
await app?.end();
await admin?.end();
});
async function underTenant<T>(tenantId: string, run: () => Promise<T>): Promise<T> {
await app.query("BEGIN");
try {
await app.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
const result = await run();
await app.query("COMMIT");
return result;
} catch (error) {
await app.query("ROLLBACK");
throw error;
}
}
it("confirms the app role is neither superuser nor BYPASSRLS", async () => {
const { rows } = await admin.query(`SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = $1`, [APP_ROLE]);
expect(rows[0]).toMatchObject({ rolsuper: false, rolbypassrls: false });
});
it("under tenant A context, only tenant A tasks are visible", async () => {
const ids = await underTenant("rls_tenantA", async () => (await app.query(`SELECT id FROM "Task" ORDER BY id`)).rows.map((r) => r.id));
expect(ids).toEqual(["rls_taskA"]);
});
it("cannot read another tenant's task even with an explicit WHERE filter", async () => {
const count = await underTenant("rls_tenantA", async () => (await app.query(`SELECT count(*)::int AS c FROM "Task" WHERE "tenantId" = 'rls_tenantB'`)).rows[0].c);
expect(count).toBe(0);
});
it("sees no tasks at all when no tenant context is set", async () => {
const count = (await app.query(`SELECT count(*)::int AS c FROM "Task"`)).rows[0].c;
expect(count).toBe(0);
});
it("WITH CHECK blocks inserting a task for a foreign tenant", async () => {
await expect(
underTenant("rls_tenantA", async () =>
app.query(
`INSERT INTO "Task"(id,"updatedAt","tenantId","createdByUserId","title","assignedToUserId")
VALUES ('rls_taskX',now(),'rls_tenantB','rls_userB','x','rls_userB')`,
),
),
).rejects.toThrow(/row-level security/i);
});
it("allows inserting and reading a task for the active tenant", async () => {
await underTenant("rls_tenantA", async () => {
await app.query(
`INSERT INTO "Task"(id,"updatedAt","tenantId","createdByUserId","title","assignedToUserId")
VALUES ('rls_taskA2',now(),'rls_tenantA','rls_userA','a2','rls_userA')
ON CONFLICT (id) DO NOTHING`,
);
const ids = (await app.query(`SELECT id FROM "Task" ORDER BY id`)).rows.map((r) => r.id);
expect(ids).toContain("rls_taskA2");
expect(ids).not.toContain("rls_taskB");
});
});
it("isolates TaskWatcher rows by tenant as well", async () => {
const ids = await underTenant("rls_tenantA", async () => (await app.query(`SELECT id FROM "TaskWatcher" ORDER BY id`)).rows.map((r) => r.id));
expect(ids).toEqual(["rls_watchA"]);
const crossCount = await underTenant("rls_tenantA", async () => (await app.query(`SELECT count(*)::int AS c FROM "TaskWatcher" WHERE "tenantId" = 'rls_tenantB'`)).rows[0].c);
expect(crossCount).toBe(0);
});
it("isolates EntityLink rows by tenant as well", async () => {
const ids = await underTenant("rls_tenantA", async () => (await app.query(`SELECT id FROM "EntityLink" ORDER BY id`)).rows.map((r) => r.id));
expect(ids).toEqual(["rls_linkA"]);
const crossCount = await underTenant("rls_tenantA", async () => (await app.query(`SELECT count(*)::int AS c FROM "EntityLink" WHERE "tenantId" = 'rls_tenantB'`)).rows[0].c);
expect(crossCount).toBe(0);
});
});