Skip to content

ADR 0002: sqlglot AST guard, default-deny at the top level

Status: accepted (v0.1.0)

Context

The agent runs SQL that a language model wrote. The first line of defence is to refuse anything that is not a plain read before it reaches Postgres. String and regex checks ("does it start with SELECT?") are easy to defeat, and a check that only inspects the outermost statement misses a Postgres feature: data-modifying CTEs. WITH x AS (DELETE FROM rental RETURNING *) SELECT * FROM x is a single statement whose outer form is SELECT but which deletes rows.

Decision

sql_guard/ast_guard.py parses each query with sqlglot (Postgres dialect) and returns a Decision(verdict, reason, detail). Only Forward reaches the database. In order:

  1. Unparseable input: Deny (unparseable_sql).
  2. Anything other than exactly one statement: Deny (stacked_statements).
  3. Default-deny at the top level: the root node must be exp.Select. Everything else (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CALL, COPY, EXPLAIN, SHOW, and anything sqlglot cannot classify) is Deny (not_a_select) without being enumerated.
  4. SELECT ... INTO: Deny (select_into).
  5. Whole-tree walk: root.find(...) over every node, including CTE bodies, for the denied types Insert, Update, Delete, Drop, Alter, TruncateTable, Merge. Any hit is Deny (forbidden_node_in_tree). This closes the data-modifying-CTE gap above. Merge was added after review showed a MERGE nested in a CTE parses without exposing its DELETE action as a Delete node, so it would otherwise have been forwarded.
  6. Denied functions by name: root.find_all(exp.Func) over the same whole tree, checked case-insensitively against pg_terminate_backend/pg_cancel_backend. Any hit is Deny (forbidden_function). Unlike pg_sleep/set_config/nextval (still forwarded and contained by lower layers, below), these two have no containment layer at all: on stock Postgres 16, one role can signal a backend running as the same role with no extra grant, so a Forwarded pg_terminate_backend/pg_cancel_backend let any agent_ro session kill any other concurrent agent_ro session (found live during review, see ADR 0005). There is no legitimate read-only use of either function, so this is a name-based deny rather than relying on ADR 0005's layers to contain it.
  7. Otherwise Forward (select_only).

The guard is fail-closed on a parse error and is checked before any connection is opened, so a denied query causes no database traffic. Red tests cover each deny class, including CALL, COPY, the CTE-DML case and the MERGE-in-CTE case.

Consequences and limits

  • Default-deny is only true at the root. Nested nodes are checked against an enumerated denylist, not an allowlist. A future or exotic write-capable node type that sqlglot models under a SELECT root and that is not in that tuple would be forwarded. The other two layers (ADR 0005) exist because this one is not sufficient alone.
  • Over-blocking. A top-level UNION/INTERSECT/EXCEPT (root is exp.Union, not Select) and a top-level VALUES are denied as not_a_select, although they are reads. Wrapping in a subquery or CTE passes. The model sees the denial as an observation and can rewrite.
  • Functions are inspected only for the denylist above. SELECT pg_sleep(10), set_config(...) and nextval(...) are still forwarded by the guard; they are contained by the statement timeout, the read-only transaction and the role's privileges respectively (ADR 0005), not by the guard. pg_terminate_backend/pg_cancel_backend were forwarded the same way until this fix, but had no such containment (see point 6 above and ADR 0005), so they are now denied by name instead. Any other function not on the name denylist is still forwarded and relies on the lower layers.
  • Parser differential. The guard reasons about sqlglot's tree; Postgres parses the string itself. A string that sqlglot and Postgres read differently is the classic way to defeat this design. Nothing here rules that out beyond the other layers.
  • MERGE-in-CTE. Whether Postgres 16 accepts a MERGE inside a CTE was not verified against a live server; the deny entry is defensive and costs nothing.
  • Denials carry a reason string that becomes an observation, so the model can self-correct; they also appear in AgentResult.sql_log, which records every submitted well-formed query, including rejected ones.