Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
21.3 KB
Raw Blame
/**
 * Every gbrain function that pins a search_path ends it with pg_temp, so a temp table or view named like a purge or
 * withdrawal ledger no longer hides it from the guard that reads it (#7632, src/core/function-search-path.ts).
 *
 * Before, the guards pinned `pg_catalog, public`, which leaves the caller's temporary schema first in the relation
 * search: any session could create an empty temp table named fact_purges (or take_purges, page_purges, fact_withdrawals,
 * page_segment_derivation_purges) and store a purged claim, take, page or derivation again, or save a withdrawn fact
 * active, as the owner or as a non-owner writer. The guards themselves are as on master (SECURITY INVOKER, same bodies,
 * same timing); a writer with no policy on a ledger still reads it empty under its own RLS (#7659), which this file does
 * not cover: its writer has policies on every table.
 * Run: DATABASE_URL=... bun test test/e2e/function-search-path-postgres.test.ts
 */
import { afterAll, beforeAll, describe, expect, test } from 'bun:test';
import { randomUUID } from 'node:crypto';
import postgres from '#postgres';
import { assertSafeE2eDatabaseUrl } from '../helpers/db-guard.ts';
import { PostgresEngine } from '../../src/core/postgres-engine.ts';
import { LATEST_VERSION, runMigrations } from '../../src/core/migrate.ts';
import { derivationNeedles } from '../../src/core/page-body/segment-derive.ts';
import { v277 } from '../../src/core/schema-migrations/v277-function-search-path-pg-temp.ts';
import { PINNED_FUNCTION_NAMES } from '../../src/core/function-search-path.ts';

const DATABASE_URL = process.env.DATABASE_URL;
const describeE2E = DATABASE_URL ? describe : describe.skip;

type Sql = ReturnType<typeof postgres>;
type Outcome = { rows: Array<Record<string, unknown>> } | { code: string; message: string };

const PURGED_CLAIM = 'alice-example joined acme-example as cto';
const WITHDRAWN_CLAIM = 'alice-example lives in city-a';
const PURGED_TAKE = 'acme-example will double revenue';
const PURGED_HASH = 'deadbeefcafe00000000000000000001';
const SHA = `decode(repeat('ab', 32), 'hex')`;
const SHADOWED = ['fact_purges', 'fact_withdrawals', 'take_purges', 'page_purges', 'page_segment_derivation_purges'];
const GUARD_TRIGGERS = ['facts_guard_inserted', 'facts_preserve_withdrawal', 'facts_refuse_purged', 'page_segment_derivations_refuse_purging',
  'pages_refuse_purged', 'takes_refuse_purged'];

const suffix = randomUUID().replaceAll('-', '').slice(0, 12);
const WRITER = `gbrain_test_path_writer_${suffix}`;
const scratch: string[] = [];
const createdRoles: string[] = [];
let admin: Sql | null = null;

function urlFor(name: string): string {
  const url = new URL(DATABASE_URL!);
  url.pathname = `/${name}`;
  return url.toString();
}

async function freshBrain(): Promise<{ url: string; conn: Sql }> {
  assertSafeE2eDatabaseUrl(DATABASE_URL!);
  const name = `gbrain_test_path_${randomUUID().replaceAll('-', '')}`;
  await admin!.unsafe(`CREATE DATABASE ${name}`);
  scratch.push(name);
  const url = urlFor(name);
  const conn = postgres(url, { max: 1, prepare: false, onnotice: () => {} });
  const engine = new PostgresEngine();
  await engine.connect({ database_url: url });
  try { await engine.initSchema(); } finally { await engine.disconnect(); }
  return { url, conn };
}

