Jyotisha/frontend/tests/database-admin-identity.test.ts

import assert from "node:assert/strict";
import { spawnSync } from "node:child_process";
import { readFileSync } from "node:fs";
import { fileURLToPath } from "node:url";
import test from "node:test";

import {
  closeLocalPostgresDataPools,
  createLocalPostgresDataClient,
  type LocalPostgresDataClient,
} from "../src/lib/db/local-postgres-client-core.ts";
import { createAdminSupabaseClient } from "../src/lib/supabase/admin-client-core.ts";
import { startPostgresFixture } from "./helpers/postgres-fixture.ts";

const runnerPath = fileURLToPath(new URL("../scripts/db-migrate.mjs", import.meta.url));
const compatibilitySql = readFileSync(
  new URL("../../deploy/postgres/002-ensure-business-compatibility-roles.sql", import.meta.url),
  "utf8",
);
const unknownUserId = "00000000-0000-4000-8000-000000000001";
const visibleAdminId = "00000000-0000-4000-8000-000000000002";

test("service and restricted admin database identities stay separated", async () => {
  const fixture = startPostgresFixture();
  try {
    const migration = spawnSync(process.execPath, [runnerPath], {
      encoding: "utf8",
      env: {
        ...process.env,
        SCHEMA_DATABASE_URL: fixture.connectionUrl("schema_owner", "schema-owner-test-password"),
      },
    });
    assert.equal(migration.status, 0, `${migration.stdout}${migration.stderr}`);

    fixture.psql(
      "revoke connect on database jyotisha from public, service_runtime; "
        + "revoke service_role from service_runtime; grant service_role to admin_runtime",
    );
    const compatibility = spawnSync(
      "docker",
      [
        "compose",
        "--project-name",
        fixture.projectName,
        "--env-file",
        fixture.databaseEnvFile,
        "-f",
        "../deploy/docker-compose.postgres.yml",
        "-f",
        "../deploy/docker-compose.postgres-ci.yml",
        "exec",
        "-T",
        "postgres",
        "psql",
        "-v",
        "ON_ERROR_STOP=1",
        "-U",
        "postgres",
        "-d",
        "jyotisha",
        "-f",
        "/dev/stdin",
      ],
      {
        encoding: "utf8",
        input: compatibilitySql,
        env: {
          ...process.env,
          DATABASE_ENV_FILE: fixture.databaseEnvFile,
          POSTGRES_HOST_PORT: String(fixture.hostPort),
        },
      },
    );
    assert.equal(compatibility.status, 0, `${compatibility.stdout}${compatibility.stderr}`);
    assert.equal(fixture.psql("select pg_has_role('service_runtime','service_role','MEMBER')"), "t");
    assert.equal(fixture.psql("select pg_has_role('admin_runtime','service_role','MEMBER')"), "f");

    fixture.psqlAs("identity_runtime", "identity-runtime-test-password", `
      insert into identity.users (id, name, email, email_verified, email_verified_at, role)
      values ('${visibleAdminId}', 'Visible Admin', 'visible-admin@example.com', true, now(), 'admin')
    `);
    fixture.psql(`
      insert into public.admin_users (user_id, created_by)
      values ('${visibleAdminId}', '${visibleAdminId}');
      insert into public.admin_user_roles (admin_user_id, role_id, assigned_by)
      select '${visibleAdminId}', id, '${visibleAdminId}' from public.admin_roles where code = 'support';
    `);
    assert.equal(
      fixture.psqlAs(
        "admin_runtime",
        "admin-runtime-test-password",
        `select count(*) from public.admin_users where user_id = '${visibleAdminId}' and revoked_at is null`,
      ),
      "1",
      "admin list reads must not be hidden by admin_users RLS",
    );

    const previousAuthProvider = process.env.AUTH_PROVIDER;
    const previousServiceDatabaseUrl = process.env.SERVICE_DATABASE_URL;
    const previousAdminDatabaseUrl = process.env.ADMIN_DATABASE_URL;
    process.env.AUTH_PROVIDER = "self-hosted";
    process.env.SERVICE_DATABASE_URL = fixture.connectionUrl("service_runtime", "service-runtime-test-password");
    process.env.ADMIN_DATABASE_URL = fixture.connectionUrl("admin_runtime", "admin-runtime-test-password");
    try {
      const service = createAdminSupabaseClient() as unknown as LocalPostgresDataClient;
      const serviceProbe = await service.from("profiles").select("id").limit(1);
      assert.equal(serviceProbe.error, null);
    } finally {
      if (previousAuthProvider === undefined) delete process.env.AUTH_PROVIDER;
      else process.env.AUTH_PROVIDER = previousAuthProvider;
      if (previousServiceDatabaseUrl === undefined) delete process.env.SERVICE_DATABASE_URL;
      else process.env.SERVICE_DATABASE_URL = previousServiceDatabaseUrl;
      if (previousAdminDatabaseUrl === undefined) delete process.env.ADMIN_DATABASE_URL;
      else process.env.ADMIN_DATABASE_URL = previousAdminDatabaseUrl;
    }

    const restrictedAdmin = createLocalPostgresDataClient(
      fixture.connectionUrl("admin_runtime", "admin-runtime-test-password"),
      null,
      null,
    );
    const permissionProbe = await restrictedAdmin.rpc("admin_permission_keys", {
      p_user_id: unknownUserId,
    });
    assert.equal(permissionProbe.error, null);
    assert.deepEqual(permissionProbe.data, []);

    const elevationProbe = await createLocalPostgresDataClient(
      fixture.connectionUrl("admin_runtime", "admin-runtime-test-password"),
      null,
      "service_role",
    ).rpc("admin_permission_keys", { p_user_id: unknownUserId });
    assert.equal(elevationProbe.error?.code, "42501");
    assert.match(elevationProbe.error?.message ?? "", /permission denied to set role/);
  } finally {
    await closeLocalPostgresDataPools();
    fixture.stop();
  }
});