IF Commons GitHub

The guard of a query that only reads

0012Active

0012-sql-guard · owner if · domain lang-sql

The guard

FR-001checkpassing

guard(text, dialect, options?) MUST give an ok value of the parse where the text is exactly one statement that only reads, and an err value of every violation (a span of the text and one sentence) where it is not; a statement only reads where it is a query (SELECT, a set operation, VALUES, or WITH whose body and common table expressions are queries), holds no data change, no definition, no SELECT ... INTO, no locking clause (FOR UPDATE, FOR SHARE), and calls no function of the denied list; a word of a write inside a string, an identifier or a comment MUST NOT be a violation, and a text that does not read in the dialect MUST be a violation with the parser's sentence.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: a statement that only reads is allowed, anything that writes, defines, takes locks or makes a table is refused, and a word of a write in a string, an identifier or a comment is not a violation

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts a statement that only reads is allowed, anything that writes, defines, takes locks or make

Cited by: aide/guard/mod

FR-002checkpassing

More than one statement, no statement and a statement that could not be read MUST each be a violation; a trailing ; MUST NOT be one.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: more than one statement, none and one that could not be read are each a violation, and a trailing semicolon is not

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts more than one statement, none and one that could not be read are each a violation, and a t

Cited by: aide/guard/mod

FR-003checkpassing

The denied functions MUST be, by default, those that sleep, read or write files, take locks, change a sequence or a setting, load code or reach another host (pg_sleep, pg_read_file, pg_read_binary_file, pg_ls_dir, pg_stat_file, lo_import, lo_export, lo_unlink, dblink, dblink_exec, pg_advisory_lock, pg_terminate_backend, pg_cancel_backend, set_config, nextval, setval, load_extension, readfile, writefile, load_file, sleep, benchmark, xp_cmdshell, sys_exec), compared without regard to case and to a schema before the name, and options.deniedFunctions MUST replace the list.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: the denied functions are refused without regard to case or schema, and the list can be replaced

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts the denied functions are refused without regard to case or schema, and the list can be rep

Cited by: aide/guard/mod

FR-004checkpassing

options.allowPragma MUST allow a PRAGMA that reads (no = and no parenthesis after its name) and nothing else of the kind command; where it is not given, a PRAGMA MUST be a violation.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: a PRAGMA that reads is allowed only where it is asked for, and a PRAGMA that sets never is; suite/guard.test.ts: a PRAGMA that lists a table is a read where PRAGMA is allowed, and a call that sets is not

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts a PRAGMA that lists a table is a read where PRAGMA is allowed, and a call that sets is not; passed: suite/guard.test.ts a PRAGMA that reads is allowed only where it is asked for, and a PRAGMA that sets never is

Cited by: aide/guard/mod

FR-005checkpassing

Where options.catalog is given and the statement is a plain SELECT, the guard MUST bind it to the catalog and give every unknown table and unknown column as a violation with the sentence and the suggestion of bind; options.tables MUST restrict the tables that may be read, whatever the catalog; and options.maxNodes MUST limit the size of the statement.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: a plain SELECT is bound to a catalog, the tables can be restricted, and the size of the statement can be limited; suite/guard.test.ts: a common table expression's own name is not a table of the allow-list, while the tables its body reads are still checked, and a name does not shadow a table inside its own definition

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts a common table expression's own name is not a table of the allow-list, while the tables it; passed: suite/guard.test.ts a plain SELECT is bound to a catalog, the tables can be restricted, and the size of the st

Cited by: aide/guard/mod

FR-006checkpassing

refusal(text, dialect, options?) MUST give the sentence of the first violation, with its place (line L, column C), or undefined where the text is allowed; and none of these functions MUST throw for any text.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: refusal gives the sentence and the place of the first violation, and nothing throws for any text in any dialect

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts refusal gives the sentence and the place of the first violation, and nothing throws for an

Cited by: aide/guard/mod

Catalogs from tables

FR-007checkpassing

catalogFromTables(tables, types?) MUST make a catalog of the tables from a list of tables with the columns' names and the types of their own system ({ name, columns: [{ name, type }] }) and a map from those types to the types of a catalog (a type not in the map is unknown, and a map need not be given for the types of a catalog), so that a library whose schema is data makes its catalog in one call.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: a catalog is made from tables described in the types of another system, with a map of types and unknown for the rest

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts a catalog is made from tables described in the types of another system, with a map of type

FR-008checkpassing

The guard MUST allow a query that uses set operations, window functions, any join, NULLS FIRST and LAST, FETCH FIRST and a recursive common table expression where nothing in it writes, and MUST still refuse the same query where a data change stands in it (a WITH that holds an INSERT, UPDATE or DELETE, a SELECT ... INTO in a side of a set operation).

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/guard.test.ts: set operations, window functions, joins, NULLS, FETCH and recursive CTEs are allowed where nothing writes, and refused where a data change or a SELECT INTO stands in them

Proved by: lang-sql/suite/guard.test.ts

Tests: passed: suite/guard.test.ts set operations, window functions, joins, NULLS, FETCH and recursive CTEs are allowed where

Out of scope

  • A cost limit or a timeout of a running query (the connection's), and the rewriting of a query that is refused.

Edge cases

  • SELECT 'DELETE FROM t', SELECT "insert", SELECT 1 -- ; DROP TABLE t: allowed, per FR-001.
  • WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x: a violation, per FR-001.
  • SELECT pg_catalog.pg_sleep(1): a violation, per FR-003.

Assumptions

  • The dialect given is the one the connection speaks; a query that is valid for the connection and not for the dialect is refused, which is the safe error.

Open questions

  • None.

Key entities

  • Guard - the judgment of a query by its tree.
  • Denied function - a function whose call changes or leaves the database.

Success criteria

SC-001success criterion

No query that writes reaches a connection that is behind the guard, and no query that reads is refused for a word in a string.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

aide/guard/mod, Lang Sql, Lang Sql, Estate Query: asking the estate

Titles, headings, exports, tables, columns and requirements. Esc closes.