Recipe: Read-only database access

The problem

You want your agent to help investigate production issues by running SELECTs against the database. You never want it to INSERT, UPDATE, DELETE, DROP, TRUNCATE, GRANT, REVOKE, ALTER, or CREATE anything. Naive patterns fail because a prompt-injected or hallucinated agent can smuggle a write past a single-statement check -- the classic "SELECT 1; DROP TABLE users;" piggyback.

The policy (recommended: semantic class)

The simplest and most portable shape is a single database:read allow plus a default_action: deny fall-through. The SDK derives the canonical SQL semantic class (read|write|admin|exec) from the sql argument, so this one rule covers SELECT, EXPLAIN, SHOW, DESCRIBE, and CTE in any dialect.

version: '1'
settings:
  default_action: deny
  default_on_missing: deny
  default_on_tamper: quarantine
rules:
  - id: read-only-db
    allow: 'database:read'
    reason: 'Read-only database access: SELECT / EXPLAIN / SHOW / CTE / DESCRIBE allowed via the canonical read class.'

A SQL-injection piggyback like SELECT 1; DROP TABLE users resolves to database:admin (most-dangerous-class wins) on controlzero 1.13.13 and later, so it does not match the database:read allow and default_action: deny fires. See Canonical Tool Names for the full mapping.

Version-dependent behavior

On controlzero 1.13.12 and earlier, this same policy allows destructive SQL when a method is passed to guard(). The method-derived class (database:read from method="query") satisfied the allow rule before the SQL extractor's database:admin class was consulted. Upgrade to 1.13.13+ where the most-dangerous-class always governs.

If you cannot upgrade immediately, the following three-rule shape denies destructive SQL on every published release up to and including 1.13.12 (driven and verified):

rules:
  - id: no-destructive-sql
    deny: 'database:admin'
  - id: no-writes
    deny: 'database:write'
  - id: read-only-db
    allow: 'database:read'

With explicit deny: database:admin and deny: database:write rules preceding the allow, DROP, DELETE, TRUNCATE, GRANT, and piggyback statements like SELECT 1; DROP TABLE x all resolve to DENY while plain SELECT still resolves to ALLOW. This works because the deny rules match the argument-derived class regardless of what method is passed -- the bug only affects the allow-only shape where no deny rule is present to intercept the dangerous class.

Per-keyword form (legacy, hook-check CLI only)

A previous version of this recipe wrote rules against per-keyword actions like database:SELECT, database:WITH, etc. Do not author new policies in this shape for SDK-driven calls. It only matches when the action method literally equals the SQL keyword, which the hook-check CLI produces but normal SDK calls (cz.guard("database", method="query", args={"sql": ...})) do not. SDK calls derive two classes: one from the method (database:query, which maps to the read class), and one from parsing the SQL arguments (e.g. database:admin if the SQL contains DROP TABLE). On controlzero 1.13.13+, the most dangerous of the two governs the decision. A rule keyed on database:SELECT matches neither the method-derived nor the argument-derived class, so the rule list falls through to default_action: deny.

If you have an existing policy authored in the per-keyword shape that worked before 2026-05 and stopped working after, migrate to the canonical class form above. The single database:read allow covers SELECT, EXPLAIN, SHOW, DESCRIBE, and CTE in any dialect, and SQL injection piggybacks resolve to database:admin (most-dangerous-class wins, 1.13.13+) so they correctly fail the no-match check.

Why the canonical form works

The SDK hook receives the agent's SQL as the database tool's sql argument. The canonical tool-extractor scans every statement in the payload (splitting on ;, stripping comments and string literals first), picks the most dangerous keyword it finds, and maps it to one of four semantic classes (read, write, admin, exec). The evaluator then matches rules against both the original database:query action AND the derived database:read class, so a single allow: database:read rule covers every read-shaped statement. SELECT 1; DROP TABLE users; resolves to database:admin (on 1.13.13+, the most dangerous class from any source wins), falls through the allow rule, and default_action: deny fires.

