146 lines
7.5 KiB
TypeScript
146 lines
7.5 KiB
TypeScript
// SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
|
|
// What the migrate job applies, and the one grant list the global reader gets (decision AT-D20).
|
|
|
|
import { readdirSync, readFileSync } from "node:fs"
|
|
import { buildRegistry } from "@conusai-kit/core"
|
|
import { entitiesOf } from "@conusai-kit/data"
|
|
import { type Migration, OUTBOX_READER_GRANT } from "@conusai-kit/data-kysely"
|
|
import { KIT_JOBS_ENTITIES } from "@conusai-kit/jobs-pgboss"
|
|
import { describe, expect, it } from "vitest"
|
|
import { CROSS_TENANT_READS, jobSchemaGrants, readerGrants, splitStatements } from "../apps/worker/src/migrations.js"
|
|
import { appModules } from "../modules/index.js"
|
|
|
|
const dir = new URL("../migrations/postgres/", import.meta.url)
|
|
const files = readdirSync(dir)
|
|
.filter((name) => name.endsWith(".sql"))
|
|
.sort()
|
|
const committed = files.map((name) => readFileSync(new URL(name, dir), "utf8")).join("\n")
|
|
const registry = buildRegistry([...appModules])
|
|
|
|
describe("the committed migrations", () => {
|
|
it("are numbered, forward only, and start at 0001", () => {
|
|
expect(files.length).toBeGreaterThan(0)
|
|
expect(files[0]).toMatch(/^0001_/)
|
|
for (const name of files) expect(name).toMatch(/^\d{4}_[a-z0-9-]+\.sql$/)
|
|
})
|
|
|
|
it("create a table for every entity this app's registry declares", () => {
|
|
const declared = [...entitiesOf(registry, "postgres"), ...KIT_JOBS_ENTITIES]
|
|
const inModules = new Set(registry.migrations.postgres.map(({ module }) => module))
|
|
for (const entity of declared) {
|
|
// A module that ships its own migration folder is allowed to own its tables there.
|
|
if (inModules.size > 0 && !committed.includes(`create table "${entity.table}"`)) continue
|
|
expect(committed, `no migration creates ${entity.table}`).toContain(`create table "${entity.table}"`)
|
|
}
|
|
})
|
|
|
|
it("split into statements without losing a dollar-quoted body", () => {
|
|
const statements = splitStatements('create table "a" ("x" int);\n\ndo $$ begin perform 1; end $$;\n')
|
|
expect(statements).toHaveLength(2)
|
|
expect(statements[1]).toContain("do $$ begin perform 1; end $$")
|
|
})
|
|
})
|
|
|
|
describe("the Agent's tables", () => {
|
|
it("are the kit's, made again for the workspace-level Agent with no conversation carried over", () => {
|
|
// The kit's file, as the package that depends on it installed it.
|
|
const kitFile = new URL("../modules/node_modules/@conusai-kit/agent/migrations/0001_assistant.sql", import.meta.url)
|
|
const kit = readFileSync(kitFile, "utf8").split("\n").slice(1).join("\n")
|
|
const app = readFileSync(
|
|
new URL("../modules/agent/migrations/0005_assistant_workspace.sql", import.meta.url),
|
|
"utf8",
|
|
)
|
|
expect(app.endsWith(kit)).toBe(true)
|
|
for (const table of ["assistant_receipt", "assistant_thread", "assistant_turn", "assistant_conversation"])
|
|
expect(app).toContain(`drop table "${table}";`)
|
|
expect(app).toMatch(/create table "assistant_conversation" \([^;]*"project_id" uuid\n\);/)
|
|
})
|
|
})
|
|
|
|
describe("the global reader's grants", () => {
|
|
const [migration, workspaces] = readerGrants("postgresql://app_reader:secret@db:5432/app") as [Migration, Migration]
|
|
const sql = migration.statements.join(";\n")
|
|
const tables = ["outbox", "org_memberships", "projects", "auth_api_keys"]
|
|
|
|
it("grant the cross-tenant reads the kit modules declare, the outbox, and no other table", () => {
|
|
// The list is the modules' own declarations, so adding a read to a module adds the grant here
|
|
// and a table nobody declared is never granted.
|
|
expect(CROSS_TENANT_READS.map((read) => read.entity.table).sort()).toEqual([
|
|
"auth_api_keys",
|
|
"org_memberships",
|
|
"projects",
|
|
"workspaces",
|
|
])
|
|
const granted = migration.statements.filter((statement) => statement.startsWith("grant select"))
|
|
expect(granted).toHaveLength(tables.length)
|
|
for (const table of tables) expect(sql).toContain(`on "${table}" to "app_reader"`)
|
|
})
|
|
|
|
it("keep the text an installed app recorded: the runner refuses a migration that changed", () => {
|
|
// Literal on purpose. A read names its columns, so a column added to an entity changes nothing
|
|
// here. A column the reader really needs is a new migration, never an edit of this list.
|
|
const granted = (table: string) =>
|
|
migration.statements.find(
|
|
(statement) => statement.startsWith("grant select") && statement.includes(` on "${table}" `),
|
|
)
|
|
expect(granted("org_memberships")).toBe(
|
|
'grant select ("id", "version", "created_at", "updated_at", "org_id", "user_id", "role", "role_key") on "org_memberships" to "app_reader"',
|
|
)
|
|
expect(granted("projects")).toBe(
|
|
'grant select ("id", "version", "created_at", "updated_at", "org_id", "workspace_id", "slug", "name", "module_key", "archived_at", "visibility") on "projects" to "app_reader"',
|
|
)
|
|
expect(granted("auth_api_keys")).toBe(
|
|
'grant select ("id", "version", "created_at", "updated_at", "org_id", "name", "token_hash", "prefix", "user_id", "expires_at", "revoked_at") on "auth_api_keys" to "app_reader"',
|
|
)
|
|
})
|
|
|
|
it("grant a read added later by a migration of its own, so the first keeps the text it shipped with", () => {
|
|
expect(migration.name).toBe("0002_reader_grants")
|
|
expect(workspaces.name).toBe("0004_reader_grants_workspaces")
|
|
expect(workspaces.statements).toEqual([
|
|
'grant select ("id", "version", "created_at", "updated_at", "org_id", "slug", "name", "is_default", "visibility") on "workspaces" to "app_reader"',
|
|
'create policy "workspaces_global_reader" on "workspaces" for select to "app_reader" using (true)',
|
|
])
|
|
})
|
|
|
|
it("never reach a project's description, which is a person's free text", () => {
|
|
expect(sql).not.toContain('"description"')
|
|
})
|
|
|
|
it("let the reader write nothing but the columns the relay keeps its books in", () => {
|
|
// A grant on the whole row would let the reader rewrite an event's organization, name or
|
|
// payload, which the relay then hands to a subscriber running as a system actor.
|
|
const update = migration.statements.filter((statement) => statement.startsWith("grant update"))
|
|
expect(update).toHaveLength(1)
|
|
expect(update[0]).toContain('on "outbox" to "app_reader"')
|
|
for (const column of OUTBOX_READER_GRANT.update ?? []) expect(update[0]).toContain(`"${column}"`)
|
|
expect(update[0]).not.toContain('"payload"')
|
|
expect(update[0]).not.toContain('"org_id"')
|
|
expect(update[0]).not.toContain('"name"')
|
|
expect(sql).not.toMatch(/grant [^;]*insert/)
|
|
expect(sql).not.toMatch(/grant [^;]*delete/)
|
|
})
|
|
|
|
it("give the reader a policy of its own, because row-level security binds it too", () => {
|
|
// The reader owns nothing and has NOBYPASSRLS (data-services DS21), so without a policy
|
|
// naming it the forced policy on app.org_id would answer zero rows for every read.
|
|
for (const table of tables) {
|
|
expect(sql).toContain(
|
|
`create policy "${table}_global_reader" on "${table}" for select to "app_reader" using (true)`,
|
|
)
|
|
}
|
|
expect(sql).toContain('create policy "outbox_global_reader_update"')
|
|
})
|
|
|
|
it("name the role from the reader's own URL, quoted", () => {
|
|
expect(readerGrants("postgresql://other_reader:s@db:5432/app")[0]?.statements[0]).toContain('"other_reader"')
|
|
})
|
|
|
|
it("grant the runtime role the job schema a transactional enqueue needs", () => {
|
|
const jobs = jobSchemaGrants("postgresql://app_app:secret@db:5432/app").statements.join(";\n")
|
|
expect(jobs).toContain('grant usage on schema "pgboss" to "app_app"')
|
|
expect(jobs).toContain('alter default privileges in schema "pgboss"')
|
|
expect(jobs).not.toContain("app_reader")
|
|
})
|
|
})
|