/** Runs `fn` as `role` in one transaction that is rolled back; each `run` is its own savepoint. */
async function as<T>(conn: Sql, role: string, fn: (run: (sql: string, params?: unknown[]) => Promise<Outcome>) => Promise<T>): Promise<T> {
  const session = await conn.reserve();
  try {
    await session.unsafe('BEGIN');
    await session.unsafe(`SET LOCAL ROLE ${role}`);
    return await fn(async (sql, params) => {
      await session.unsafe('SAVEPOINT attempt');
      try {
        const rows = await session.unsafe(sql, params as never[]);
        await session.unsafe('RELEASE SAVEPOINT attempt');
        return { rows: [...rows] as Array<Record<string, unknown>> };
      } catch (error) {
        await session.unsafe('ROLLBACK TO SAVEPOINT attempt');
        const e = error as { code?: string; message?: string };
        return { code: String(e.code), message: String(e.message) };
      }
    });
  } finally {
    await session.unsafe('ROLLBACK').catch(() => {});
    session.release();
  }
}

const rowsOf = (o: Outcome) => ('rows' in o ? o.rows : (() => { throw new Error(`expected rows, got ${o.code}: ${o.message}`); })());
const outcome = (o: Outcome) => ('code' in o ? o.message.split(':')[0] : `stored ${o.rows.length}`);

async function capturingStderr<T>(fn: () => Promise<T>): Promise<{ value: T; stderr: string }> {
  const write = process.stderr.write.bind(process.stderr);
  let stderr = '';
  process.stderr.write = ((chunk: string | Uint8Array) => { stderr += String(chunk); return true; }) as typeof process.stderr.write;
  try { return { value: await fn(), stderr }; } finally { process.stderr.write = write; }
}

/** SECURITY DEFINER functions, guard trigger timing and every pinned search_path in public. */
async function catalog(c: Sql) {
  const definers = await c.unsafe<Array<{ fn: string }>>(
    `SELECT p.oid::regprocedure::text AS fn FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'public' AND p.prosecdef ORDER BY 1`);
  const triggers = await c.unsafe<Array<{ trigger: string; timing: string }>>(
    `SELECT tgname AS trigger, CASE WHEN (tgtype & 2) <> 0 THEN 'BEFORE' ELSE 'AFTER' END AS timing FROM pg_trigger
      WHERE NOT tgisinternal AND tgname = ANY($1::text[]) ORDER BY 1`, [GUARD_TRIGGERS]);
  const paths = await c.unsafe<Array<{ name: string; sig: string; setting: string }>>(
    `SELECT p.proname AS name, p.oid::regprocedure::text AS sig, s.setting FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
      CROSS JOIN LATERAL unnest(p.proconfig) AS s(setting) WHERE n.nspname = 'public' AND s.setting LIKE 'search_path=%' ORDER BY 1`);
  return {
    definers: definers.map(d => d.fn),
    triggers: triggers.map(t => ({ trigger: t.trigger, timing: t.timing })),
    pinned: paths.length,
    withoutTemp: paths.filter(p => !p.setting.endsWith(', pg_temp')).map(p => p.sig),
    unlisted: paths.filter(p => !(PINNED_FUNCTION_NAMES as readonly string[]).includes(p.name)).map(p => p.sig),
  };
}

/** Puts a brain back in the shape every brain had before #7632 (no pg_temp on any pinned path), at the version before v277. */
async function rewind(c: Sql): Promise<void> {
  await c.unsafe(`DO $$ DECLARE f RECORD; BEGIN
      FOR f IN SELECT p.oid::regprocedure AS sig, substr(s.setting, 13) AS path FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                CROSS JOIN LATERAL unnest(p.proconfig) AS s(setting) WHERE n.nspname = 'public' AND s.setting LIKE 'search_path=%, pg_temp' LOOP
        EXECUTE format('ALTER FUNCTION %s SET search_path = %s', f.sig, left(f.path, -9));
      END LOOP; END $$;
    UPDATE config SET value = '${v277.version - 1}' WHERE key = 'version';`);
}

