Skip to main content

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 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), so it does not match the database:read allow and default_action: deny fires. See Canonical Tool Names for the full mapping.

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 send database:query plus the derived semantic class database:read. A rule keyed on database:SELECT matches neither, 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) 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, falls through the allow rule, and default_action: deny fires.

What gets blocked​

Agent callExtracted actionDecisionreason_code
INSERT INTO users (id, name) VALUES (1, 'x')database:INSERTdenyNO_RULE_MATCH
UPDATE users SET admin = truedatabase:UPDATEdenyNO_RULE_MATCH
DELETE FROM users WHERE id = 1database:DELETEdenyNO_RULE_MATCH
DROP TABLE usersdatabase:DROPdenyNO_RULE_MATCH
SELECT 1; DROP TABLE users;database:DROPdenyNO_RULE_MATCH
GRANT ALL ON users TO app_userdatabase:GRANTdenyNO_RULE_MATCH
TRUNCATE usersdatabase:TRUNCATEdenyNO_RULE_MATCH
ALTER TABLE users ADD COLUMN x TEXTdatabase:ALTERdenyNO_RULE_MATCH
PIVOT some_table ON column (unknown)database:*denyNO_RULE_MATCH
"" (empty sql)database:*denyNO_RULE_MATCH

What gets allowed​

Agent callExtracted actionDecisionreason_code
SELECT id, name FROM usersdatabase:SELECTallowRULE_MATCH
SELECT * FROM t; SELECT * FROM udatabase:SELECTallowRULE_MATCH
WITH cte AS (SELECT 1) SELECT * FROM ctedatabase:WITHallowRULE_MATCH
SHOW TABLESdatabase:SHOWallowRULE_MATCH
EXPLAIN SELECT * FROM usersdatabase:EXPLAINallowRULE_MATCH
DESCRIBE usersdatabase:DESCRIBEallowRULE_MATCH
SELECT 1 /* ; DROP TABLE users; */database:SELECTallowRULE_MATCH

Test it yourself​

# Fetch the exact policy and scenarios from the recipes catalog:
curl -O https://docs.controlzero.ai/recipes/read-only-database/policy.yaml
curl -O https://docs.controlzero.ai/recipes/read-only-database/scenarios.json

# Requires the Python SDK built from the 228-phase3 branch or a
# release that includes the tool-extractor spec. Check with:
#   controlzero --version
#
# Run every scenario through the local enforcer + extractor:
controlzero test-policy policy.yaml --scenarios scenarios.json

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.