0pt/tests/analytics/analyst-sql-guard.test.ts

/**
 * tests/analytics/analyst-sql-guard.test.ts
 * ==========================================
 * BI-P017 — layer 2 (statement admission) against the bypass corpus.
 *
 * BI-P017's DoD: "INSERT, UPDATE, DROP, unsafe functions and unauthorized
 * catalogs fail." That is the floor. This file tests the floor and then keeps
 * going, because the interesting attacks are not `DROP TABLE` — they are
 * `url()`, `merge()`, a quoted identifier, a homoglyph, and a comment placed
 * where the guard and the engine might disagree about where the query ends.
 *
 * What this file does NOT prove: that the SYSTEM is safe. It proves the guard
 * refuses these statements. The system claim is
 * tests/integration/analyst-sql-clickhouse.integration.test.ts, which runs the
 * same corpus against a live ClickHouse with this layer REMOVED.
 */
import { describe, expect, it } from 'vitest';
import { SiaxError } from '@/src/platform/errors';
import {
  guardAnalystStatement,
  stripSqlText,
  type StatementGuardOptions,
} from '@/src/analytics/sql/statement-guard';
import {
  AnalyticsSQLSession,
  ANALYST_SQL_ROLE,
  type AnalystStatementRecord,
  type ReadOnlySqlEndpoint,
} from '@/src/analytics/sql/analyst-sql-session';
import { tenantSqlCatalogDdl, tenantSqlUserDdl } from '@/src/analytics/sql/tenant-catalog';
import { BYPASS_CORPUS, GUARD_ADMITS_ENGINE_REFUSES, LEGITIMATE_CORPUS } from './analyst-sql-bypass-corpus';

const OPTS: StatementGuardOptions = {
  allowedCatalog: 't_alpha_sql',
  allowedObjects: ['sales', 'events'],
};

describe('BI-P017 layer 2 — the bypass corpus is refused', () => {
  it('covers every attack family the DoD names, and more', () => {
    // A corpus that shrank silently would make this suite pass by testing
    // nothing. Pin the size and the presence of the named families.
    expect(BYPASS_CORPUS.length).toBeGreaterThanOrEqual(55);
    const ids = new Set(BYPASS_CORPUS.map((a) => a.id));
    for (const required of [
      'insert',
      'alter-delete',
      'create-view-escape',
      'tf-url',
      'system-tables',
      'cross-catalog-direct',
    ]) {
      expect(ids.has(required)).toBe(true);
    }
    expect(new Set(BYPASS_CORPUS.map((a) => a.sql)).size).toBe(BYPASS_CORPUS.length);
  });

  it.each(BYPASS_CORPUS.map((a) => [a.id, a.goal, a.sql] as const))('refuses %s (%s)', (_id, _goal, sql) => {
    let thrown: unknown;
    try {
      guardAnalystStatement(sql, OPTS);
    } catch (e) {
      thrown = e;
    }
    expect(thrown, 'statement was ADMITTED — this is a bypass').toBeInstanceOf(SiaxError);
    const err = thrown as SiaxError;
    expect(['AUTHORIZATION_DENIED', 'INVALID_REQUEST']).toContain(err.code);
    // A refusal with an empty reason is unreviewable.
    expect(err.message.length).toBeGreaterThan(20);
  });
});

describe('BI-P017 layer 2 — what the guard honestly does NOT catch', () => {
  // Recorded, not hidden. The guard admits these; the engine refuses them
  // because the view does not project the column. The claim is proven in
  // tests/integration/analyst-sql-clickhouse.integration.test.ts, not here.
  it.each(GUARD_ADMITS_ENGINE_REFUSES.map((a) => [a.id, a.sql] as const))(
    'the guard admits %s — layers 3/4 are what refuse it',
    (_id, sql) => {
      expect(() => guardAnalystStatement(sql, OPTS)).not.toThrow();
    },
  );
});