What gets blocked

Agent call	Extracted action	Decision	reason_code
INSERT INTO users (id, name) VALUES (1, 'x')	database:INSERT	deny	NO_RULE_MATCH
UPDATE users SET admin = true	database:UPDATE	deny	NO_RULE_MATCH
DELETE FROM users WHERE id = 1	database:DELETE	deny	NO_RULE_MATCH
DROP TABLE users	database:DROP	deny	NO_RULE_MATCH
SELECT 1; DROP TABLE users;	database:DROP	deny	NO_RULE_MATCH
GRANT ALL ON users TO app_user	database:GRANT	deny	NO_RULE_MATCH
TRUNCATE users	database:TRUNCATE	deny	NO_RULE_MATCH
ALTER TABLE users ADD COLUMN x TEXT	database:ALTER	deny	NO_RULE_MATCH
PIVOT some_table ON column (unknown)	database:*	deny	NO_RULE_MATCH
"" (empty sql)	database:*	deny	NO_RULE_MATCH

What gets allowed

Agent call	Extracted action	Decision	reason_code
SELECT id, name FROM users	database:SELECT	allow	RULE_MATCH
SELECT * FROM t; SELECT * FROM u	database:SELECT	allow	RULE_MATCH
WITH cte AS (SELECT 1) SELECT * FROM cte	database:WITH	allow	RULE_MATCH
SHOW TABLES	database:SHOW	allow	RULE_MATCH
EXPLAIN SELECT * FROM users	database:EXPLAIN	allow	RULE_MATCH
DESCRIBE users	database:DESCRIBE	allow	RULE_MATCH
SELECT 1 /* ; DROP TABLE users; */	database:SELECT	allow	RULE_MATCH

Test it yourself

# Fetch the exact policy from the recipes catalog (or copy the YAML block above):
curl -O https://docs.controlzero.ai/recipes/read-only-database/policy.yaml
# scenarios.json is not hosted -- build it from the expected-decision table above.

# Requires controlzero >= 1.13.13 (most-dangerous-class fix).
# Check with:
#   controlzero --version
#
# Baseline: no --method. The extractor derives the class purely from SQL:
controlzero test database --policy policy.yaml --args '{"sql":"SELECT id FROM users"}'
# Expected: ALLOW (database:read)

controlzero test database --policy policy.yaml --args '{"sql":"DROP TABLE users"}'
# Expected: DENY (database:admin)

# With --method query (simulates an SDK call passing method="query").
# On 1.13.13+ the argument-derived class wins when it is more dangerous:
controlzero test database --method query --policy policy.yaml --args '{"sql":"SELECT id FROM users"}'
# Expected: ALLOW (both method and args resolve to read)

controlzero test database --method query --policy policy.yaml --args '{"sql":"DROP TABLE users"}'
# Expected: DENY (args resolve to admin, which outranks method's read class)

If you are not yet on a release that ships the extractor spec, you can still test the policy layer manually with controlzero hook-check and confirm the extracted method lines up with the "Extracted action" column above.

Caveats

• CTE-embedded DML like WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x is a single statement whose first keyword is WITH. The extractor emits database:WITH and this recipe allows WITH, so CTE-embedded writes slip through. Two mitigations: forbid CTE-embedded DML at the tool layer (recommended), or replace the allow: database:WITH rule with a stricter pattern that inspects the body with your own parser before calling guard.
• Stored procedures / functions that wrap writes are invisible to the extractor. CALL rotate_keys() resolves to database:CALL, not to the INSERT inside the procedure. Review the procedure catalog separately.
• Bash-wrapped database shells (for example psql -c "DROP TABLE users") are covered by the Bash extractor, not the database extractor -- see Block outbound network and the Bash:psql family.
• Prompt injection that rewrites the SQL AFTER extraction cannot be caught here. Pair this recipe with a prompt-safety DLP rule.

Related recipes

• Scoped file access
• Block outbound network
• Multi-tenant isolation