~ knotless Search/
↑↓ move↵ open⇧↵ all results

grain.social/grain-support-agent

grain's support agent: triages mentions and reports, investigates bugs, proposes fixes

1 collaborator
0
0
main
83 lines · 3.6 KB Raw History
import { test } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { mkdtempSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { isHidden, MODERATION_TABLES, PRIVATE_TABLES, query, refuse } from "../src/grain-db.ts";

// An index with a public table, an FTS table, and every private and moderation one, in WAL
// mode as grain-serve keeps it.
const path = join(mkdtempSync(join(tmpdir(), "grain-db-")), "index.db");
const w = new DatabaseSync(path);
w.exec(`
  PRAGMA journal_mode = WAL;
  CREATE TABLE photo (uri TEXT PRIMARY KEY, did TEXT);
  WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 500)
    INSERT INTO photo SELECT 'at://did:plc:a/social.grain.photo/' || n, 'did:plc:a' FROM c;
  CREATE VIRTUAL TABLE profile_search USING fts5(handle);
  INSERT INTO profile_search VALUES ('someone.bsky.social');
`);
for (const table of [...PRIVATE_TABLES, ...MODERATION_TABLES]) w.exec(`CREATE TABLE ${table} (secret TEXT); INSERT INTO ${table} VALUES ('hunter2');`);
w.close();

test("statements must be a single read", () => {
  assert.equal(refuse("SELECT COUNT(*) FROM photo"), undefined);
  assert.equal(refuse("with x as (select 1) select * from x;"), undefined);
  for (const sql of ["", "DELETE FROM photo", "PRAGMA table_info(photo)", "SELECT 1; DROP TABLE x", "ATTACH '/etc/passwd' AS p", "VACUUM INTO '/tmp/copy.db'"]) {
    assert.ok(refuse(sql), sql);
  }
});

test("public tables read, through FTS and the table-valued pragmas too", () => {
  assert.equal(query(path, "SELECT count(*) AS n FROM photo").rows[0].n, 500);
  assert.equal(query(path, "SELECT handle FROM profile_search WHERE profile_search MATCH 'someone'").rows.length, 1);
  assert.ok(query(path, "SELECT name FROM pragma_table_info('photo')").rows.length);
});

test("no statement reads a private or moderation table, however it names it", () => {
  for (const table of [...PRIVATE_TABLES, ...MODERATION_TABLES]) {
    for (const sql of [
      `SELECT * FROM ${table}`,
      `SELECT count(*) FROM "${table}"`,
      `SELECT * FROM [${table.toUpperCase()}]`,
      `SELECT * FROM main.\`${table}\``,
      `SELECT * FROM photo WHERE did IN (SELECT secret FROM ${table})`,
      `WITH x AS (SELECT * FROM ${table}) SELECT * FROM x`,
      `SELECT * FROM photo p JOIN ${table} t ON 1`,
      `EXPLAIN SELECT secret FROM ${table}`,
      `SELECT name FROM pragma_table_info('${table}')`,
    ]) {
      assert.throws(() => query(path, sql), /cannot be read/, sql);
    }
  }
  // Nor the raw pages the file holds them in.
  assert.throws(() => query(path, "SELECT * FROM dbstat"));
});

test("hidden tables are known by name, in any case", () => {
  assert.ok(isHidden("oauth_key"));
  assert.ok(isHidden("Web_Session"));
  assert.equal(isHidden("photo"), false);
  assert.equal(isHidden("profile"), false);
});

test("reports and classifier verdicts are closed to SQL: a model reads them only through the brief", () => {
  assert.ok(isHidden("report"));
  assert.ok(isHidden("Classification"));
  for (const sql of [
    "SELECT * FROM report",
    "SELECT secret FROM classification",
    "SELECT p.uri FROM photo p WHERE EXISTS (SELECT 1 FROM report r WHERE r.secret = p.did)",
    "SELECT name FROM pragma_table_info('report')",
  ]) {
    assert.throws(() => query(path, sql), /moderation tables \(report, classification\)/, sql);
  }
});

test("query caps rows and cannot write", () => {
  const { rows, truncated } = query(path, "SELECT uri FROM photo");
  assert.equal(rows.length, 200);
  assert.equal(truncated, true);
  assert.throws(() => query(path, "WITH x AS (SELECT 1) INSERT INTO photo (uri) SELECT * FROM x"));
});