describe('BI-P017 layer 2 — legitimate analyst work is admitted', () => {
  it.each(LEGITIMATE_CORPUS.map((a) => [a.id, a.sql] as const))('admits %s', (_id, sql) => {
    const guarded = guardAnalystStatement(sql, OPTS);
    expect(guarded.referencedObjects.length).toBeGreaterThan(0);
    for (const obj of guarded.referencedObjects) {
      expect(obj.startsWith('t_alpha_sql.')).toBe(true);
    }
  });

  it('a guard that refused everything would fail this file', () => {
    // Stated explicitly because "all tests pass" is compatible with a guard
    // that throws unconditionally, and that guard would be useless.
    expect(LEGITIMATE_CORPUS.length).toBeGreaterThanOrEqual(10);
  });
});

describe("BI-P017 — literal and comment handling matches ClickHouse's lexer", () => {
  it('does not see keywords that are inside a string literal', () => {
    const g = guardAnalystStatement(
      "SELECT count() AS n FROM t_alpha_sql.sales WHERE customer = 'DROP TABLE system.tables'",
      OPTS,
    );
    expect(g.stripped).not.toContain('DROP');
    expect(g.stripped).not.toContain('system');
  });

  it('masks literals in the normalized form so statement history is not a data dump', () => {
    const g = guardAnalystStatement(
      "SELECT customer FROM t_alpha_sql.sales WHERE customer = 'Anna Andersson, 19850101-1234'",
      OPTS,
    );
    expect(g.normalized).not.toContain('Andersson');
    expect(g.normalized).toContain('?');
    expect(g.raw).toContain('Andersson'); // the raw form is retained deliberately
  });

  it('handles doubled and backslash-escaped quotes without losing the statement end', () => {
    expect(stripSqlText("SELECT 'a''b'").stripped).toBe("SELECT ''");
    expect(stripSqlText("SELECT 'a\\'b'").stripped).toBe("SELECT ''");
    // The classic: an escaped quote that a naive stripper would treat as the
    // close, leaving `; DROP …` outside a literal in the guard's view but
    // inside one in the engine's. Both must agree it stays inside.
    expect(stripSqlText("SELECT 'a\\'; DROP TABLE x; --'").stripped).toBe("SELECT ''");
  });

  it('refuses rather than guesses when a literal or comment never ends', () => {
    expect(() => guardAnalystStatement("SELECT * FROM t_alpha_sql.sales WHERE c = 'x", OPTS)).toThrowError(
      SiaxError,
    );
    expect(() => guardAnalystStatement('SELECT * FROM t_alpha_sql.sales /* x', OPTS)).toThrowError(SiaxError);
  });

  it('treats a comment as whitespace, so it cannot glue two identifiers together', () => {
    // `a/**/b` must not become `ab`.
    expect(stripSqlText('a/**/b').stripped).toBe('a b');
    // A line comment leaves its terminating newline in place, so the result is
    // "a \nb" — separated, which is the property that matters.
    expect(stripSqlText('a--x\nb').stripped).toMatch(/^a\s+b$/);
    expect(stripSqlText('a--x\nb').stripped).not.toContain('ab');
  });
});

describe('BI-P017 — session role gate', () => {
  const endpoint: ReadOnlySqlEndpoint = {
    engine: 'clickhouse',
    async execute() {
      return { columns: ['n'], rows: [[1]] };
    },
    async cancel() {
      return true;
    },
  };
  const audit = {
    records: [] as AnalystStatementRecord[],
    record(e: AnalystStatementRecord) {
      this.records.push(e);
    },
  };

  const open = (roles: string[] | undefined) =>
    new AnalyticsSQLSession({
      identity: { tenant_id: 't_alpha', principal_id: 'u_1', roles },
      endpoint,
      catalog: 't_alpha_sql',
      objects: ['sales', 'events'],
      audit,
    });

  it('refuses to open without the analyst role', () => {
    for (const roles of [undefined, [], ['analytics.reader'], ['admin'], ['analytics.sql.analystX']]) {
      try {
        open(roles as string[] | undefined);
        throw new Error(`session opened with roles=${JSON.stringify(roles)} — role gate is not closed`);
      } catch (e) {
        expect(e).toBeInstanceOf(SiaxError);
        expect((e as SiaxError).code).toBe('AUTHORIZATION_DENIED');
      }
    }
  });

  it('opens with the analyst role', () => {
    expect(open([ANALYST_SQL_ROLE])).toBeInstanceOf(AnalyticsSQLSession);
  });

  it('refuses to open without a verified tenant or principal', () => {
    for (const identity of [
      { tenant_id: '', principal_id: 'u_1', roles: [ANALYST_SQL_ROLE] },
      { tenant_id: '   ', principal_id: 'u_1', roles: [ANALYST_SQL_ROLE] },
      { tenant_id: 't_alpha', principal_id: '', roles: [ANALYST_SQL_ROLE] },
    ]) {
      expect(
        () =>
          new AnalyticsSQLSession({
            identity,
            endpoint,
            catalog: 't_alpha_sql',
            objects: ['sales'],
            audit,
          }),
      ).toThrowError(SiaxError);
    }
  });
});

