208 lines
8.7 KiB
TypeScript
208 lines
8.7 KiB
TypeScript
import { afterAll, beforeAll, describe, expect, it } from "vitest";
|
|
import { createPostgresDb } from "@/infra/db/postgres";
|
|
import { migrate, pendingMigrations, readMigrationLedger, MigrationLedgerError } from "@/infra/db/migrate";
|
|
import type { Db } from "@/infra/db/types";
|
|
import { readRlsCatalog, RLS_TABLES } from "../helpers/rls-catalog";
|
|
|
|
/**
|
|
* RLS e isolamento multi-tenant contra PostgreSQL real. A suíte padrão usa
|
|
* PGlite, que é o mesmo motor mas não o mesmo servidor: papel `app_user`,
|
|
* `force row level security` e `set local role` precisam ser confirmados aqui.
|
|
*/
|
|
const url = process.env.ADAPTER_TEST_DATABASE_URL;
|
|
const enabled = Boolean(url);
|
|
|
|
function roleUrl(base: string, role: string): string {
|
|
const parsed = new URL(base);
|
|
parsed.username = role;
|
|
parsed.password = "";
|
|
return parsed.toString();
|
|
}
|
|
|
|
const ALPHA = "11111111-1111-4111-8111-111111111111";
|
|
const BETA = "22222222-2222-4222-8222-222222222222";
|
|
const ANA = "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa";
|
|
const DIEGO = "dddddddd-dddd-4ddd-8ddd-dddddddddddd";
|
|
|
|
let db: Db;
|
|
|
|
describe.skipIf(!enabled)("PostgreSQL real", () => {
|
|
beforeAll(async () => {
|
|
process.env.ADAPTER_APPLY_IDENTITY_SHIM = "1";
|
|
db = createPostgresDb(url as string);
|
|
await db.exec(`drop schema if exists public cascade; create schema public;`);
|
|
await migrate(db, { applyIdentityShim: true });
|
|
await db.withService(async (query) => {
|
|
await query.query(
|
|
`insert into public.profiles (id, display_name, is_superadmin) values ($1,'Ana',false), ($2,'Diego',false)
|
|
on conflict (id) do nothing`,
|
|
[ANA, DIEGO],
|
|
);
|
|
await query.query(
|
|
`insert into public.workspaces (id, name) values ($1,'Alpha'), ($2,'Beta') on conflict (id) do nothing`,
|
|
[ALPHA, BETA],
|
|
);
|
|
await query.query(
|
|
`insert into public.workspace_members (workspace_id, user_id, role) values ($1,$2,'owner'), ($3,$4,'admin')
|
|
on conflict (workspace_id, user_id) do nothing`,
|
|
[ALPHA, ANA, BETA, DIEGO],
|
|
);
|
|
await query.query(
|
|
`insert into public.workspace_module_flags (workspace_id, module_key, enabled)
|
|
values ($1,'spreadsheet_adapter',true), ($2,'spreadsheet_adapter',true)
|
|
on conflict (workspace_id, module_key) do update set enabled = excluded.enabled`,
|
|
[ALPHA, BETA],
|
|
);
|
|
});
|
|
});
|
|
|
|
afterAll(async () => {
|
|
if (db) await db.close();
|
|
});
|
|
|
|
it("aplica todas as migrations e não deixa pendência", async () => {
|
|
expect(await pendingMigrations(db, { applyIdentityShim: true })).toEqual([]);
|
|
});
|
|
|
|
it("cria o papel app_user sem bypass de RLS", async () => {
|
|
const rows = await db.withService((query) =>
|
|
query.query<{ rolbypassrls: boolean; rolsuper: boolean }>(
|
|
`select rolbypassrls, rolsuper from pg_roles where rolname = 'app_user'`,
|
|
),
|
|
);
|
|
expect(rows[0]).toBeDefined();
|
|
expect(rows[0].rolbypassrls).toBe(false);
|
|
expect(rows[0].rolsuper).toBe(false);
|
|
});
|
|
|
|
it("força RLS em todas as tabelas do módulo", async () => {
|
|
const rows = await db.withService(readRlsCatalog);
|
|
expect(rows.map((row) => row.relname)).toEqual([...RLS_TABLES]);
|
|
for (const row of rows) {
|
|
expect(["r", "p"], `${row.relname} não é tabela`).toContain(row.relkind);
|
|
expect(row.relrowsecurity, `${row.relname} sem RLS`).toBe(true);
|
|
expect(row.relforcerowsecurity, `${row.relname} sem FORCE RLS`).toBe(true);
|
|
}
|
|
});
|
|
|
|
it("não deixa um usuário ver o workspace do outro", async () => {
|
|
const anaSees = await db.withUser(ANA, (query) =>
|
|
query.query<{ id: string }>(`select id from public.workspaces`),
|
|
);
|
|
expect(anaSees.map((row) => row.id.toLowerCase())).toEqual([ALPHA]);
|
|
|
|
const diegoSees = await db.withUser(DIEGO, (query) =>
|
|
query.query<{ id: string }>(`select id from public.workspaces`),
|
|
);
|
|
expect(diegoSees.map((row) => row.id.toLowerCase())).toEqual([BETA]);
|
|
});
|
|
|
|
it("não deixa um usuário ler modelo de outro workspace", async () => {
|
|
const modelId = "99999999-9999-4999-8999-999999999999";
|
|
await db.withService((query) =>
|
|
query.query(
|
|
`insert into public.spreadsheet_models
|
|
(id, workspace_id, name, original_filename, format, storage_key, content_sha256, sheet_name, header_row, columns_schema, created_by, status)
|
|
values ($1,$2,'Modelo Beta','b.xlsx','xlsx',$3,'sha','Plan1',1,'[]'::jsonb,$4,'active')
|
|
on conflict (id) do nothing`,
|
|
[modelId, BETA, `workspaces/${BETA}/models/${modelId}/original`, DIEGO],
|
|
),
|
|
);
|
|
const anaSees = await db.withUser(ANA, (query) =>
|
|
query.query(`select id from public.spreadsheet_models where id = $1`, [modelId]),
|
|
);
|
|
expect(anaSees).toHaveLength(0);
|
|
});
|
|
|
|
it("não deixa o papel da aplicação alterar auditoria", async () => {
|
|
// workspace_id é uuid e resource_id é text: cada um tem o seu parâmetro,
|
|
// senão o Postgres recusa com "inconsistent types deduced for parameter".
|
|
await db.withService((query) =>
|
|
query.query(
|
|
`insert into public.audit_log (workspace_id, actor_id, action, resource_type, resource_id, result)
|
|
values ($1::uuid, $2::uuid, 'test.event', 'workspace', $3::text, 'success')`,
|
|
[ALPHA, ANA, ALPHA],
|
|
),
|
|
);
|
|
|
|
// A recusa tem de vir da falta de privilégio (42501), não de sintaxe ou tipo.
|
|
for (const statement of [
|
|
`update public.audit_log set result = 'error' where workspace_id = $1::uuid`,
|
|
`delete from public.audit_log where workspace_id = $1::uuid`,
|
|
]) {
|
|
await expect(
|
|
db.withUser(ANA, (query) => query.query(statement, [ALPHA])),
|
|
).rejects.toMatchObject({ code: "42501", message: expect.stringMatching(/permission denied/i) });
|
|
}
|
|
|
|
const rows = await db.withService((query) =>
|
|
query.query<{ result: string }>(
|
|
`select result from public.audit_log where workspace_id = $1::uuid and action = 'test.event'`,
|
|
[ALPHA],
|
|
),
|
|
);
|
|
expect(rows.length).toBeGreaterThan(0);
|
|
expect(rows.every((row) => row.result === "success")).toBe(true);
|
|
});
|
|
|
|
it("mantém web e worker sem bypass e sem varredura direta", async () => {
|
|
const roles = await db.withService((query) =>
|
|
query.query<{ rolname: string; rolsuper: boolean; rolbypassrls: boolean }>(
|
|
`select rolname, rolsuper, rolbypassrls from pg_roles
|
|
where rolname in ('adaptador_web', 'adaptador_worker', 'app_user')`,
|
|
),
|
|
);
|
|
expect(roles).toHaveLength(3);
|
|
for (const role of roles) {
|
|
expect(role.rolsuper).toBe(false);
|
|
expect(role.rolbypassrls).toBe(false);
|
|
}
|
|
await expect(
|
|
db.exec(`do $$ begin set local role adaptador_web; perform 1 from public.spreadsheet_jobs; end $$;`),
|
|
).rejects.toMatchObject({ code: "42501" });
|
|
await expect(
|
|
db.exec(`do $$ begin set local role app_user; perform * from public.adapter_list_queued_job_ids(); end $$;`),
|
|
).rejects.toMatchObject({ code: "42501" });
|
|
await db.exec(`do $$ begin set local role adaptador_worker; perform * from public.adapter_list_queued_job_ids(); end $$;`);
|
|
const queued = await db.withUser(ANA, (query) => query.query<{ current_user: string }>(`select current_user`));
|
|
expect(queued[0]?.current_user).toBe("app_user");
|
|
});
|
|
|
|
it("separa histórico ausente, íntegro e ilegível", async () => {
|
|
await db.exec("drop table public.schema_migrations");
|
|
await expect(readMigrationLedger(db)).resolves.toEqual({ state: "ausente" });
|
|
const aindaAusente = await db.withService((query) =>
|
|
query.query<{ name: string | null }>("select to_regclass('public.schema_migrations')::text as name"),
|
|
);
|
|
expect(aindaAusente[0]?.name ?? null).toBeNull();
|
|
await migrate(db, { applyIdentityShim: true });
|
|
const presente = await readMigrationLedger(db);
|
|
expect(presente.state).toBe("presente");
|
|
if (presente.state === "presente") expect(presente.ids).toHaveLength(5);
|
|
|
|
await db.exec(`
|
|
do $$ begin
|
|
create role historico_leitor nologin nosuperuser nobypassrls;
|
|
exception when duplicate_object then null;
|
|
end $$;
|
|
grant usage on schema public to historico_leitor;
|
|
alter role historico_leitor login;
|
|
`);
|
|
const leitor = createPostgresDb(roleUrl(url as string, "historico_leitor"));
|
|
await expect(readMigrationLedger(leitor)).rejects.toBeInstanceOf(MigrationLedgerError);
|
|
await leitor.close();
|
|
await db.exec("revoke all on schema public from historico_leitor; drop role historico_leitor");
|
|
});
|
|
|
|
it("pula o shim quando a decisão é não aplicar", async () => {
|
|
const pending = await pendingMigrations(db, { applyIdentityShim: false });
|
|
expect(pending).not.toContain("0001_platform_shim.sql");
|
|
});
|
|
});
|
|
|
|
describe.skipIf(enabled)("PostgreSQL real", () => {
|
|
it("PENDENTE: defina ADAPTER_TEST_DATABASE_URL para executar", () => {
|
|
expect(enabled).toBe(false);
|
|
});
|
|
});
|