Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
16.9 KB
Raw Blame
/**
 * Every public table has row-level security after a fresh install and after
 * the migrations-only upgrade path (#GBRA-95 class: Wave B PR 4's v273 tables
 * shipped with RLS off on Postgres).
 *
 * Supabase grants anon and authenticated default privileges on public tables,
 * so a gbrain table without RLS is readable and writable with the anon key
 * whenever the Data API is on. gbrain enables RLS in three places: the schema
 * blob's DO blocks (replayed by a full initSchema), per-migration DO blocks,
 * and v35's auto-RLS event trigger. The trigger is superuser-only and absent
 * or inert on many managed brains, and `gbrain apply-migrations --yes` runs
 * migrations without replaying the blob, so a migration that creates a table
 * must enable RLS itself. On a superuser test database the trigger exists and
 * hides that gap, so each case here starts from a scratch database where the
 * trigger is pre-created DISABLED (v35 is create-if-absent and converges on
 * it, as it does on a managed brain whose master user created it).
 *
 * The only exemption is a table carrying the documented
 * `GBRAIN:RLS_EXEMPT reason=...` comment (the regex doctor's rls check uses).
 * A new table that relies on the event trigger or on the blob alone fails here.
 * Run: DATABASE_URL=... bun test test/e2e/rls-every-table-postgres.test.ts
 */
import { afterAll, describe, expect, test } from 'bun:test';
import { randomUUID } from 'node:crypto';
import { rmSync } from 'node:fs';
import postgres from '#postgres';
import { assertSafeE2eDatabaseUrl } from '../helpers/db-guard.ts';
import { collect, makeHome, spawnDriver } from '../helpers/apply-migrations-lock-driver.ts';
import { writeSchemaDriver } from '../helpers/apply-migrations-schema-driver.ts';
import { PostgresEngine } from '../../src/core/postgres-engine.ts';
import { LATEST_VERSION } from '../../src/core/migrate.ts';
import { v275 } from '../../src/core/schema-migrations/v275-rls-backfill-unguarded-tables.ts';
import { recreateStep } from '../../src/core/schema-missing-tables.ts';
import { resolveRepairScope, runRepair } from '../../src/core/repair/core.ts';
import { missingTablesRepair } from '../../src/core/repair/missing-tables.ts';
import type { OperationContext } from '../../src/core/ops/contract.ts';
import { withEnv } from '../helpers/with-env.ts';

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

const V273_TABLES = ['page_derivation_spend', 'page_segment_derivation_purge_pages', 'page_segment_derivation_purges', 'page_segment_derivations'];

const scratch: string[] = [];
const homes: string[] = [];
let admin: ReturnType<typeof postgres> | null = null;

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

/** A scratch database whose v35 auto-RLS event trigger exists but never fires. */
async function scratchDatabase(): Promise<{ url: string; conn: ReturnType<typeof postgres> }> {
  assertSafeE2eDatabaseUrl(DATABASE_URL!);
  admin ??= postgres(DATABASE_URL!, { max: 1, prepare: false });
  const name = `gbrain_test_rls_${randomUUID().replaceAll('-', '')}`;
  await admin.unsafe(`CREATE DATABASE ${name}`);
  scratch.push(name);
  const url = urlFor(name);
  const conn = postgres(url, { max: 1, prepare: false, onnotice: () => {} });
  await conn.unsafe(`CREATE FUNCTION auto_enable_rls() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN END $$`);
  await conn.unsafe(`CREATE EVENT TRIGGER auto_rls_on_create_table ON ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION auto_enable_rls()`);
  await conn.unsafe(`ALTER EVENT TRIGGER auto_rls_on_create_table DISABLE`);
  return { url, conn };
}

async function freshInit(url: string): Promise<void> {
  const engine = new PostgresEngine();
  await engine.connect({ database_url: url });
  try { await engine.initSchema(); } finally { await engine.disconnect(); }
}

/** Public base tables without RLS, minus the documented GBRAIN:RLS_EXEMPT ones. */
async function tablesWithoutRls(conn: ReturnType<typeof postgres>): Promise<string[]> {
  const rows = await conn.unsafe<Array<{ relname: string }>>(`
    SELECT c.relname FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
      LEFT JOIN pg_description d ON d.objoid = c.oid AND d.classoid = 'pg_class'::regclass AND d.objsubid = 0
     WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND NOT c.relrowsecurity
       AND (d.description IS NULL OR d.description !~ '^GBRAIN:RLS_EXEMPT\\s+reason=\\S.{3,}')
     ORDER BY c.relname`);
  return rows.map(r => r.relname);
}

