Jesse_Chen
eb8ed8bee4
# Conflicts: # .github/workflows/deploy-production.yml # frontend/src/app/api/account/route.ts # frontend/src/app/globals.css # frontend/src/app/page.tsx # frontend/tests/health-deployment.test.ts
459 lines
14 KiB
TypeScript
459 lines
14 KiB
TypeScript
import assert from "node:assert/strict";
|
|
import { spawn, spawnSync } from "node:child_process";
|
|
import {
|
|
appendFileSync,
|
|
copyFileSync,
|
|
mkdtempSync,
|
|
mkdirSync,
|
|
rmSync,
|
|
unlinkSync,
|
|
writeFileSync,
|
|
} from "node:fs";
|
|
import { tmpdir } from "node:os";
|
|
import { dirname, join } from "node:path";
|
|
import { fileURLToPath } from "node:url";
|
|
import { test } from "node:test";
|
|
import { readDatabaseUrl } from "../src/lib/db/config";
|
|
import { startPostgresFixture } from "./helpers/postgres-fixture";
|
|
|
|
const migrationFilename = "20260720000100_backend_foundation.sql";
|
|
const pendingFilename = "20260720000200_pending_check.sql";
|
|
const concurrentFilename = "20260720000300_concurrent_lock.sql";
|
|
const failingFilename = "20260720000400_atomic_rollback.sql";
|
|
const malformedFilename = "20260720_malformed.sql";
|
|
const runnerPath = fileURLToPath(
|
|
new URL("../scripts/db-migrate.mjs", import.meta.url),
|
|
);
|
|
const migrationPath = fileURLToPath(
|
|
new URL(`../db/migrations/${migrationFilename}`, import.meta.url),
|
|
);
|
|
const fixturePasswords = [
|
|
"schema-owner-test-password",
|
|
"identity-runtime-test-password",
|
|
"app-runtime-test-password",
|
|
"admin-runtime-test-password",
|
|
"migration-runner-test-password",
|
|
"backup-reader-test-password",
|
|
"postgres-test-password",
|
|
];
|
|
|
|
type MigrationResult = {
|
|
status: number | null;
|
|
stdout: string;
|
|
stderr: string;
|
|
};
|
|
|
|
function migrationInvocation(
|
|
connectionString: string,
|
|
options: { check?: boolean; migrationsDirectory?: string },
|
|
) {
|
|
return {
|
|
arguments: [runnerPath, ...(options.check ? ["--check"] : [])],
|
|
environment: {
|
|
NODE_ENV: process.env.NODE_ENV,
|
|
SCHEMA_DATABASE_URL: connectionString,
|
|
...(options.migrationsDirectory
|
|
? { MIGRATIONS_DIRECTORY: options.migrationsDirectory }
|
|
: {}),
|
|
},
|
|
};
|
|
}
|
|
|
|
function runMigration(
|
|
connectionString: string,
|
|
options: { check?: boolean; migrationsDirectory?: string } = {},
|
|
): MigrationResult {
|
|
const invocation = migrationInvocation(connectionString, options);
|
|
const result = spawnSync(
|
|
process.execPath,
|
|
invocation.arguments,
|
|
{
|
|
encoding: "utf8",
|
|
env: invocation.environment,
|
|
},
|
|
);
|
|
return { status: result.status, stdout: result.stdout, stderr: result.stderr };
|
|
}
|
|
|
|
function runMigrationAsync(
|
|
connectionString: string,
|
|
migrationsDirectory: string,
|
|
): Promise<MigrationResult> {
|
|
const invocation = migrationInvocation(connectionString, {
|
|
migrationsDirectory,
|
|
});
|
|
|
|
return new Promise((resolve, reject) => {
|
|
const child = spawn(process.execPath, invocation.arguments, {
|
|
env: invocation.environment,
|
|
stdio: ["ignore", "pipe", "pipe"],
|
|
});
|
|
let stdout = "";
|
|
let stderr = "";
|
|
child.stdout.setEncoding("utf8");
|
|
child.stderr.setEncoding("utf8");
|
|
child.stdout.on("data", (chunk) => {
|
|
stdout += chunk;
|
|
});
|
|
child.stderr.on("data", (chunk) => {
|
|
stderr += chunk;
|
|
});
|
|
child.once("error", reject);
|
|
child.once("close", (status) => resolve({ status, stdout, stderr }));
|
|
});
|
|
}
|
|
|
|
function assertSafeOutput(result: MigrationResult): void {
|
|
const output = `${result.stdout}${result.stderr}`;
|
|
for (const password of fixturePasswords) {
|
|
assert.doesNotMatch(output, new RegExp(password));
|
|
}
|
|
}
|
|
|
|
test("readDatabaseUrl requires APP_DATABASE_URL", () => {
|
|
assert.throws(
|
|
() => readDatabaseUrl({} as NodeJS.ProcessEnv, "APP_DATABASE_URL"),
|
|
new Error("APP_DATABASE_URL is required"),
|
|
);
|
|
});
|
|
|
|
test("migration runner is serialized, atomic, drift-safe, and read-only in check mode", async () => {
|
|
const fixture = startPostgresFixture();
|
|
const temporaryDirectory = mkdtempSync(join(tmpdir(), "jyotisha-migrations-"));
|
|
const copiedMigration = join(temporaryDirectory, migrationFilename);
|
|
const schemaUrl = fixture.connectionUrl(
|
|
"schema_owner",
|
|
"schema-owner-test-password",
|
|
);
|
|
const results: MigrationResult[] = [];
|
|
|
|
try {
|
|
copyFileSync(migrationPath, copiedMigration);
|
|
|
|
const malformedPath = join(temporaryDirectory, malformedFilename);
|
|
writeFileSync(malformedPath, "select 1;\n");
|
|
const malformedCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(malformedCheck);
|
|
assert.equal(malformedCheck.status, 1);
|
|
assert.match(malformedCheck.stderr, /invalid migration filename/);
|
|
unlinkSync(malformedPath);
|
|
|
|
const missingLedgerCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(missingLedgerCheck);
|
|
assert.equal(missingLedgerCheck.status, 3);
|
|
assert.equal(missingLedgerCheck.stdout.trim(), migrationFilename);
|
|
assert.equal(
|
|
fixture.psql(
|
|
"select exists (select from pg_namespace where nspname = 'migration')",
|
|
),
|
|
"f",
|
|
);
|
|
|
|
const firstRun = runMigration(schemaUrl, {
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(firstRun);
|
|
assert.equal(firstRun.status, 0, firstRun.stderr);
|
|
assert.match(firstRun.stdout, new RegExp(`applied ${migrationFilename}`));
|
|
|
|
const ledgerRow = fixture.psql(`
|
|
select filename || ':' || checksum || ':' || applied_at
|
|
from migration.schema_migrations
|
|
`);
|
|
const [filename, checksum] = ledgerRow.split(":");
|
|
assert.equal(filename, migrationFilename);
|
|
assert.equal(checksum.length, 64);
|
|
|
|
const secondRun = runMigration(schemaUrl, {
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(secondRun);
|
|
assert.equal(secondRun.status, 0, secondRun.stderr);
|
|
assert.match(
|
|
secondRun.stdout,
|
|
new RegExp(`already applied ${migrationFilename}`),
|
|
);
|
|
assert.equal(
|
|
fixture.psql(`
|
|
select filename || ':' || checksum || ':' || applied_at
|
|
from migration.schema_migrations
|
|
`),
|
|
ledgerRow,
|
|
);
|
|
|
|
const currentCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(currentCheck);
|
|
assert.equal(currentCheck.status, 0, currentCheck.stderr);
|
|
|
|
const emptyDirectory = join(temporaryDirectory, "empty");
|
|
mkdirSync(emptyDirectory);
|
|
const missingFileCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: emptyDirectory,
|
|
});
|
|
results.push(missingFileCheck);
|
|
assert.equal(missingFileCheck.status, 1);
|
|
assert.match(
|
|
missingFileCheck.stderr,
|
|
new RegExp(`migration file missing: ${migrationFilename}`),
|
|
);
|
|
const missingFileApply = runMigration(schemaUrl, {
|
|
migrationsDirectory: emptyDirectory,
|
|
});
|
|
results.push(missingFileApply);
|
|
assert.equal(missingFileApply.status, 1);
|
|
assert.match(
|
|
missingFileApply.stderr,
|
|
new RegExp(`migration file missing: ${migrationFilename}`),
|
|
);
|
|
|
|
const pendingPath = join(temporaryDirectory, pendingFilename);
|
|
mkdirSync(dirname(pendingPath), { recursive: true });
|
|
writeFileSync(pendingPath, "create schema check_mode_side_effect;\n");
|
|
const ledgerCountBeforeCheck = fixture.psql(
|
|
"select count(*) from migration.schema_migrations",
|
|
);
|
|
const pendingCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(pendingCheck);
|
|
assert.equal(pendingCheck.status, 3, pendingCheck.stderr);
|
|
assert.equal(pendingCheck.stdout.trim(), pendingFilename);
|
|
assert.equal(
|
|
fixture.psql("select count(*) from migration.schema_migrations"),
|
|
ledgerCountBeforeCheck,
|
|
);
|
|
assert.equal(
|
|
fixture.psql(
|
|
"select exists (select from pg_namespace where nspname = 'check_mode_side_effect')",
|
|
),
|
|
"f",
|
|
);
|
|
unlinkSync(pendingPath);
|
|
|
|
appendFileSync(copiedMigration, " ");
|
|
const driftRun = runMigration(schemaUrl, {
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(driftRun);
|
|
assert.equal(driftRun.status, 1);
|
|
assert.match(
|
|
driftRun.stderr,
|
|
new RegExp(`migration checksum mismatch: ${migrationFilename}`),
|
|
);
|
|
|
|
const driftCheck = runMigration(schemaUrl, {
|
|
check: true,
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(driftCheck);
|
|
assert.equal(driftCheck.status, 1);
|
|
assert.match(
|
|
driftCheck.stderr,
|
|
new RegExp(`migration checksum mismatch: ${migrationFilename}`),
|
|
);
|
|
copyFileSync(migrationPath, copiedMigration);
|
|
|
|
writeFileSync(
|
|
join(temporaryDirectory, concurrentFilename),
|
|
"select pg_sleep(1);\ncreate schema concurrent_lock_probe;\n",
|
|
);
|
|
const concurrentResults = await Promise.all([
|
|
runMigrationAsync(schemaUrl, temporaryDirectory),
|
|
runMigrationAsync(schemaUrl, temporaryDirectory),
|
|
]);
|
|
results.push(...concurrentResults);
|
|
assert.deepEqual(
|
|
concurrentResults.map((result) => result.status),
|
|
[0, 0],
|
|
);
|
|
assert.deepEqual(
|
|
concurrentResults
|
|
.flatMap((result) => result.stdout.trim().split("\n"))
|
|
.filter((line) => line.endsWith(concurrentFilename))
|
|
.sort(),
|
|
[
|
|
`already applied ${concurrentFilename}`,
|
|
`applied ${concurrentFilename}`,
|
|
].sort(),
|
|
);
|
|
assert.equal(
|
|
fixture.psql(`
|
|
select count(*)
|
|
from migration.schema_migrations
|
|
where filename = '${concurrentFilename}'
|
|
`),
|
|
"1",
|
|
);
|
|
|
|
writeFileSync(
|
|
join(temporaryDirectory, failingFilename),
|
|
`create schema atomic_rollback_probe authorization schema_owner;
|
|
create table atomic_rollback_probe.parent (id integer primary key);
|
|
create table atomic_rollback_probe.child (
|
|
parent_id integer references atomic_rollback_probe.parent(id)
|
|
deferrable initially deferred
|
|
);
|
|
insert into atomic_rollback_probe.child (parent_id) values (1);
|
|
`,
|
|
);
|
|
const failingRun = runMigration(schemaUrl, {
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
results.push(failingRun);
|
|
assert.equal(failingRun.status, 1);
|
|
assert.match(
|
|
failingRun.stderr,
|
|
new RegExp(`migration failed: ${failingFilename}`),
|
|
);
|
|
assert.equal(
|
|
fixture.psql(`
|
|
select exists (
|
|
select from pg_namespace where nspname = 'atomic_rollback_probe'
|
|
)
|
|
`),
|
|
"f",
|
|
);
|
|
assert.equal(
|
|
fixture.psql(`
|
|
select count(*)
|
|
from migration.schema_migrations
|
|
where filename = '${failingFilename}'
|
|
`),
|
|
"0",
|
|
);
|
|
|
|
assert.equal(
|
|
fixture.psql(
|
|
"select has_database_privilege('app_runtime', current_database(), 'create')",
|
|
),
|
|
"f",
|
|
);
|
|
assert.equal(
|
|
fixture.psql(
|
|
"select has_table_privilege('app_runtime', 'migration.schema_migrations', 'select')",
|
|
),
|
|
"f",
|
|
);
|
|
|
|
for (const result of results) assertSafeOutput(result);
|
|
} finally {
|
|
fixture.stop();
|
|
rmSync(temporaryDirectory, { force: true, recursive: true });
|
|
}
|
|
});
|
|
|
|
test("foundation grants no direct runtime table DML and exposes only reviewed functions", () => {
|
|
const fixture = startPostgresFixture();
|
|
const temporaryDirectory = mkdtempSync(join(tmpdir(), "jyotisha-privileges-"));
|
|
const schemaUrl = fixture.connectionUrl(
|
|
"schema_owner",
|
|
"schema-owner-test-password",
|
|
);
|
|
|
|
try {
|
|
copyFileSync(migrationPath, join(temporaryDirectory, migrationFilename));
|
|
const migration = runMigration(schemaUrl, {
|
|
migrationsDirectory: temporaryDirectory,
|
|
});
|
|
assert.equal(migration.status, 0, migration.stderr);
|
|
|
|
fixture.psqlAs(
|
|
"schema_owner",
|
|
"schema-owner-test-password",
|
|
`
|
|
create table public.runtime_boundary_probe (
|
|
id integer primary key,
|
|
value text not null
|
|
);
|
|
create table identity.runtime_boundary_probe (
|
|
id integer primary key,
|
|
value text not null
|
|
);
|
|
create table audit.admin_event_probe (
|
|
value text not null
|
|
);
|
|
create function identity.unreviewed_identity_probe()
|
|
returns text
|
|
language sql
|
|
as 'select ''not callable''::text';
|
|
create function audit.record_admin_event_probe(event_value text)
|
|
returns void
|
|
language sql
|
|
security definer
|
|
set search_path = pg_catalog, audit
|
|
as 'insert into audit.admin_event_probe(value) values (event_value)';
|
|
grant execute on function audit.record_admin_event_probe(text) to admin_runtime;
|
|
`,
|
|
);
|
|
|
|
assert.throws(() =>
|
|
fixture.psqlAs(
|
|
"app_runtime",
|
|
"app-runtime-test-password",
|
|
"insert into public.runtime_boundary_probe values (1, 'denied')",
|
|
),
|
|
);
|
|
assert.throws(() =>
|
|
fixture.psqlAs(
|
|
"admin_runtime",
|
|
"admin-runtime-test-password",
|
|
"insert into audit.admin_event_probe values ('denied')",
|
|
),
|
|
);
|
|
assert.throws(() =>
|
|
fixture.psqlAs(
|
|
"identity_runtime",
|
|
"identity-runtime-test-password",
|
|
"insert into identity.runtime_boundary_probe values (1, 'denied')",
|
|
),
|
|
);
|
|
assert.throws(() =>
|
|
fixture.psqlAs(
|
|
"admin_runtime",
|
|
"admin-runtime-test-password",
|
|
"update public.runtime_boundary_probe set value = 'denied'",
|
|
),
|
|
);
|
|
assert.equal(
|
|
fixture.psql(
|
|
"select has_function_privilege('identity_runtime', 'identity.unreviewed_identity_probe()', 'execute')",
|
|
),
|
|
"f",
|
|
);
|
|
assert.throws(() =>
|
|
fixture.psqlAs(
|
|
"identity_runtime",
|
|
"identity-runtime-test-password",
|
|
"select identity.unreviewed_identity_probe()",
|
|
),
|
|
);
|
|
|
|
assert.equal(
|
|
fixture.psqlAs(
|
|
"admin_runtime",
|
|
"admin-runtime-test-password",
|
|
"select audit.record_admin_event_probe('approved')",
|
|
),
|
|
"",
|
|
);
|
|
assert.equal(
|
|
fixture.psql("select value from audit.admin_event_probe"),
|
|
"approved",
|
|
);
|
|
} finally {
|
|
fixture.stop();
|
|
rmSync(temporaryDirectory, { force: true, recursive: true });
|
|
}
|
|
});
|