Skip to content

ADR 0005: Three independent layers keep the agent read-only

Status: accepted (v0.1.0)

Context

Any single control can have a bug or a bypass. ADR 0002 lists real limits of the AST guard; the model is not trusted, so the design must not depend on the guard being perfect.

Decision

A write must get past all three layers, each of which is enforced by a different component:

  1. AST guard (application, sql_guard/ast_guard.py): rejects non-SELECT statements, stacked statements, SELECT INTO and write nodes anywhere in the tree, before a connection is opened.
  2. Database role (Postgres, docker/init-db/003_agent_ro_role.sh): agent_ro is NOSUPERUSER NOBYPASSRLS with GRANT SELECT on the tables in public (plus default privileges for future tables). It has no INSERT/UPDATE/DELETE/TRUNCATE privilege and no sequence privileges. db/session.py connects only as this role, and tests/unit/test_no_raw_agent_ro_bypass.py fails if a regex for engine or connection construction matches any other module under src/ (a text grep, so it is a tripwire, not proof). The role's password is passed with psql -v and :'var' substitution in a quoted heredoc, not interpolated by the shell.
  3. Read-only transaction (Postgres, db/session.py): get_agent_session() runs SET TRANSACTION READ ONLY before yielding the connection. execute_read_only_sql adds SET LOCAL statement_timeout = '3000ms' and also enforces a 3.0 s client-side asyncio.wait_for; a timeout is an observation, not a crash.

Checked against a live Postgres 16 with the compose stack: as agent_ro, DELETE FROM customer fails with permission denied for table customer; SELECT nextval(...) fails with permission denied for sequence ...; SELECT ... FOR UPDATE inside a read-only transaction fails with cannot execute SELECT FOR UPDATE in a read-only transaction. These are statements the AST guard forwards, so they show the lower layers doing independent work.

Gap found and closed: pg_terminate_backend/pg_cancel_backend had no containment layer at all. Unlike pg_sleep/nextval/set_config above, these two were Forwarded by the guard (ADR 0002) with nothing in layers 2 or 3 to stop them: same-role backend signalling needs no special grant on stock Postgres 16, so agent_ro is not restricted from it by role privilege, and a read-only transaction does not prevent signalling another backend either. Confirmed live on a throwaway Postgres: SELECT pid FROM pg_stat_activity WHERE usename='agent_ro' (Forwarded) finds another agent_ro session's pid, and SELECT pg_terminate_backend(pid) (previously Forwarded) kills it — a working, ungated cross-session denial-of-service against the agent's own role, with no elevated privilege needed. This is now closed at layer 1: the AST guard denies both functions by name (forbidden_function, ADR 0002 point 6) rather than relying on layers 2 or 3, since neither actually contains them.

What is automated and what is not:

  • Layer 3 (read-only transaction): automated. tests/integration/test_read_only_enforcement.py::test_agent_session_rejects_write_even_without_the_ast_guard sends a DELETE through get_agent_session() with the guard bypassed and asserts the error matches read-only. Replacing SET TRANSACTION READ ONLY in db/session.py with pass turns it red (mutation-checked in review).
  • Layer 1 (AST guard): automated in tests/unit/test_ast_guard.py.
  • Layer 2 (role grants): manual only. No automated test proves agent_ro is SELECT-only. The test above cannot show it, because the read-only transaction rejects the DELETE first. The permission denied for table customer and permission denied for sequence results above come from psql checks against the compose stack, and the FOR UPDATE result is also a manual check.

Consequences and limits

  • The role layer only protects the data if the grants stay SELECT-only. Anyone who connects the agent with a more privileged AGENT_DATABASE_URL removes layers 2 (and weakens 3); the code does not refuse a superuser URL.
  • SET TRANSACTION READ ONLY is a Postgres session setting the connection itself could change in principle; it is a guard against mistakes and forwarded statements, not against an attacker who already controls the SQL text and the connection. Here the SQL text is filtered by layer 1 and the role has no privilege to abuse.
  • agent_ro can read every table in public (no row- or column-level restrictions), including anything sensitive added later, because grants are ALL TABLES plus default privileges.
  • Reads are not free: an expensive but permitted SELECT is bounded only by the 3 s timeout.
  • Only the public schema is granted; the setup is Pagila-specific and would need review for another database.