describe('BI-P017 — audit and statement history', () => {
  function makeSession(opts?: { retainRaw?: boolean }) {
    const records: AnalystStatementRecord[] = [];
    const session = new AnalyticsSQLSession({
      identity: {
        tenant_id: 't_alpha',
        principal_id: 'u_1',
        roles: [ANALYST_SQL_ROLE],
        correlation_id: 'c_1',
      },
      endpoint: {
        engine: 'clickhouse',
        async execute() {
          return { columns: ['n'], rows: [[7]], rows_read: 42, bytes_read: 4242 };
        },
        async cancel() {
          return true;
        },
      },
      catalog: 't_alpha_sql',
      objects: ['sales', 'events'],
      audit: { record: (e) => void records.push(e) },
      retainRawStatements: opts?.retainRaw,
    });
    return { session, records };
  }

  it('records a REFUSAL, not only a success — a guard whose rejections are invisible cannot be reviewed', async () => {
    const { session, records } = makeSession();
    await expect(session.query('SELECT * FROM system.tables')).rejects.toBeInstanceOf(SiaxError);
    expect(records).toHaveLength(1);
    expect(records[0].outcome).toBe('refused');
    expect(records[0].error_code).toBe('AUTHORIZATION_DENIED');
    expect(records[0].refusal_reason).toBeTruthy();
    // Even a refused statement is masked before it is stored.
    expect(records[0].normalized).not.toContain("'");
  });

  it('records the executed statement with objects, timings and byte counts', async () => {
    const { session, records } = makeSession();
    const out = await session.query('SELECT count() AS n FROM t_alpha_sql.sales');
    expect(out.rows).toEqual([[7]]);
    expect(records).toHaveLength(1);
    expect(records[0].outcome).toBe('succeeded');
    expect(records[0].referenced_objects).toEqual(['t_alpha_sql.sales']);
    expect(records[0].rows_read).toBe(42);
    expect(records[0].bytes_read).toBe(4242);
    expect(records[0].statement_hash).toMatch(/^[0-9a-f]{64}$/);
    expect(records[0].correlation_id).toBe('c_1');
  });

  it('omits the raw statement when retention is off, but never the normalized one', async () => {
    const { session, records } = makeSession({ retainRaw: false });
    await session.query("SELECT count() AS n FROM t_alpha_sql.sales WHERE customer = 'Anna'");
    expect(records[0].raw).toBeUndefined();
    expect(records[0].normalized).toContain('?');
    expect(records[0].normalized).not.toContain('Anna');
  });

  it('applies every limit BI-P017 names, with readonly=1 (not 2, which permits SETTINGS changes)', async () => {
    let seen: Record<string, string> = {};
    const session = new AnalyticsSQLSession({
      identity: { tenant_id: 't_alpha', principal_id: 'u_1', roles: [ANALYST_SQL_ROLE] },
      endpoint: {
        engine: 'clickhouse',
        async execute(args) {
          seen = args.settings;
          return { columns: [], rows: [] };
        },
        async cancel() {
          return true;
        },
      },
      catalog: 't_alpha_sql',
      objects: ['sales'],
      audit: { record: () => undefined },
    });
    await session.query('SELECT count() AS n FROM t_alpha_sql.sales');
    expect(seen.readonly).toBe('1');
    for (const key of [
      'max_execution_time',
      'max_result_rows',
      'max_rows_to_read',
      'max_bytes_to_read',
      'max_memory_usage',
    ]) {
      expect(Number(seen[key])).toBeGreaterThan(0);
    }
    // A limit that returns a PARTIAL result silently is a false green.
    expect(seen.timeout_overflow_mode).toBe('throw');
    expect(seen.result_overflow_mode).toBe('throw');
    expect(seen.read_overflow_mode).toBe('throw');
  });

  it('a CL0UD quota may only tighten the budget, never loosen it', () => {
    const session = new AnalyticsSQLSession({
      identity: {
        tenant_id: 't_alpha',
        principal_id: 'u_1',
        roles: [ANALYST_SQL_ROLE],
        quota: { maxRowsToRead: 10, maxExecutionTimeSec: 999_999 },
      },
      endpoint: {
        engine: 'clickhouse',
        async execute() {
          return { columns: [], rows: [] };
        },
        async cancel() {
          return true;
        },
      },
      catalog: 't_alpha_sql',
      objects: ['sales'],
      audit: { record: () => undefined },
    });
    expect(session.resourceBudget.maxRowsToRead).toBe(10);
    expect(session.resourceBudget.maxExecutionTimeSec).toBeLessThanOrEqual(60);
  });

  it('refuses to cancel a statement belonging to another session', async () => {
    const { session } = makeSession();
    await expect(session.cancel('sql_deadbeefcafe_notmine')).rejects.toBeInstanceOf(SiaxError);
  });
});

