# How do I restrict an MCP database tool to read-only queries?

**Short answer:** Enforce read-only in the database, not in the MCP server's prompt or its query wrapper. Connect the server with a database role that can only SELECT (in PostgreSQL, GRANT SELECT on the schemas the agent needs, or the predefined pg_read_all_data role, and nothing else), and point it at a read replica when you have one: on a PostgreSQL hot standby every transaction is read-only and can't be switched to read-write. A wrapper that runs the agent's SQL inside BEGIN TRANSACTION READ ONLY is not a boundary on its own: Datadog Security Labs showed in August 2025 that Anthropic's archived reference Postgres MCP server could be bypassed with COMMIT; DROP SCHEMA public CASCADE;, because it sent stacked statements in one string. Then trim what the agent sees: turn on the server's read-only mode if it has one, and remove the write tools from the client, so an agent that needs one query tool isn't handed twenty.

## Checklist
1. Create a dedicated role for the agent with SELECT only: GRANT USAGE on the schema and GRANT SELECT on the tables or views it needs (PostgreSQL 14 and later also offer the predefined pg_read_all_data role). Don't let the role own tables, and don't give it INSERT, UPDATE, DELETE, TRUNCATE or CREATE.
2. Prefer a read replica: on a PostgreSQL hot standby, transaction_read_only is always on and BEGIN READ WRITE fails, even for a superuser, so a write can't land on your data whatever SQL the agent sends.
3. Treat session settings as a second fence, not the wall: default_transaction_read_only = on can be overridden by the session itself (BEGIN READ WRITE), and a read-only transaction wrapper can be closed with a stacked COMMIT.
4. Use a server that sends one statement per query through PostgreSQL's extended protocol (prepared statements), which rejects stacked statements. Avoid the archived @modelcontextprotocol/server-postgres package where writes must be prevented.
5. Turn on the server's own read-only mode where it exists, and check it took effect: Supabase's hosted MCP server, for example, takes ?read_only=true (with an equals sign) to run SQL as a read-only Postgres user and disable mutating tools such as apply_migration, plus ?project_ref= to scope it to one project and ?features= to load only the tool groups you need.
6. Remove the tools the task doesn't need in the client. In Claude Code, a deny rule with a bare MCP tool name, such as mcp__supabase__apply_migration, removes that tool from the model's context; other clients let you switch individual tools off per server.
7. Limit what reads can reach: expose views instead of raw tables, leave out columns with secrets or personal data, use row-level security for tenant data, and don't grant EXECUTE on SECURITY DEFINER functions that write, since a SELECT can call a function.
8. Bound every query: a statement_timeout and a row limit on the role or in the server, so a careless SELECT can't lock up the database or pull a whole table into the agent's context.

## Why the query wrapper isn't enough
- The archived reference server wrapped each query in BEGIN TRANSACTION READ ONLY and rolled back afterwards. The Node.js Postgres client accepts several semicolon-separated statements in one string, so COMMIT ended the read-only transaction and the next statement ran with the role's full rights; the final ROLLBACK had nothing left to undo.
- Datadog also showed session changes such as SET statement_timeout persisting on pooled connections and affecting later queries. A SELECT-only role stops the writes, not that.
- The text of the SQL usually comes from the model, and the model takes instructions from whatever it reads, including a user's message or a tool result. Assume it will eventually send a write, and make sure the database refuses it.

## Read-only hints and annotations
- MCP tool annotations such as readOnlyHint describe what a server says a tool does. The MCP specification says clients must treat annotations as untrusted unless they come from trusted servers, so they help a client decide what to confirm but are not a permission.
- A tool named query or run_sql that accepts free-form SQL is only as read-only as the role behind it. Check the role, not the tool name.

## Ask Rightshold (free, no key)
Add the MCP server https://www.rightshold.com/mcp to Claude, Cursor, ChatGPT, Grok or VS Code (one-click setup: https://www.rightshold.com/start?ref=seo), then ask: "Before I connect this database MCP server to my agent, is it a listed server, what tools does it expose, and have they changed since they were recorded?"

```json
{
  "tool": "precall_check",
  "arguments": {
    "url": "<the database MCP server's https URL>",
    "tool": "<optional tool name, for example execute_sql>"
  }
}
```
- The answer combines the external registry entry, a live integrity probe against the recorded tool fingerprint, an invisible-text scan of the definitions and any security flags. It is evidence to decide from, never a finding that a server is read-only or safe.
- check_tool_integrity shows tools added, removed or changed since the definitions were recorded, so a write tool that appears in an update is visible before your agent uses it.
- Delegated and intent keys on Rightshold follow the same rule: an agent gets only the scopes its task needs, never the owner's full key.

_Rightshold does not connect to your database, create roles or enforce permissions in your MCP client; read-only access is enforced by your database role, replica and client settings._

## Sources
- [Datadog Security Labs: SQL injection in the Postgres MCP server (August 2025)](https://securitylabs.datadoghq.com/articles/mcp-vulnerability-case-study-SQL-injection-in-the-postgresql-mcp-server/)
- [PostgreSQL: GRANT](https://www.postgresql.org/docs/current/sql-grant.html)
- [PostgreSQL: Predefined roles (pg_read_all_data)](https://www.postgresql.org/docs/current/predefined-roles.html)
- [PostgreSQL: Hot standby](https://www.postgresql.org/docs/current/hot-standby.html)
- [Supabase docs: MCP server (read_only, project_ref, features)](https://supabase.com/docs/guides/ai-tools/mcp)
- [Claude Code docs: Permissions (MCP tool rules)](https://code.claude.com/docs/en/permissions)
- [MCP specification: Tools (annotations are untrusted)](https://modelcontextprotocol.io/specification/2025-11-25/server/tools)

## Related
- [How to stop an AI coding agent from deleting your production database](https://www.rightshold.com/answers/how-to-stop-an-ai-coding-agent-from-deleting-your-production-database)
- [How to vet an MCP server before installing it](https://www.rightshold.com/answers/how-to-vet-an-mcp-server-before-installing-it)
- [How to detect MCP tool description changes (rug pulls)](https://www.rightshold.com/answers/how-to-detect-mcp-tool-description-changes-rug-pull)
- [How to prevent MCP tool shadowing between servers](https://www.rightshold.com/answers/how-to-prevent-mcp-tool-shadowing-between-servers)

_Not legal advice. Updated 2026-10-29. JSON: https://www.rightshold.com/answers/how-to-restrict-an-mcp-database-tool-to-read-only-queries.json_