async function presentTables(conn: ReturnType<typeof postgres>, names: string[]): Promise<string[]> {
  const rows = await conn.unsafe<Array<{ relname: string }>>(
    `SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE n.nspname = 'public' AND c.relkind = 'r' AND c.relname = ANY($1::text[]) ORDER BY c.relname`, [names]);
  return rows.map(r => r.relname);
}

describeE2E('RLS on every public table (Postgres, no auto-RLS event trigger)', () => {
  afterAll(async () => {
    if (admin) {
      for (const name of scratch) await admin.unsafe(`DROP DATABASE IF EXISTS ${name} WITH (FORCE)`);
      await admin.end();
    }
    for (const home of homes) rmSync(home, { recursive: true, force: true });
  });

  test('a fresh initSchema leaves no public table without RLS', async () => {
    const { url, conn } = await scratchDatabase();
    try {
      await freshInit(url);
      expect(await presentTables(conn, V273_TABLES)).toEqual(V273_TABLES);
      expect(await tablesWithoutRls(conn)).toEqual([]);
    } finally {
      await conn.end();
    }
  }, 300_000);

  test('a brain at v272 upgraded with `gbrain apply-migrations --yes` (no blob replay) leaves no public table without RLS', async () => {
    const { url, conn } = await scratchDatabase();
    try {
      await freshInit(url);
      // Rewind to the schema before v273: drop what v273 and v274 create and stamp version 272.
      await conn.unsafe(`DROP TABLE page_segment_derivation_purge_pages, page_segment_derivation_purges, page_derivation_spend, page_segment_derivations CASCADE`);
      await conn.unsafe(`DROP TRIGGER IF EXISTS page_body_manifest_derivation_scope_retire ON page_body_manifest;
        DROP TRIGGER IF EXISTS page_body_manifest_derivation_scope_delete ON page_body_manifest;
        DROP TRIGGER IF EXISTS pages_derivation_scope_delete ON pages;
        DROP FUNCTION IF EXISTS gbrain_mark_derivation_scope_entry();
        DROP FUNCTION IF EXISTS gbrain_refuse_purging_derivation();
        ALTER TABLE facts DROP CONSTRAINT IF EXISTS facts_source_window_chk, DROP COLUMN IF EXISTS source_window;
        ALTER TABLE pages DROP COLUMN IF EXISTS withheld_source_path;`);
      await conn.unsafe(`UPDATE config SET value = '272' WHERE key = 'version'`);
      expect(await presentTables(conn, V273_TABLES)).toEqual([]);

      const home = makeHome({ engine: 'postgres', database_url: url });
      homes.push(home);
      const run = await collect(spawnDriver(home, writeSchemaDriver(home, { migrations: [], args: ['--yes', '--non-interactive', '--no-autopilot-install', '--json'] })));
      expect(run.code, run.stderr + run.stdout).toBe(0);
      const version = await conn.unsafe<Array<{ value: string }>>(`SELECT value FROM config WHERE key = 'version'`);
      expect(Number(version[0]?.value)).toBe(LATEST_VERSION);

      expect(await presentTables(conn, V273_TABLES)).toEqual(V273_TABLES);
      expect(await tablesWithoutRls(conn)).toEqual([]);
    } finally {
      await conn.end();
    }
  }, 300_000);

  test('every public table with RLS turned off at the version before v275, upgraded with `gbrain apply-migrations --yes`, ends with RLS on', async () => {
    // The upgrade path for tables older than v273: a table whose RLS came only from the blob's blocks or the event
    // trigger is off on a trigger-less brain however old it is. A later migration that creates a table without its own
    // RLS block leaves it off here too.
    const { url, conn } = await scratchDatabase();
    try {
      await freshInit(url);
      const all = await tablesWithoutRls(conn);
      expect(all).toEqual([]);
      await conn.unsafe(`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 = 'r' AND c.relrowsecurity LOOP
          EXECUTE format('ALTER TABLE %s DISABLE ROW LEVEL SECURITY', t.rel);
        END LOOP; END $$`);
      await conn.unsafe(`UPDATE config SET value = $1 WHERE key = 'version'`, [String(v275.version - 1)]);
      expect((await tablesWithoutRls(conn)).length).toBeGreaterThan(100);

      const home = makeHome({ engine: 'postgres', database_url: url });
      homes.push(home);
      const run = await collect(spawnDriver(home, writeSchemaDriver(home, { migrations: [], args: ['--yes', '--non-interactive', '--no-autopilot-install', '--json'] })));
      expect(run.code, run.stderr + run.stdout).toBe(0);
      expect(run.stderr).not.toContain('row-level security is still off');
      expect(await tablesWithoutRls(conn)).toEqual([]);
    } finally {
      await conn.end();
    }
  }, 300_000);

  /** Runs the v275 handler on one reserved session (role, search path) and returns what it wrote to stderr. */
  async function runHandlerAs(conn: ReturnType<typeof postgres>, setup: string): Promise<string> {
    const session = await conn.reserve();
    const stderr: string[] = [];
    const write = process.stderr.write;
    try {
      await session.unsafe(setup);
      const executeRaw = (sql: string, params?: unknown[]) => session.unsafe(sql, params as never[]);
      const transaction = async (fn: (tx: unknown) => Promise<unknown>) => {
        await session.unsafe('BEGIN');
        try {
          const result = await fn({ executeRaw });
          await session.unsafe('COMMIT');
          return result;
        } catch (error) {
          await session.unsafe('ROLLBACK');
          throw error;
        }
      };
      const engine = { kind: 'postgres', executeRaw, transaction };
      process.stderr.write = ((chunk: string) => { stderr.push(String(chunk)); return true; }) as typeof process.stderr.write;
      await v275.handler!(engine as never);
    } finally {
      process.stderr.write = write;
      await session.unsafe('RESET ROLE; RESET search_path');
      session.release();
    }
    return stderr.join('');
  }

  test('v275 skips an exempt table and a table the role cannot protect, and names what is left', async () => {
    const { url, conn } = await scratchDatabase();
    const suffix = randomUUID().replaceAll('-', '').slice(0, 12);
    const noBypass = `gbrain_test_rls_nobypass_${suffix}`;
    const notOwner = `gbrain_test_rls_notowner_${suffix}`;
    try {
      await freshInit(url);
      await conn.unsafe(`ALTER TABLE page_derivation_spend DISABLE ROW LEVEL SECURITY; ALTER TABLE query_cache DISABLE ROW LEVEL SECURITY;
        COMMENT ON TABLE query_cache IS 'GBRAIN:RLS_EXEMPT reason=probe table read by an external reader'`);
      await conn.unsafe(`CREATE ROLE ${noBypass} NOLOGIN NOBYPASSRLS; CREATE ROLE ${notOwner} NOLOGIN BYPASSRLS`);
      for (const role of [noBypass, notOwner]) {
        const stderr = await runHandlerAs(conn, `SET ROLE ${role}`);
        expect(stderr).toContain('v275: row-level security is still off on 1 table(s) (page_derivation_spend)');
        expect(await tablesWithoutRls(conn)).toEqual(['page_derivation_spend']);
      }

      expect(await runHandlerAs(conn, 'SELECT 1')).toBe('');
      expect(await tablesWithoutRls(conn)).toEqual([]);
      const exempt = await conn.unsafe<Array<{ relrowsecurity: boolean }>>(`SELECT relrowsecurity FROM pg_class WHERE oid = 'public.query_cache'::regclass`);
      expect(exempt[0]?.relrowsecurity).toBe(false);
    } finally {
      await conn.end();
      await admin!.unsafe(`DROP ROLE IF EXISTS ${noBypass}`).catch(() => {});
      await admin!.unsafe(`DROP ROLE IF EXISTS ${notOwner}`).catch(() => {});
    }
  }, 300_000);

  test('v275 protects the public table when a same-named table earlier in the search path shadows it', async () => {
    const { url, conn } = await scratchDatabase();
    try {
      await freshInit(url);
      await conn.unsafe(`CREATE SCHEMA shadow; CREATE TABLE shadow.query_cache (LIKE public.query_cache);
        ALTER TABLE shadow.query_cache DISABLE ROW LEVEL SECURITY; ALTER TABLE public.query_cache DISABLE ROW LEVEL SECURITY`);
      expect(await runHandlerAs(conn, 'SET search_path = shadow, public')).toBe('');
      const flags = await conn.unsafe<Array<{ name: string; relrowsecurity: boolean }>>(
        `SELECT n.nspname || '.' || c.relname AS name, c.relrowsecurity FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE c.relname = 'query_cache' ORDER BY 1`);
      expect(flags.map(f => ({ name: f.name, relrowsecurity: f.relrowsecurity }))).toEqual([{ name: 'public.query_cache', relrowsecurity: true }, { name: 'shadow.query_cache', relrowsecurity: true }]);
    } finally {
      await conn.end();
    }
  }, 300_000);

  // Generated by gstack /ship coverage audit (#7625).
  // Value: protects=v275 stops with the typed schema_lock_blocked refusal naming the table when another session holds a
  // lock, changes nothing, and succeeds once the lock is gone; fails_when=the SET LOCAL lock_timeout or the 55P03 mapping
  // is dropped (the upgrade hangs or throws an untyped error); why_new=no test holds a lock against v275; seam=none;
  // no_claim=the blocking-session sample (v275 passes sessions: null) and transaction-pooler behavior
  test('v275 refuses with schema_lock_blocked when another session holds a lock on a table it must protect, and finishes after', async () => {
    const { url, conn } = await scratchDatabase();
    const engine = new PostgresEngine();
    const holder = postgres(url, { max: 1, prepare: false, onnotice: () => {} });
    try {
      await freshInit(url);
      await engine.connect({ database_url: url });
      await conn.unsafe('ALTER TABLE page_derivation_spend DISABLE ROW LEVEL SECURITY');
      const session = await holder.reserve();
      try {
        await session.unsafe('BEGIN; LOCK TABLE page_derivation_spend IN ACCESS SHARE MODE');
        const error = await withEnv({ GBRAIN_SCHEMA_LOCK_TIMEOUT_SECONDS: '1' }, () => v275.handler!(engine).then(() => null, (e: unknown) => e));
        expect(error).toMatchObject({ code: 'schema_lock_blocked', detail: 'v275 on page_derivation_spend' });
        expect(await tablesWithoutRls(conn)).toEqual(['page_derivation_spend']);
      } finally {
        await session.unsafe('ROLLBACK');
        session.release();
      }
      await withEnv({ GBRAIN_SCHEMA_LOCK_TIMEOUT_SECONDS: '1' }, () => v275.handler!(engine));
      expect(await tablesWithoutRls(conn)).toEqual([]);
    } finally {
      await holder.end();
      await engine.disconnect();
      await conn.end();
    }
  }, 300_000);

  test('gbrain repair missing-tables recreates a cache table with RLS on', async () => {
    const { url, conn } = await scratchDatabase();
    const engine = new PostgresEngine();
    try {
      await freshInit(url);
      await engine.connect({ database_url: url });
      for (const table of ['query_cache', 'code_traversal_cache']) {
        await conn.unsafe(`DROP TABLE ${table}`);
        await recreateStep(table)!(engine);
      }
      expect(await presentTables(conn, ['code_traversal_cache', 'query_cache'])).toEqual(['code_traversal_cache', 'query_cache']);
      expect(await tablesWithoutRls(conn)).toEqual([]);

      // Generated by gstack /ship coverage audit (#7625).
      // Value: protects=the real repair apply, which runs the recreate step inside its own transaction and lock timeout,
      // leaves the recreated cache with RLS on; fails_when=protectTables breaks or is skipped when nested in the apply
      // transaction; why_new=the calls above run the step outside any transaction; seam=none;
      // no_claim=the CLI wrapper and preview printing (handler driven through runRepair)
      const home = makeHome({ engine: 'postgres', database_url: url });
      homes.push(home);
      await withEnv({ GBRAIN_HOME: home }, async () => {
        const ctx = { engine, sourceId: 'default', remote: false, dryRun: false, config: { engine: 'postgres', embedding_disabled: true },
          logger: { info() {}, warn() {}, error() {} } } as unknown as OperationContext;
        await conn.unsafe('DROP TABLE query_cache');
        const scope = await resolveRepairScope(engine);
        const preview = await runRepair(ctx, missingTablesRepair, scope, { apply: false, explicit: true });
        const applied = await runRepair(ctx, missingTablesRepair, scope, { apply: true, explicit: true, expect: preview.preview_hash });
        expect(applied.outcomes).toMatchObject({ recreated: 1 });
      });
      expect(await presentTables(conn, ['query_cache'])).toEqual(['query_cache']);
      expect(await tablesWithoutRls(conn)).toEqual([]);
    } finally {
      await engine.disconnect();
      await conn.end();
    }
  }, 300_000);
});