Is a read-only MCP server actually read-only?
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.
What is the problem?
Section titled “What is the problem?”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:
- Filter the SQL text. Reject anything matching
INSERT|UPDATE|DELETE|DROP. - Make the transaction read-only.
default_transaction_read_only = on, orBEGIN READ ONLYaround each request. - 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.
What the first two actually do
Section titled “What the first two actually do”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; -> onINSERT INTO t VALUES (2); -> ERROR: cannot execute INSERT in a read-only transactionSET default_transaction_read_only = off;INSERT INTO t VALUES (3); -> INSERT 0 1No 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 transactionBut 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 1COMMIT; -> the row is thereOnce 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 queryA 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.”
A text filter has to be complete
Section titled “A text filter has to be complete”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 tThe 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 1A 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'); -> tSELECT pub_writes(); -> 70A 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.
How do I confirm it?
Section titled “How do I confirm 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_typeFROM information_schema.table_privilegesWHERE grantee = current_userORDER 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 tINSERT INTO t VALUES (60) -> INSERT 0 1The 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_agentproduced zero rows in the query above for a table from which the role then read all three rows. PUBLICgrants, including theEXECUTEon functions described earlier.- Ownership. An owner can drop and alter a table whose ACL mentions nobody.
Audit the effective privileges instead
Section titled “Audit the effective privileges instead”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_meFROM pg_class AS cJOIN pg_namespace AS n ON n.oid = c.relnamespaceWHERE 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') -> fhas_any_column_privilege('notes','UPDATE') -> tUPDATE notes SET reviewed = true WHERE ... -> UPDATE 1has_table_privilege('notes','INSERT') -> fhas_any_column_privilege('notes','INSERT') -> tINSERT INTO notes (id) VALUES (2) -> INSERT 0 1information_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_typeFROM information_schema.column_privilegesWHERE grantee = current_userORDER 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_optionFROM pg_auth_members AS mJOIN pg_roles AS member ON member.oid = m.memberJOIN pg_roles AS parent ON parent.oid = m.roleidWHERE 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') -> fSET ROLE writer;has_table_privilege('t','INSERT') -> tINSERT INTO t VALUES (80); -> INSERT 0 1NOINHERIT 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_useFROM pg_namespace AS nWHERE 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 ownerFROM pg_proc AS pJOIN pg_namespace AS n ON n.oid = p.pronamespaceWHERE 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 1SELECT line FROM prog; -> postgresThat 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.
What to do instead
Section titled “What to do instead”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 = ttable created by migrator -> has_table_privilege SELECT = fRepeat 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.
Read-only is three different promises
Section titled “Read-only is three different promises”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 TABLESis 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 withSELECTgranted on an RLS table read zero of its rows, while the table’s owner read them all, because owners bypass RLS unless the table isFORCE 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:
| design | integrity assurance | what it does not solve |
|---|---|---|
| regex or keyword filter over the SQL | none worth relying on | cannot decide what DO or a function call will do |
default_transaction_read_only on the session | none: the client can set it off | it is a default, not a boundary |
server-controlled BEGIN READ ONLY per request | real, if the server refuses to forward SET | measured above: SET TRANSACTION READ WRITE as the first statement escapes it |
| least-privilege role on the primary | strong against direct writes | reads everything it is granted, and can still block DDL |
| least-privilege role plus a physical standby | strongest: a standby refuses writes whatever the role holds | replication lag, cost, and confidentiality unchanged |
| fixed parameterised read tools, no raw SQL | strong, and removes the session-control surface | somebody 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.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”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.
How do I stop it coming back?
Section titled “How do I stop it coming back?”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.