sql-write-gate

写库前门禁 · Policy firewall for AI agents writing to databases.

Prevent Claude Code, Codex, Cursor and MCP agents from executing unsafe database operations.

  Agent SQL  ──►  sql-write-gate  ──►  ALLOW / BLOCK / APPROVAL  ──►  Database

Deterministic policy engine (sqlglot AST + catalog + policy.yaml). No LLM. No API key.

非生产唯一边界 — Early gate prototype; not the sole production security boundary. 未列语法拒绝 — unsupported / ambiguous SQL → REJECT/BLOCK (fail closed), never silent ALLOW as read-only.

Install

pip install sql-write-gate
pip install 'sql-write-gate[postgres]'   # optional: psycopg
pip install 'sql-write-gate[mysql]'      # optional: pymysql
pip install 'sql-write-gate[mcp]'        # optional: MCP server

From a clone:

pip install -e ".[dev]"                 # or: make install
pip install -e ".[postgres,mysql]"
make test                               # PG/MySQL live tests skip if no service

sql-write-gate check "DELETE FROM users"
# → BLOCKED  rule=delete_without_where

Commands

sql-write-gate check "SQL"       # evaluate SQL; no execute
sql-write-gate hook              # PreToolUse: block raw psql/mysql/…
sql-write-gate mcp               # MCP stdio (query_sql / write_sql)
sql-write-gate proxy --sql "..." # gate then execute if ALLOW
sql-write-gate approve <id>      # human approve then write (once)
sql-write-gate resolve <id> --as succeeded|failed|rejected
sql-write-gate audit             # TIME / SOURCE / OP / TABLE / VERDICT
sql-write-gate init              # scaffold policy.yaml + catalog.json

What it does (current)

• DROP / TRUNCATE / ALTER → BLOCK
• DELETE / UPDATE without WHERE → BLOCK
• Blast-radius COUNT vs update_rows / delete_rows (dialect quoting; fail-closed on estimate error)
• Schema / PII / restricted columns; PII SELECT → REQUIRE_APPROVAL (approve executes once)
• Freshness partitions (dt); range / NOT / OR / UPSERT SET expired → BLOCK
• Nested / data-modifying CTE / SELECT INTO → REJECT (unsupported_sql)
• Approval state machine (SQLite source of truth + JSONL mirror): pending→executing→succeeded|failed|unknown (+ rejected)
• Atomic claim under fcntl.flock + SQLite BEGIN IMMEDIATE (single-host; fail closed without flock)
• Three-state execute outcomes; unknown/executing never auto-retried — use resolve or approve --allow-unknown-retry after manual DB verify
• JSONL audit (redacts URL passwords; records execute failures / unknown; request_id + approval_id + execution_outcome correlation; rotatable)
• Adapters: DuckDB (default), PostgreSQL, MySQL, SQLite

Platform support matrix

Windows: install, CLI evaluate/execute on DuckDB/SQLite/URL backends work. Approval mutations require Unix fcntl flock (plus SQLite transactions); without flock they refuse rather than silently degrading.

Approval outcomes & crash recovery (v0.21)

Recovery rules:

1. Process crash while executing: after TTL (SQL_WRITE_GATE_EXECUTING_TTL_SEC, default 120s) → unknown (via approve --force-unknown-check / next store access). Never silent re-claim that re-runs SQL.
2. Operator path for unknown: verify target DB manually, then either
   • sql-write-gate resolve <id> --as succeeded|failed|rejected (no SQL), or
   • sql-write-gate approve <id> --allow-unknown-retry (explicit re-exec; double-write risk).
3. Default second approve on succeeded / unknown does not write again.

Database URLs

Priority: database= → database_url= → db_path= → POSTGRES_URL → MYSQL_URL → DATABASE_URL → DuckDB default.

Live integration tests (optional locally)

export POSTGRES_URL=postgresql://gate:gate@localhost:5432/writegate
export MYSQL_URL=mysql://gate:gate@127.0.0.1:3306/writegate
pip install -e ".[dev,postgres,mysql]"
make test

Without those services, live tests skip; CI runs Postgres + MySQL service containers.

Policy (default production)

Limits: update_rows: 100, delete_rows: 50. Demo policy (examples/policy.demo.yaml) allows insert/update for walkthroughs.

Guards (any BLOCK wins, else any APPROVAL, else ALLOW):

destructive → schema → pii → freshness → blast_radius → environment

Decision model

ALLOW | BLOCK | REQUIRE_APPROVAL with risk, rule_id, reason, evidence.

Deployment model (trusted executor)

非生产唯一边界 — this gate is not the sole production security control.

Approval privilege (SQL_WRITE_GATE_APPROVAL_TOKEN)

1. On the trusted executor, create a secret file (default .logs/approval.key, or set SQL_WRITE_GATE_APPROVAL_KEY_FILE).
2. When calling approve / resolve / reject, set env SQL_WRITE_GATE_APPROVAL_TOKEN to that file's contents.
3. Missing key file, missing token, or wrong token → refuse (CLI exit non-zero). Correct token → 0.21 behavior.
4. Agents must not receive the key file or token. They may still enqueue REQUIRE_APPROVAL via normal write paths.

Target binding

Approval records store database_config_id (fingerprint). Approve reconnect binds trusted credentials only for the same target. Queue against DB A then change env to DB B → approve fail closed (will not write to B).

SQL support matrix

Anything dangerous or ambiguous not on the supported side → unsupported_sql BLOCK/REJECT (fail closed), never silent ALLOW.

Boundaries (non-goals)

• 非生产唯一边界 — combine with least-privilege DB roles, network isolation, and human workflows
• Not a distributed approval lock, MySQL wire-protocol proxy, or Web UI
• Not an enterprise DQ / lineage / ChatBI / multi-tenant platform

See docs/troubleshooting.md for common failures, unknown recovery, approval token, credentials, and real-DB CI.

See CHANGELOG.md for version history (v0.1 → v0.23).

Ops knobs (v0.23)

Backlog (post-0.23)

• Statement timeout + failed/unknown mapping (0.23)
• Result row/byte caps with truncate flag (0.23)
• Audit request_id / correlation fields (0.23)
• JSONL audit / approvals mirror rotation (0.23)
• Troubleshooting guide (0.23)
• Trusted-executor approval token + key file privilege separation (0.22)
• Approve target fingerprint fail-closed on DATABASE_URL swap (0.22)
• SQL support matrix + unsupported variant regressions (0.22)
• Three-state approve outcomes + unknown ≠ auto-retry (0.21)
• SQLite durable approval store + crash TTL → unknown (0.21)
• Multi-process single-write approve regressions (0.21)
• Real Postgres / MySQL CI services + persist/recheck integration tests (0.20)
• R1–R6 permanent regression (dangerous + safe paths) (0.20)
• Windows support matrix + flock fail-closed (0.20)
• Release gate: wheel install smoke; publish needs test+build on same tag (0.20)
• Deferred: distributed / multi-host approval lock
• Deferred: MySQL wire-protocol proxy
• Deferred: Web UI

许可

MIT。见 LICENSE.