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

What should I check, step by step?

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 isn't the query wrapper 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.

Do read-only hints and annotations make a tool safe?

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.