Read-Only Connections & Injection Prevention

A read-only database connection is the foundation of query safety. Create a dedicated database role with SELECT-only grants on the specific tables and views the MCP server needs. Revoke all other privileges — INSERT, UPDATE, DELETE, CREATE, DROP, TRUNCATE. Then set the connection to read-only mode at the session level (SET default_transaction_read_only = ON in PostgreSQL) as a second guardrail. This way, even if a SQL injection attack constructs a write statement, the database itself rejects it.

Connection-level read-only mode and role-level grants serve different purposes. Role grants determine which objects the user can access. Session-level read-only mode prevents any write operation regardless of grants. Use both: the role limits which tables are visible, and the session setting prevents writes even if the role accidentally has write permissions. Some database drivers also support a readOnly connection option that sets this automatically — use it as a third layer.

Beyond SQL injection, consider indirect write vectors. Certain PostgreSQL functions can write to disk (COPY TO, lo_export), create temporary tables, or trigger side effects through function calls. A properly restricted role should not have access to these, but verify by testing: connect as the MCP role and attempt every operation you want to prevent. Automated tests for permission boundaries are just as important as tests for application logic.

-- Create a dedicated read-only role for the MCP server
CREATE ROLE mcp_reader WITH LOGIN PASSWORD 'rotate-this-regularly';

-- Grant connect to the database
GRANT CONNECT ON DATABASE appdb TO mcp_reader;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO mcp_reader;

-- Grant SELECT on specific tables only
GRANT SELECT ON TABLE customers, orders, products, order_items
  TO mcp_reader;

-- Explicitly revoke everything else
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM mcp_reader;
GRANT SELECT ON TABLE customers, orders, products, order_items
  TO mcp_reader;

-- Set default read-only at the role level
ALTER ROLE mcp_reader SET default_transaction_read_only = ON;

-- Prevent function creation and temp table abuse
REVOKE CREATE ON SCHEMA public FROM mcp_reader;
REVOKE TEMPORARY ON DATABASE appdb FROM mcp_reader;

1. Create the role Create a dedicated database role for MCP with SELECT-only grants. Never reuse the application read-write role — even temporarily.
2. Set session read-only Configure default_transaction_read_only at the role level. Also set the readOnly flag in your connection pool configuration as a backup.
3. Test the boundaries Connect as the MCP role and attempt INSERT, UPDATE, DELETE, CREATE TABLE, COPY, and function calls. Every one should fail. Automate these tests in your CI pipeline.