The one line everyone reaches for when handing a model a database:

GRANT SELECT ON ALL TABLES IN SCHEMA app TO mcp_reader;

We built that role on PostgreSQL 17.11 and ran 15 probes against it. Five got through. Then we applied hardening one control at a time to find out which change closes which hole — and two of the five were closed by none of them.

What got through

Against a role with nothing but CONNECT, USAGE and GRANT SELECT:

Probe	GRANT SELECT only	Closed by
`CREATE TEMP TABLE`	allowed	`REVOKE TEMPORARY … FROM PUBLIC`
`EXECUTE` a `SECURITY DEFINER` function that writes	allowed	`REVOKE EXECUTE` on the function
Hold a 3-second query	allowed	`statement_timeout`
Enumerate every table name via `pg_catalog`	allowed	nothing
Read column names of a table it cannot `SELECT`	allowed	nothing

The third column is the part that took a rerun to get right — see the method below.

The finding worth the whole exercise

Two probes stayed open through every control we applied.

A role restricted to SELECT on three tables in one schema can still read pg_catalog and information_schema. In our run it counted every table in the database, including the private schema whose data it was refused. It then listed the column names of those same unreadable tables.

-- refused
SELECT count(*) FROM private.salaries;
ERROR:  permission denied for schema private

-- allowed, same role, same session
SELECT count(*) FROM information_schema.columns WHERE table_schema = 'private';

So the boundary you actually get is data, not structure. A model with this role cannot read the salaries. It can learn that private.salaries exists, and what its columns are called.

Whether that matters depends on your schema. If your column names are amount and employee_id, probably not much. If they are acquisition_target_valuation or layoff_round_2_headcount, the names are the leak.

None of the standard hardening advice closes this, because catalog visibility is not a table privilege. Closing it means not exposing the objects at all — a separate database, or a schema the role cannot see.

What the write-once controls actually closed

Applied cumulatively, each in its own stage:

Control	Newly closed	Refusal
`REVOKE TEMPORARY … FROM PUBLIC`	`CREATE TEMP TABLE`	`permission denied to create temporary tables in database "probe"`
`REVOKE EXECUTE ON FUNCTION … FROM PUBLIC`	the writing `SECURITY DEFINER` call	`permission denied for function bump_counter`
`statement_timeout = '1s'`	the 3-second query	`canceling statement due to statement timeout`
`default_transaction_read_only = on`	nothing new	—
`ALTER DEFAULT PRIVILEGES`	opened future tables, as intended	—

default_transaction_read_only closing nothing is not a failure of the setting. By the time it was applied, every write path the probes tried was already blocked by privileges. It is a seatbelt behind a locked door — worth having, and not the thing holding the door.

The SECURITY DEFINER function is the real escape hatch

Of the five, this is the one that actually wrote to the database:

CREATE FUNCTION app.bump_counter() RETURNS int
  LANGUAGE sql SECURITY DEFINER AS
  $$ UPDATE app.counters SET n = n + 1 RETURNING n; $$;

EXECUTE on a new function is granted to PUBLIC by default, and a SECURITY DEFINER function runs with its owner’s privileges. So a role with no UPDATE anywhere performed an UPDATE, through a function it was never explicitly granted.

If your application has helper functions like this — and most do — a SELECT-only grant does not stop a model calling them.

What PostgreSQL 17 blocks for you

Worth stating, because a lot of hardening advice is older than the defaults:

CREATE TABLE in public — blocked. PostgreSQL 15 removed the default CREATE grant to PUBLIC. On 14 and earlier, and on databases upgraded from them, this is still open and still needs revoking.
COPY TO PROGRAM — blocked (permission denied to COPY to or from an external program).
pg_read_file() — blocked (permission denied for function pg_read_file).
INSERT / UPDATE / DELETE on the granted table — blocked, as expected.
A table created after the GRANT — blocked. And ALTER DEFAULT PRIVILEGES does not fix it retroactively: in our run the table created before the change stayed refused, while one created after it was readable.

Method

A single before/after comparison cannot say which change closed which hole, and the first version of this harness proved it: default_transaction_read_only masked the TEMPORARY revoke, so the revoke appeared to work when it had never been tested. The published version applies each control in sequence against the same role and credits a stage only with what it newly closed.

The harness creates its own PostgreSQL cluster in a temp directory on a free port, runs everything inside it, and deletes it — it never connects to a cluster you already run.

node scripts/probe-postgres-readonly.mjs
node scripts/probe-postgres-readonly.mjs --json

Every probe outcome and refusal message is in the raw results. The harness is scripts/probe-postgres-readonly.mjs in the Benchivo repo, so you can add a probe and rerun it against your own version.

What this does not cover

One cluster, one version, default configuration, a schema we invented. It does not test row-level security, extensions, foreign data wrappers, or any particular MCP server’s own behaviour — only what the database account permits. A different Postgres version will move some of these rows, which is exactly why the version is on the page.

What this means

Treat GRANT SELECT as the start of the configuration, not the end of it. It blocks the writes you were worried about and leaves temp tables, function execution and unbounded queries open.

Audit SECURITY DEFINER functions before connecting a model. It is the only probe here that achieved a real write, and it needed no special privilege to call.

Assume your schema is readable even when your data is not. If the structure itself is sensitive, privileges are the wrong tool — use a separate database or a schema the role cannot see.

FAQ

Was this measured or explained? Measured. The numbers come from the harness above, run against PostgreSQL 17.11, and the raw output is published.

Does this mean read-only Postgres roles are unsafe? No. It means GRANT SELECT alone is not the whole boundary. With the temp-table, function and timeout controls applied, everything except catalog visibility was closed.

Why does catalog access matter? Because column and table names are often descriptive enough to be the sensitive part, and no privilege setting here removed that visibility.

How do I set one up properly? The configuration this article probes is written up step by step in Set up a read-only PostgreSQL MCP server.

Where this sits

A measured page: the harness is in the repo and the raw results are published, so you can disagree with the conclusion by rerunning it. How Benchivo separates measured pages from explanations is in the methodology.