testapilocally.com

Safe read-only SQL access for AI agents

How do I give an AI agent read-only access to my database?

Enforce read-only below the agent, so the database refuses writes, rather than relying on a prompt that says "only run SELECT". The options, strongest first:

  1. A database user with only SELECT grants. This is the best fit for shared or production-like databases.
  2. A read-only transaction or connection. This is what the SignOff Studio MCP server does for db_query and db_assert: PostgreSQL BEGIN READ ONLY, MySQL/MariaDB START TRANSACTION READ ONLY, Oracle SET TRANSACTION READ ONLY, SQLite opened read-only, and on SQL Server a transaction that is always rolled back. Every read ends in a rollback.
  3. Parsing the SQL and allowing only reads. This is weak on its own: CTEs with writes, functions with side effects and engine-specific syntax get through. The server uses it only as a first gate (for example, to stop DDL that would commit implicitly in MySQL or Oracle before the transaction could help).

SignOff Studio layers 2 and 3 on top of whatever grants your connection has, so a read-only user plus the server gives you all three.

What else should a safe setup do?

  • One statement per call. SELECT 1; DROP TABLE x is rejected rather than split.
  • Row and cell limits. Results are capped (50 rows and 200 characters per cell by default), so one query can't flood the agent's context.
  • No passwords in the model. The password comes from the workspace .env or the app's vault and is scrubbed from every output.
  • An audit trail. Each call is logged to .signoff/agent-audit.jsonl (tool, target, outcome, time; not the SQL or data).
  • Writes are a separate, opt-in tool. db_execute exists for test databases you choose (Allow AI agent writes). It never runs on connections tagged production, and it does a dry run (rolled back) by default.

Is a prompt instruction like "never modify data" enough?

No. Instructions help the agent choose the right tool, but they aren't a control. A confused or prompt-injected agent can ignore them. Enforcement has to be in the database user or the connection.

Can I hide connection details from the agent?

They're hidden by default. db_connections shows each connection's name, engine, environment tag, whether agent writes are allowed and where the password comes from, but not the host, database or user. To show those, set "db": { "showConnectionDetails": true } in the workspace policy (.signoff/agent-policy.json, git-ignored). The agent refers to connections by name either way.

See also: How can an AI agent verify a database migration? · MCP server overview

Updated 2026-11-19 · This page as Markdown · llms.txt