Skip to content

Is a read-only MCP server actually read-only?

Last updated

View as Markdown

Quick answer: Usually not. Most MCP servers enforce read-only with a session setting or a pattern match over the SQL, and both can be got round from inside the session that is supposed to be constrained. What holds is the database refusing the statement, which means the effective privileges of the role the server connects as. Effective, not granted: the usual audit query misses membership, ownership and PUBLIC.

MCP, the Model Context Protocol, is the interface that lets an assistant call tools. A database MCP server exposes your database as one of those tools, and almost all of them advertise a read-only mode.

This page covers one type of server: one that forwards arbitrary SQL to a writable primary. That is the common case and the risky one. A server that exposes fixed, parameterised operations, or one pointed at a physical standby, has different properties. Both are discussed at the bottom of this page.

For the arbitrary-SQL case there are three ways to implement the read-only claim:

  1. Filter the SQL text. Reject anything matching INSERT|UPDATE|DELETE|DROP.
  2. Make the transaction read-only. default_transaction_read_only = on, or BEGIN READ ONLY around each request.
  3. Connect as a role that has no write privileges.

The first is not enforcement. The second is enforced by the server, but it is not an authorization boundary because the constrained session can send the next statement. The third does not depend on what the client sends. All three measurements below were run on PostgreSQL 17.11 in a container.

A session setting is a default, not a boundary

Section titled “A session setting is a default, not a boundary”

The following was run against PostgreSQL 17 in a container, as a non-superuser role that had ALTER ROLE ... SET default_transaction_read_only = on:

SHOW default_transaction_read_only; -> on
INSERT INTO t VALUES (2); -> ERROR: cannot execute INSERT in a
read-only transaction
SET default_transaction_read_only = off;
INSERT INTO t VALUES (3); -> INSERT 0 1

No privilege was needed for the SET. Anything that can send arbitrary SQL, which is the entire point of the tool, can send it. This works only if the server forwards session-control SQL. A server that wraps every request itself and rejects SET is not exposed to it.

The same sequence with the table privileges revoked instead gave permission denied for table t both before and after the SET. That is the difference between a default and a boundary.

A per-request read-only transaction is stronger, and still escapable

Section titled “A per-request read-only transaction is stronger, and still escapable”

BEGIN READ ONLY is not a default the client can flip. PostgreSQL refuses the write itself, and it refuses it inside functions and DO blocks too. Measured on 17.11, as a role that did have INSERT privilege:

BEGIN READ ONLY; INSERT INTO t VALUES (50);
-> ERROR: cannot execute INSERT in a read-only transaction

But the mode is settable, and the rule is about ordering rather than privilege:

BEGIN READ ONLY;
SET TRANSACTION READ WRITE;
INSERT INTO t VALUES (51); -> INSERT 0 1
COMMIT; -> the row is there

Once a query has run in the transaction, it locks:

BEGIN READ ONLY;
SELECT 1;
SET TRANSACTION READ WRITE;
-> ERROR: transaction read-write mode must be set before any query

A server that opens BEGIN READ ONLY and then hands the transaction to client-supplied SQL has given the client the first statement, which can undo the setting. The wrapping holds only if the server also refuses to forward SET TRANSACTION and SET default_transaction_read_only. That is a filter again, with the same problems.

There is also no SET TRANSACTION READ ONLY “per statement”, despite how it is often described. The PostgreSQL 17 SET TRANSACTION reference is exact about the scope: “The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions.” It also states: “If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect.”

The verbs people filter are INSERT, UPDATE, DELETE and DROP. Other writing verbs include CREATE, ALTER, TRUNCATE, GRANT, COPY ... FROM, REFRESH MATERIALIZED VIEW and SELECT ... INTO.

DO belongs on the list for a different reason. It does not write by itself; it runs an anonymous block, and whether that block writes depends on what it contains and what the caller is allowed to do. A filter cannot determine that from the outer statement. The text is a string literal, and deciding what it does means running it.

A plain SELECT can also write if it calls a function that writes. Measured on 17.11: a SECURITY DEFINER function owned by a privileged role inserted a row when called by a role with no INSERT privilege on that table at all.

SELECT sneaky_sd(); -> 98, and the row is in t

The statement is a SELECT. A filter reading the verb sees nothing wrong, because nothing about the text is wrong.

The same call inside a read-only transaction was refused. A function’s writes are subject to the transaction mode, so this escalation concerns privilege rather than evasion of the read-only rule.

BEGIN READ ONLY; SELECT sneaky_sd();
-> ERROR: cannot execute INSERT in a read-only transaction
CONTEXT: SQL function "sneaky_sd" statement 1