describeE2E('a temp table or view named like a ledger hides nothing from the guards (#7632)', () => {
  let conn: Sql;
  let url: string;
  let owner = '';
  const ids: Record<string, number> = {};

  beforeAll(async () => {
    assertSafeE2eDatabaseUrl(DATABASE_URL!);
    admin = postgres(DATABASE_URL!, { max: 1, prepare: false, onnotice: () => {} });
    await admin.unsafe(`CREATE ROLE ${WRITER} NOLOGIN NOSUPERUSER NOBYPASSRLS`);
    createdRoles.push(WRITER);
    ({ url, conn } = await freshBrain());
    [{ owner }] = await conn.unsafe<Array<{ owner: string }>>('SELECT current_user AS owner');
    // A non-owner writer with a policy on every RLS table, ledgers included: the guards refuse it exactly as the owner.
    await conn.unsafe(`GRANT USAGE ON SCHEMA public TO ${WRITER};
      GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ${WRITER};
      GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ${WRITER};
      DO $$ DECLARE t RECORD; BEGIN
        FOR t IN SELECT c.oid::regclass AS rel FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
                  WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND c.relrowsecurity LOOP
          EXECUTE format('CREATE POLICY gbrain_test_writer ON %s TO ${WRITER} USING (true) WITH CHECK (true)', t.rel);
        END LOOP; END $$`);
    for (const [key, slug] of [['takes', 'people/alice-example'], ['derived', 'notes/derivation-example']]) {
      const [row] = await conn.unsafe<Array<{ id: number }>>(`INSERT INTO pages (slug, type, title, compiled_truth) VALUES ($1, 'note', $1, 'x') RETURNING id`, [slug]);
      ids[key] = row!.id;
    }
    await conn.unsafe(`INSERT INTO fact_purges (source_id, visibility, subject, fact_hash) VALUES ('default', 'private', '*', gbrain_fact_fingerprint($1))`, [PURGED_CLAIM]);
    await conn.unsafe(`INSERT INTO fact_withdrawals (source_id, visibility, subject, fact_hash) VALUES ('default', 'private', '*', gbrain_fact_fingerprint($1))`, [WITHDRAWN_CLAIM]);
    await conn.unsafe(`INSERT INTO page_purges (source_id, content_hash, slug) VALUES ('default', $1, 'notes/purged-example')`, [PURGED_HASH]);
    await conn.unsafe(`INSERT INTO take_purges (source_id, subject, claim_hash) VALUES ('default', '*', gbrain_fact_fingerprint($1))`, [PURGED_TAKE]);
    const { needles, patterns } = await derivationNeedles({ executeRaw: (sql: string, params?: unknown[]) => conn.unsafe(sql, params as never[]) } as never, [PURGED_CLAIM]);
    await conn.unsafe(`INSERT INTO page_segment_derivation_purges (request_id, source_id, needles, patterns, page_ids) VALUES ($1::uuid, 'default', $2::text[], $3::text[], $4::int[])`,
      [randomUUID(), needles, patterns, [ids.derived]]);
  }, 300_000);

  afterAll(async () => {
    await conn?.end();
    if (admin) {
      for (const name of scratch) await admin.unsafe(`DROP DATABASE IF EXISTS ${name} WITH (FORCE)`);
      for (const role of createdRoles) await admin.unsafe(`DROP ROLE IF EXISTS ${role}`).catch(() => {});
      await admin.end();
    }
  });

  /** Each guarded write the ledgers should stop, and what became of it. */
  async function guardedWrites(run: (sql: string, params?: unknown[]) => Promise<Outcome>) {
    const fact = outcome(await run(`INSERT INTO facts (fact, source) VALUES ($1, 'probe') RETURNING id`, [PURGED_CLAIM]));
    const page = outcome(await run(`INSERT INTO pages (slug, type, title, compiled_truth, content_hash) VALUES ('notes/again-example', 'note', 't', 'x', $1) RETURNING id`, [PURGED_HASH]));
    const take = outcome(await run(`INSERT INTO takes (page_id, row_num, claim, kind, holder) VALUES ($1, 1, $2, 'take', 'alice-example') RETURNING id`, [ids.takes, PURGED_TAKE]));
    const derivation = outcome(await run(`INSERT INTO page_segment_derivations (page_id, pipeline, occurrence_id, pipeline_version, segment_sha256, context_hash, state, output)
      VALUES ($1, 'links', 1, 1, ${SHA}, ${SHA}, 'done', $2::jsonb) RETURNING 1 AS stored`, [ids.derived, JSON.stringify({ text: PURGED_CLAIM })]));
    const [w] = rowsOf(await run(`INSERT INTO facts (fact, source) VALUES ($1, 'probe') RETURNING id`, [WITHDRAWN_CLAIM]));
    const [stamped] = rowsOf(await run('SELECT expired_at IS NOT NULL AS expired FROM facts WHERE id = $1', [w!.id]));
    return { fact, page, take, derivation, withdrawn: stamped!.expired ? 'expired' : 'active' };
  }
  const REFUSED = { fact: 'purged_content', page: 'purged_content', take: 'purged_content', derivation: 'stored 0', withdrawn: 'expired' };

  test('1. without a shadow, the owner and a non-owner writer have a purged fact, page and take refused, a purged derivation dropped and a withdrawn fact stamped', async () => {
    for (const role of [owner, WRITER]) {
      expect({ role, ...(await as(conn, role, guardedWrites)) }).toEqual({ role, ...REFUSED });
    }
  });

  test('2. a temp table or view named like each ledger hides nothing, for the owner and for a non-owner writer', async () => {
    for (const role of [owner, WRITER]) {
      for (const kind of ['TABLE', 'VIEW']) {
        const result = await as(conn, role, async run => {
          for (const ledger of SHADOWED) {
            rowsOf(await run(kind === 'TABLE' ? `CREATE TEMP TABLE ${ledger} (LIKE public.${ledger})` : `CREATE TEMP VIEW ${ledger} AS SELECT * FROM public.${ledger} WHERE false`));
          }
          return guardedWrites(run);
        });
        expect({ role, kind, ...result }).toEqual({ role, kind, ...REFUSED });
      }
    }
  });

  test('3. after a fresh install: no SECURITY DEFINER function, the guard triggers as on master, every pinned search_path ends in pg_temp', async () => {
    const c = await catalog(conn);
    expect(c.definers).toEqual([]);
    expect(c.triggers).toEqual([
      { trigger: 'facts_guard_inserted', timing: 'AFTER' },
      { trigger: 'facts_preserve_withdrawal', timing: 'BEFORE' },
      { trigger: 'facts_refuse_purged', timing: 'BEFORE' },
      { trigger: 'page_segment_derivations_refuse_purging', timing: 'BEFORE' },
      { trigger: 'pages_refuse_purged', timing: 'BEFORE' },
      { trigger: 'takes_refuse_purged', timing: 'BEFORE' },
    ]);
    expect(c.pinned).toBeGreaterThanOrEqual(35);
    expect(c.withoutTemp).toEqual([]);
    expect(c.unlisted).toEqual([]);
  });

  test('4. a brain at the version before, migrated (no schema blob replay), ends the same, and doctor passes only after', async () => {
    const { functionSearchPathProblems } = await import('../../src/commands/doctor/checks/function-search-path.ts');
    const brain = await freshBrain();
    const engine = new PostgresEngine();
    try {
      await rewind(brain.conn);
      const before = await catalog(brain.conn);
      expect(before.withoutTemp.length).toBe(before.pinned);
      await engine.connect({ database_url: brain.url });
      expect((await functionSearchPathProblems(engine)).functions.length).toBe(before.pinned);
      const { value, stderr } = await capturingStderr(() => runMigrations(engine));
      expect(value.current).toBe(LATEST_VERSION);
      expect(stderr).not.toContain('left without pg_temp');
      expect(await catalog(brain.conn)).toEqual(await catalog(conn));
      expect(await functionSearchPathProblems(engine)).toEqual({ functions: [], fix: [] });
    } finally {
      await engine.disconnect();
      await brain.conn.end();
    }
  }, 300_000);

  test('5. a migrating role that owns every table and function but one finishes, names that one, and the printed fix (as its owner) clears it; the migrating role then boots', async () => {
    const { functionSearchPathProblems } = await import('../../src/commands/doctor/checks/function-search-path.ts');
    const brain = await freshBrain();
    const migrator = `gbrain_test_path_migrator_${suffix}`;
    const other = `gbrain_test_path_other_${suffix}`;
    await admin!.unsafe(`CREATE ROLE ${migrator} LOGIN PASSWORD 'migrator' NOSUPERUSER BYPASSRLS; CREATE ROLE ${other} NOLOGIN NOSUPERUSER`);
    createdRoles.push(migrator, other);
    const engine = new PostgresEngine();
    try {
      await rewind(brain.conn);
      await brain.conn.unsafe(`GRANT USAGE, CREATE ON SCHEMA public TO ${migrator};
        DO $$ DECLARE r RECORD; BEGIN
          FOR r IN SELECT c.oid::regclass AS rel FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
                    WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p', 'v', 'm', 'S')
                      AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = c.oid AND d.deptype IN ('e', 'a', 'i')) LOOP
            EXECUTE format('ALTER TABLE %s OWNER TO ${migrator}', r.rel);
          END LOOP;
          FOR r IN SELECT p.oid::regprocedure AS sig FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                    WHERE n.nspname = 'public' AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = p.oid AND d.deptype = 'e') LOOP
            EXECUTE format('ALTER ROUTINE %s OWNER TO ${migrator}', r.sig);
          END LOOP; END $$;
        ALTER FUNCTION gbrain_refuse_purged_fact() OWNER TO ${other}`);
      const asMigrator = new URL(brain.url);
      asMigrator.username = migrator;
      asMigrator.password = 'migrator';
      await engine.connect({ database_url: asMigrator.toString() });
      const { value, stderr } = await capturingStderr(() => runMigrations(engine));
      expect(value.current).toBe(LATEST_VERSION);
      expect(stderr.split('1 function(s) left without pg_temp on their search_path (gbrain_refuse_purged_fact())').length - 1).toBe(1);
      expect((await catalog(brain.conn)).withoutTemp).toEqual(['gbrain_refuse_purged_fact()']);
      const { functions, fix } = await functionSearchPathProblems(engine);
      expect({ functions, fix }).toEqual({
        functions: ['gbrain_refuse_purged_fact()'],
        fix: ['ALTER FUNCTION public.gbrain_refuse_purged_fact() SET search_path = pg_catalog, public, pg_temp;'],
      });
      const atHead = await capturingStderr(() => runMigrations(engine));
      expect(atHead.stderr).not.toContain('left without pg_temp');
      await brain.conn.unsafe(`SET ROLE ${other}; ${fix.join('\n')} RESET ROLE;`);
      expect(await functionSearchPathProblems(engine)).toEqual({ functions: [], fix: [] });
      await engine.initSchema();
      expect(await functionSearchPathProblems(engine)).toEqual({ functions: [], fix: [] });
      expect((await catalog(brain.conn)).withoutTemp).toEqual([]);
    } finally {
      await engine.disconnect().catch(() => {});
      await brain.conn.end();
    }
  }, 300_000);

  test('6. a brain at the latest version that boots again (schema blob replay, no migration) keeps pg_temp on every pinned path', async () => {
    const engine = new PostgresEngine();
    try {
      await engine.connect({ database_url: url });
      const { value } = await capturingStderr(() => runMigrations(engine));
      expect(value.applied).toBe(0);
      const before = await catalog(conn);
      await engine.initSchema();
      expect(await catalog(conn)).toEqual(before);
    } finally {
      await engine.disconnect();
    }
  }, 300_000);

  test('7. v277 refuses with schema_lock_blocked while another session holds a function it alters, and finishes after', async () => {
    const { withEnv } = await import('../helpers/with-env.ts');
    const engine = new PostgresEngine();
    const holder = postgres(url, { max: 1, prepare: false, onnotice: () => {} });
    try {
      await conn.unsafe('ALTER FUNCTION gbrain_try_jsonb(text) SET search_path = pg_catalog, public');
      await engine.connect({ database_url: url });
      const session = await holder.reserve();
      try {
        await session.unsafe(`BEGIN; ALTER FUNCTION gbrain_try_jsonb(text) SET statement_timeout = '1min'`);
        const error = await withEnv({ GBRAIN_SCHEMA_LOCK_TIMEOUT_SECONDS: '1' }, () => v277.handler!(engine).then(() => null, (e: unknown) => e));
        expect(error).toMatchObject({ code: 'schema_lock_blocked', detail: 'search_path hardening' });
        expect((error as Error).message).toContain('for a gbrain function row lock (pg_proc) another session holds');
        expect((await catalog(conn)).withoutTemp).toEqual(['gbrain_try_jsonb(text)']);
      } finally {
        await session.unsafe('ROLLBACK');
        session.release();
      }
      await withEnv({ GBRAIN_SCHEMA_LOCK_TIMEOUT_SECONDS: '1' }, () => v277.handler!(engine));
      expect((await catalog(conn)).withoutTemp).toEqual([]);
    } finally {
      await holder.end();
      await engine.disconnect();
    }
  }, 120_000);

  for (const [entry, migrate] of [
    ['initSchema', (engine: PostgresEngine) => engine.initSchema()],
    ['runMigrations (the locked apply-migrations and upgrade path)', (engine: PostgresEngine) => runMigrations(engine)],
  ] as const) {
    test(`8. an upgrade through ${entry} whose migrations re-create guard functions and then fail at v276 still leaves pg_temp on every pinned path`, async () => {
      const brain = await freshBrain();
      const engine = new PostgresEngine();
      try {
        await brain.conn.unsafe(`DROP INDEX persistence_requests_foreground_committed;
          CREATE TABLE persistence_requests_foreground_committed (x int);
          UPDATE config SET value = '263' WHERE key = 'version';`);
        await engine.connect({ database_url: brain.url });
        const { value: failure } = await capturingStderr(() => migrate(engine).then(() => null, (e: unknown) => e));
        expect(String((failure as Error | null)?.message)).toContain('Index persistence_requests_foreground_committed is not valid');
        const [version] = await brain.conn.unsafe<Array<{ value: string }>>(`SELECT value FROM config WHERE key = 'version'`);
        expect(version!.value).toBe('275');
        expect((await catalog(brain.conn)).withoutTemp).toEqual([]);
      } finally {
        await engine.disconnect();
        await brain.conn.end();
      }
    }, 300_000);
  }

  test('9. a migration run at the latest version (nothing pending) still puts pg_temp back on a function a failed earlier run left without it', async () => {
    const brain = await freshBrain();
    const engine = new PostgresEngine();
    try {
      await brain.conn.unsafe('ALTER FUNCTION gbrain_refuse_purged_fact() SET search_path = pg_catalog, public');
      expect((await catalog(brain.conn)).withoutTemp).toEqual(['gbrain_refuse_purged_fact()']);
      await engine.connect({ database_url: brain.url });
      const { value } = await capturingStderr(() => runMigrations(engine));
      expect(value).toEqual({ applied: 0, current: LATEST_VERSION });
      expect((await catalog(brain.conn)).withoutTemp).toEqual([]);
    } finally {
      await engine.disconnect();
      await brain.conn.end();
    }
  }, 300_000);
});