# 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?

**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.

## What the first two actually do

### 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

`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](https://www.postgresql.org/docs/17/sql-set-transaction.html)
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

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](https://www.postgresql.org/docs/17/sql-createfunction.html)
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.

## 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.

```sql
SELECT current_user, session_user, current_database();
```

### Do not audit with `information_schema.table_privileges`

This is the usual audit query:

```sql
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.

### Audit the effective privileges instead

Ask the database for the role's effective privileges:

```sql
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`

This is a write that both audit queries call absent. The
[PostgreSQL 17 access privilege inquiry functions](https://www.postgresql.org/docs/17/functions-info.html)
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:

<!-- sql-check: skip runs as the agent role against its own database -->
```sql
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:

<!-- sql-check: skip runs as the agent role against its own database -->
```sql
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:

```sql
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`:

```sql
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.

## What to do instead

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

```sql
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:

```sql
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:

```sql
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](https://www.postgresql.org/docs/17/sql-alterdefaultprivileges.html)
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:

```sql
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

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

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?

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?

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.

## How DBGorilla helps

DBGorilla connects to your database read-only and exposes it to your AI coding
agent through MCP: live `EXPLAIN` plans, per-table statistics, schema and
topology, blocking sessions, and the query workload attributed to the user that
ran it. When it runs a query for you, that query goes inside a server-side
`BEGIN READ ONLY`, which is the third design in the table above rather than a
switch in the tool, and the escape named there is closed: the query is wrapped
as a subquery and sent over the extended protocol, so a leading `SET TRANSACTION
READ WRITE` does not parse and a second statement cannot be stacked. Plans come
down a separate path that needs no transaction, because the `EXPLAIN` is
plan-only and the statement is never executed. That is the integrity promise on
this page's list, not the availability one. Like any reader it takes an
`AccessShareLock` while it reads. It surfaces and explains; it does not write to
your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [How to set up a read-only Postgres role for an AI agent](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
- [Is it safe to let an AI agent access my production database?](https://www.dbgorilla.com/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)
- [How to audit which SQL an AI agent ran](https://www.dbgorilla.com/learn/postgres/how-to-audit-which-sql-an-ai-agent-ran/)