A SECURITY DEFINER function runs as its owner. That is a documented delegation mechanism, not a defect, and it can expose one narrow action without granting access to the table. It is privileged code, so treat it accordingly: the PostgreSQL 17 CREATE FUNCTION reference says “Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused”. It recommends setting a safe search_path and revoking the default PUBLIC execute privilege before granting it deliberately.

That default is easy to overlook, so it was measured. Created on 17.11 with no GRANT of any kind, pg_proc.proacl was null and the agent role could call it:

SELECT has_function_privilege('pub_writes()', 'EXECUTE'); -> t
SELECT pub_writes(); -> 70

A null ACL means the built-in default, and the same reference says what that default is: “by default, execute privilege is granted to PUBLIC for newly created functions”. So “we never granted the agent anything” does not mean the agent cannot call it.

Find out what the MCP server is connecting as, then test that role directly with psql. The relevant evidence is what the database server allows, not what the tool does.

SELECT current_user, session_user, current_database();

Do not audit with information_schema.table_privileges

Section titled “Do not audit with information_schema.table_privileges”

This is the usual audit query:

SELECT table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = current_user
ORDER BY table_name, privilege_type;

It lists direct grants to the role, which is not the same as what the role can do. Measured on 17.11, the agent role had SELECT on t and one extra line of setup, GRANT writer TO ai_agent:

the query above -> t | SELECT (one row, nothing else)
has_table_privilege(...) -> SELECT t, INSERT t, UPDATE t, DELETE t
INSERT INTO t VALUES (60) -> INSERT 0 1

The audit said read-only, and the role wrote anyway. Four sources of privilege are invisible to that query:

  • Role membership. Privileges reached through a granted role are not grants directly to your role.
  • Predefined roles. GRANT pg_read_all_data TO ai_agent produced zero rows in the query above for a table from which the role then read all three rows.
  • PUBLIC grants, including the EXECUTE on functions described earlier.
  • Ownership. An owner can drop and alter a table whose ACL mentions nobody.

Ask the database for the role’s effective privileges:

SELECT c.relname,
has_any_column_privilege(c.oid, 'SELECT') AS sel,
has_any_column_privilege(c.oid, 'INSERT') AS ins,
has_any_column_privilege(c.oid, 'UPDATE') AS upd,
has_table_privilege(c.oid, 'DELETE') AS del,
has_table_privilege(c.oid, 'TRUNCATE') AS trunc,
pg_get_userbyid(c.relowner) = current_user AS owned_by_me
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'f')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1;

DELETE and TRUNCATE cannot be granted per column, so they stay on has_table_privilege. The other three use has_any_column_privilege for the reason in the next subsection.

These functions read what the role can do rather than what was written down, so they cover role membership and predefined roles, which the information_schema query does not. They still do not cover everything. Two things defeat them, and both are below: a column-level grant, unless you use the any_column form, and NOINHERIT plus SET ROLE, which no privilege function can see because the privileges are not held until the role asks for them.

A column-level grant is invisible to has_table_privilege

Section titled “A column-level grant is invisible to has_table_privilege”

This is a write that both audit queries call absent. The PostgreSQL 17 access privilege inquiry functions say of has_any_column_privilege: “This succeeds either if the privilege is held for the whole table, or if there is a column-level grant of the privilege for at least one column.”

Measured on 17.11. The agent role had three column grants and nothing else: SELECT (id, body), UPDATE (reviewed) and INSERT (id) on notes.

has_table_privilege('notes','UPDATE') -> f
has_any_column_privilege('notes','UPDATE') -> t
UPDATE notes SET reviewed = true WHERE ... -> UPDATE 1
has_table_privilege('notes','INSERT') -> f
has_any_column_privilege('notes','INSERT') -> t
INSERT INTO notes (id) VALUES (2) -> INSERT 0 1

information_schema.table_privileges returned zero rows for that role in the same session. An audit that runs either of those two queries reports a clean result while the role writes to two columns.

The inventory of column grants is its own view:

SELECT table_schema, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE grantee = current_user
ORDER BY 1, 2, 3, 4;

Then list the memberships themselves, since they are where the unexpected privileges come from:

SELECT parent.rolname AS granted_role, m.inherit_option, m.set_option
FROM pg_auth_members AS m
JOIN pg_roles AS member ON member.oid = m.member
JOIN pg_roles AS parent ON parent.oid = m.roleid
WHERE member.rolname = current_user;

Two columns matter. inherit_option false means the privileges are not automatic, and set_option true means the role can SET ROLE into the other role and have them anyway. Measured on 17.11 with ALTER ROLE ai_agent NOINHERIT:

has_table_privilege('t','INSERT') -> f
SET ROLE writer;
has_table_privilege('t','INSERT') -> t
INSERT INTO t VALUES (80); -> INSERT 0 1

NOINHERIT does not stop the role from writing. Removing the membership does.

Then check ownership and CREATE across every schema, not just public. A role with CREATE somewhere owns what it makes there, and an owner needs no grants:

SELECT n.nspname,
has_schema_privilege(n.oid, 'CREATE') AS can_create,
has_schema_privilege(n.oid, 'USAGE') AS can_use
FROM pg_namespace AS n
WHERE n.nspname NOT LIKE 'pg\_%' AND n.nspname <> 'information_schema'
ORDER BY 1;

Measured on 17.11 with CREATE revoked on public and granted on one other schema, the agent role created a table and owned it. A check that looks only at public returns a clean result and is wrong.

Then check the callable functions. List the ones this role can call, rather than every function that is SECURITY DEFINER:

SELECT n.nspname, p.proname, p.prosecdef,
pg_get_userbyid(p.proowner) AS owner
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND has_function_privilege(p.oid, 'EXECUTE')
ORDER BY p.prosecdef DESC, 1, 2;

Then check the role attributes and predefined roles. The most dangerous is pg_execute_server_program, which lets a role run commands on the database host through COPY ... FROM PROGRAM. Measured on 17.11:

GRANT pg_execute_server_program TO ai_agent;
COPY prog FROM PROGRAM 'id -un'; -> COPY 1
SELECT line FROM prog; -> postgres

That is shell access as the PostgreSQL server account, from a role whose table privileges are SELECT.

Last, try to write. Expect permission denied, and treat anything else as the answer.

Build the role, and let the MCP server connect as it:

CREATE ROLE ai_agent LOGIN PASSWORD 'use-a-generated-secret'
NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE app TO ai_agent;
GRANT USAGE ON SCHEMA public TO ai_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent;

Grant it membership in nothing. This step is a statement you do not run, and a membership grant is what undid the audit above.

REVOKE CREATE ON SCHEMA public FROM PUBLIC is often given as the next step. It does less than that suggests:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

On PostgreSQL 15 and later it usually changes nothing: a new database already gives PUBLIC no CREATE on public. In a database upgraded from 14 or earlier, it removes one broad default from every role in the database, not from the agent, so run it deliberately rather than as part of setting up one credential. It does not prove the agent cannot create anything: a direct grant, a membership, or CREATE on another schema all survive it. The per-schema query above is the check; this is one fix for one of its answers.

Tables created after the GRANT ... ON ALL TABLES are not covered by it, which is the step most setups miss:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT ON TABLES TO ai_agent;

FOR ROLE has to name the role that creates the tables, usually the one migrations run as, and it covers only that role. Measured on 17.11 with the statement above in place:

table created by app_owner -> has_table_privilege SELECT = t
table created by migrator -> has_table_privilege SELECT = f

Repeat it for every identity that creates objects: migrations, the deploy job, an extension’s installer, or a human with psql.

And ON TABLES is one of five object kinds, not the whole feature. The PostgreSQL 17 ALTER DEFAULT PRIVILEGES reference says: “Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.” Measured on 17.11, ON TABLES covered a view created afterwards and did not cover a sequence. So a GRANT USAGE, SELECT ON SEQUENCES line is its own line if the agent reads sequences, and the same is true of functions and types if a later grant is meant to reach them.

Bound the damage a read can do:

ALTER ROLE ai_agent SET statement_timeout = '30s';
ALTER ROLE ai_agent SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE ai_agent SET default_transaction_read_only = on;

Keep the last line. It catches honest mistakes and produces a clearer error than a permission failure. It is the third layer, not the first.

Most of the confusion on this page comes from one word carrying three jobs. Separate them, because the role above only does the first:

  • Integrity: nothing the agent does changes stored data. The role does this.
  • Confidentiality: the agent sees only the data it should. The role above does not do this: SELECT ON ALL TABLES is every row of every column. Use views, column grants, or row-level security. With RLS enabled and no policy that applies, PostgreSQL denies by default, measured on 17.11: a role with SELECT granted on an RLS table read zero of its rows, while the table’s owner read them all, because owners bypass RLS unless the table is FORCE ROW LEVEL SECURITY.
  • Availability: the agent cannot hurt the database. The role above does not do this either.

That last one is often missed, so measure it. A plain SELECT takes an AccessShareLock and holds it for the transaction. Measured on 17.11: with the agent’s SELECT open inside a transaction, the owner’s ALTER TABLE t ADD COLUMN c int waited and then failed on a two second lock_timeout. A read-only credential blocked a migration.