describe('BI-P017 layers 3 and 4 — the DDL that holds when the guard does not', () => {
  const spec = {
    sourceDatabase: 'opt_analytics',
    tenantId: 't_alpha',
    catalog: 't_alpha_sql',
    objects: [{ table: 'opt_analytics_event', columns: ['event_id', 'metric', 'value', 'event_time'] }],
  };

  it('bakes the tenant predicate into every view definition', () => {
    const ddl = tenantSqlCatalogDdl(spec);
    const view = ddl.find((s) => s.startsWith('CREATE OR REPLACE VIEW'));
    expect(view).toContain("WHERE tenant_id = 't_alpha'");
    expect(view).toContain('FROM opt_analytics.opt_analytics_event');
  });

  it('refuses to project tenant_id into a single-tenant view', () => {
    expect(() =>
      tenantSqlCatalogDdl({ ...spec, objects: [{ table: 'x', columns: ['tenant_id', 'v'] }] }),
    ).toThrowError(SiaxError);
  });

  it('refuses a tenant id that is not catalog-safe rather than embedding it', () => {
    for (const bad of ["t_alpha'; DROP", 't alpha', "'", '', "t_alpha' OR '1'='1"]) {
      expect(() => tenantSqlCatalogDdl({ ...spec, tenantId: bad })).toThrowError(SiaxError);
    }
  });

  it('refuses to point the tenant catalog at the shared source database', () => {
    expect(() => tenantSqlCatalogDdl({ ...spec, catalog: 'opt_analytics' })).toThrowError(SiaxError);
  });

  it('grants SELECT only on the tenant catalog and pins a row policy to the base table', () => {
    const ddl = tenantSqlUserDdl({ ...spec, userName: 't_alpha_sql_user', password: 'x' });
    expect(ddl.some((s) => s.includes('GRANT SELECT ON t_alpha_sql.* TO t_alpha_sql_user'))).toBe(true);
    // The wide grant that would make the whole design pointless:
    expect(ddl.some((s) => s.includes('GRANT SELECT ON opt_analytics.* '))).toBe(false);
    expect(
      ddl.some((s) => s.includes('CREATE ROW POLICY') && s.includes("USING tenant_id = 't_alpha'")),
    ).toBe(true);
    expect(ddl[0]).toContain('readonly = 1');
  });

  it('never embeds a password containing a quote or a backslash', () => {
    for (const bad of ["a'b", 'a\\b']) {
      expect(() => tenantSqlUserDdl({ ...spec, userName: 'u', password: bad })).toThrowError(SiaxError);
    }
  });
});