statement_timeout = '30s' bounds duration, not cost. A thirty second query can read the whole table, evict the buffer cache and saturate the disk. If that matters, use a replica; a tighter timeout does not fix it.

The designs, and what each one actually buys

Section titled “The designs, and what each one actually buys”

Not every MCP server forwards arbitrary SQL, and the choice matters more than the configuration:

designintegrity assurancewhat it does not solve
regex or keyword filter over the SQLnone worth relying oncannot decide what DO or a function call will do
default_transaction_read_only on the sessionnone: the client can set it offit is a default, not a boundary
server-controlled BEGIN READ ONLY per requestreal, if the server refuses to forward SETmeasured above: SET TRANSACTION READ WRITE as the first statement escapes it
least-privilege role on the primarystrong against direct writesreads everything it is granted, and can still block DDL
least-privilege role plus a physical standbystrongest: a standby refuses writes whatever the role holdsreplication lag, cost, and confidentiality unchanged
fixed parameterised read tools, no raw SQLstrong, and removes the session-control surfacesomebody has to design and maintain the tools

The last two rows are the most robust options. A physical standby changes the property from “this role has no write privileges today” to “this server cannot commit a write”, which survives a later grant.

The main risk is that the agent reads untrusted text and then acts on it, more than that the model writes bad SQL. A comment in a file, a row in a table, or a page it fetched can contain an instruction, and an agent with a tool that can write will sometimes follow it.

That is why “the model would not do that” is not a control. The model is not the only author of its input.

Treat the MCP server’s credential as the security boundary, and review it like a credential rather than like a configuration file.

Audit effective privileges, never a grant list. Use the privilege inquiry functions and pg_auth_members, not information_schema.table_privileges. Three measured failures on this page are a role that passed the grant list and wrote anyway.

Use the any_column form for SELECT, INSERT and UPDATE, because a column-level grant leaves has_table_privilege false while the write succeeds.

Nothing catches NOINHERIT plus SET ROLE for you. Every privilege function answers for the current role, and SET ROLE changes the current role. Read pg_auth_members and treat membership itself as the privilege.

Check the role, not the tool. A new version of the server, a new configuration flag, or a different server entirely changes nothing about what the role can do.

Point it at a replica where you can, so read-only is also true of the server it reaches.

Re-run the checks above after every schema migration, because new tables do not inherit grants and new functions do not inherit your assumptions.

  • Is a read-only MCP server actually read-only?

    It depends on how it enforces read-only, and the common implementations are not equal. A regex over the SQL text can be worked around. A read-only session setting can be turned off by the client in one statement. Verified on PostgreSQL 17.11, even a per-request BEGIN READ ONLY can be escaped: SET TRANSACTION READ WRITE as the transaction's first statement succeeds and the INSERT after it commits. Least-privilege credentials are the control that holds, because the server refuses the statement rather than the tool refusing to send it.

  • Can a client turn off default_transaction_read_only?

    Yes, and it needs no special privilege. Verified on PostgreSQL 17: a non-superuser role with ALTER ROLE ... SET default_transaction_read_only = on had an INSERT rejected, ran SET default_transaction_read_only = off, and the same INSERT then succeeded. Anything that can send arbitrary SQL can send that SET.

  • Does blocking INSERT, UPDATE and DELETE in the tool make it safe?

    No. Text filtering has to be complete to be worth anything, and the surface is large: CREATE, ALTER, DROP, GRANT, TRUNCATE, COPY, functions that write, and DO blocks that contain anything at all. A filter is a useful way to produce clearer error messages, not a way to bound what a credential can do.

  • Can a SELECT write to the database?

    Yes, if it calls a function that writes and the transaction is not read-only. Verified on PostgreSQL 17.11: SELECT on a SECURITY DEFINER function owned by a privileged role inserted a row for a caller with no INSERT privilege on that table, and the same call inside BEGIN READ ONLY was rejected. The statement is a SELECT, the effect is an INSERT, and no filter looking at the verb will see it.

  • Why is listing table_privileges not enough to audit the role?

    Because it shows direct grants only. Verified on PostgreSQL 17.11: a role granted membership in a writer role listed SELECT and nothing else in information_schema.table_privileges, while has_table_privilege returned true for INSERT, UPDATE and DELETE, and the INSERT succeeded. Membership, ownership, predefined roles such as pg_read_all_data, and PUBLIC grants are all invisible to that query.

  • How do I actually make it read-only?

    Create a login role for the agent, grant CONNECT, USAGE and SELECT only, make sure it owns nothing and is a member of nothing, and check CREATE on every schema rather than only public. Then connect as that role and try to write. The check is the part people skip, and it is the only part that produces evidence rather than a belief.