A read-only Postgres role is the whole MCP security model

Ask what stops a tool-calling agent writing to production and you’ll get an answer about the prompt, or about a tool-definition field called readOnlyHint. Neither is a control.

A SELECT-only role bounds privilege, not work. The GRANT is the boundary — everyone writing about this agrees, and almost everyone stops there. It stops a statement that would change data. It does not stop a sequential scan that takes the database down with it, and it stops nothing at all once rows reach the model’s output.

The Model Context Protocol specification at revision 2026-07-28 is candid about its reach. Under Implementation Guidelines: “While MCP itself cannot enforce these security principles at the protocol level, implementors SHOULD:”, then five numbered obligations, every one somebody else’s build.

Lamplighter carries the arithmetic. It’s invented for this library and modelled on nobody: a retrieval layer over a contracts archive, one tool called execute_sql, and a PostgreSQL 18 metadata database whose clause table runs to 240 GiB — a row per clause per contract version, decades deep. Eighteen analysts reach it at peak, all through the agent. Every number here was chosen.

What a read-only Postgres role actually stops

A read-only role stops exactly one class of failure: a statement that would change data. Real, and narrower than its name. Section 5.8 of the PostgreSQL 18 documentation defines SELECT as a privilege that “Allows SELECT from any column, or specific column(s), of a table, view … or other table-like object. Also allows use of COPY TO.” Hold that second sentence.

Three privileges arrive whether or not you asked. The same section says nothing goes to PUBLIC by default on tables, columns or sequences, then lists what does: “CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures”. A role with no grants can therefore connect, make temporary tables, and run any function in a schema it reaches.

Revoke TEMPORARY by name. Nobody does. An agent that can make a temporary table could spill a slice of the clause table into one, and that’s a write: not in any audit, not outliving the session, but disk nobody budgeted for.

Does readOnlyHint stop an MCP tool writing to the database?

No. readOnlyHint is a hint, and the schema says so in the doc comment on the interface, not on the field, which is part of why it gets missed. ToolAnnotations sits at line 1912 of schema/2026-07-28/schema.ts, sixty per cent of the way down a file of 3,197 lines:

NOTE: all properties in ToolAnnotations are hints. They are not guaranteed to provide a faithful description of tool behavior (including descriptive properties like title).

The defaults underneath are worse. readOnlyHint is Default: false and destructiveHint Default: true, and the second counts only when the first is false, so an unannotated tool is a destructive one.

The specification is blunter. Under Security and Trust & Safety, its third key principle, Tool Safety: “Tools represent arbitrary code execution and must be treated with appropriate caution.” The nested sub-bullet goes further: “In particular, descriptions of tool behavior such as annotations should be considered untrusted, unless obtained from a trusted server.”

A label does not stop a statement.

Use GRANT SELECT on named tables, not pg_read_all_data

Name the tables in the GRANT and leave pg_read_all_data alone. The honest case is strong: one line, it covers tables, views and sequences, it carries USAGE on every schema, and section 21.5 of the PostgreSQL 18 documentation says it “does not bypass row-level security (RLS) policies”. It keeps working when somebody adds a table.

Covering the table somebody adds next month is why I wouldn’t grant it to an agent. The role reads that table without anyone deciding it should, and the RLS reassurance protects nothing on a table with no policy, which is most tables. One case would change my mind: an analyst role where every table carries a policy and somebody has checked that they all do.

The failure a read-only role does not stop: a sequential scan that never ends

A SELECT-only role will run a sequential scan over Lamplighter’s 240 GiB clause table for as long as it takes, and the GRANT has no opinion. Take a chosen read rate of 350 MB/s. 240 GiB × 1,024 = 245,760 MiB. 245,760 ÷ 350 = 702.2 seconds. 702.2 ÷ 60 = 11.7 minutes for one unindexed scan, with seventeen other analysts queued behind it.

Three things go wrong while that scan runs. Only one is slow queries. The SELECT holds an ACCESS SHARE lock, which section 13.3.1 says “Conflicts with the ACCESS EXCLUSIVE lock mode only”, and ACCESS EXCLUSIVE is what DROP TABLE, TRUNCATE, REINDEX and VACUUM FULL take, so your migration queues behind the agent’s question. Second, the long transaction pins the xmin horizon and vacuum stops reclaiming rows; look in pg_stat_activity for a large age(backend_xmin), which section 24.1.5 prescribes under a wraparound heading, not a bloat one. The read-only role causes bloat it cannot itself write. Third, the sort or the hash spills, and temp_file_limit ships at -1, which section 19.4.2 calls no limit.

temp_file_limit is the parameter the agent can’t turn off: the same entry says “Only superusers and users with the appropriate SET privilege can change this setting.” Derive it. work_mem defaults to 4MB and hash_mem_multiplier to 2.0, so a hash node gets 4 × 2 = 8 MB and the heaviest plan I’d accept holds three: 3 × 8 = 24 MB per process. Allow a spill of twenty times that: 24 × 20 = 480 MB. A 10-second query at 350 MB/s writes 10 × 350 = 3,500 MB at most, so the limit binds 3,500 ÷ 480 = 7.3 times earlier.

Then set the clock. At 10 seconds the scan dies after 3,500 MB read, 3,500 ÷ 245,760 = 1.4 per cent of the table.

How to choose statement_timeout for an agent’s database connection

Work backwards from the wall-clock budget for one turn, then divide by the attempts you allow. For Lamplighter I’ll take a 30-second tolerance, 4 seconds for the model to write the SQL and 6 to write the answer, all chosen. 30 − 4 − 6 = 20 seconds belongs to the database. Allow two attempts, because the first query is often wrong in a way the error message repairs: 20 ÷ 2 = 10 seconds.

Derive it again from the other side. Say the slowest query you’ll accept in the catalogue is 3.8 seconds, against a chosen p50 of 0.12 and p95 of 1.6, times a headroom factor of 2.5 for a cold cache: 3.8 × 2.5 = 9.5, the same 10 seconds. Two routes, one number, and the agreement is why I trust it. That catalogue is your evaluation set, so build the evaluation set first.

Now derate the riskiest estimate. Suppose the slowest accepted query is three times worse than I think: 3.8 × 3 = 11.4, and 11.4 × 2.5 = 28.5 against a database share of 20. The decision flips. No timeout saves that turn, and the repair is an index or a materialised view. Halve the tolerance and it runs the other way: 15 − 4 − 6 = 5, then 5 ÷ 2 = 2.5 seconds, which covers the chosen p95 of 1.6 and deletes the 3.8-second query. Under about 2.5 seconds is honest only if you’ve already replaced it with an index.

Why ALTER ROLE SET statement_timeout is a default and not a ceiling

Because statement_timeout has context user in pg_settings, and the context list in section 53.25 of the PostgreSQL 18 documentation — below the full column table, where nobody scrolls — defines user as settings where “Any user is allowed to change their session-local value.” The session can raise its own timeout to anything, zero included, which section 19.11.1 gives as the shipped value: “A value of zero (the default) disables the timeout.”

ALTER ROLE is not ambiguous either, just read optimistically. Its Description section says the value “becomes the session default”. Default, not ceiling. And no grant repairs that: section 5.8 calls the SET privilege “meaningless except for parameters that would normally require superuser privilege to set”. In the source, as of PostgreSQL 18.6, it is declared PGC_USERSET at line 2740 of guc_tables.c, a file of 5,457 lines, while temp_file_limit is PGC_SUSET at line 2633. Check your tag: in 18.4 both sit a line earlier.

transaction_timeout, idle_in_transaction_session_timeout and idle_session_timeout all ship at 0 as well, and the role can change all three.

Don’t move the timeout into the pooler either. PgBouncer’s configuration reference files query_timeout under “Dangerous timeouts”, a section opening “Setting the following timeouts can cause unexpected errors.”

CONNECTION LIMIT 12 is not twelve processes

Twelve connections can be twenty processes, because the CREATE ROLE documentation says “Neither prepared transactions nor background worker connections are counted towards this limit.”

Get to twelve first. Eighteen analysts at one question each per 15 seconds, all through the agent’s role, is 18 ÷ 15 = 1.2 per second, and the timeout, 10 seconds, is worst-case service time. Little’s law gives 1.2 × 10 = 12, against a shipped -1 the same page calls no limit. Now memory, at the 24 MB per process derived above: a 4 GB instance with shared_buffers at a chosen 25 per cent gives it 1 GB, the OS 1 GB and backends 2 GB = 2,048 MB, so 2,048 ÷ 24 = 85 processes.

And the obvious estimate is wrong. Charge each connection a leader plus two workers: 12 × 3 = 36 processes, 36 × 24 = 864 MB, 864 ÷ 2,048 = 42 per cent of that budget. The cluster won’t do it. max_parallel_workers defaults to 8 cluster-wide, so twelve leaders share eight: 12 + 8 = 20 processes, 20 ÷ 12 = 1.67 times what the limit implies, 20 × 24 = 480 MB, 480 ÷ 2,048 = 23 per cent. The estimate runs 42 − 23 = 19 points high.

max_parallel_workers is not even the binding parameter. Raise it to 32 for somebody’s reports and nothing moves, because section 19.4.6 says a value above max_worker_processes “will have no effect, since parallel workers are taken from the pool of worker processes established by that setting”, and max_worker_processes is 8 and “can only be set at server start”.

Push it both ways. Double the fleet to 36 analysts: 36 ÷ 15 = 2.4 per second, 2.4 × 10 = 24 connections, 24 + 8 = 32 processes, 32 × 24 = 768 MB, 768 ÷ 2,048 = 37.5 per cent, under the ceiling of 85 − 8 = 77 leaders. The received advice is a pooler in transaction mode. I wouldn’t bother. Halve the fleet. 9 ÷ 15 = 0.6, 0.6 × 10 = 6, and below roughly eight concurrent agent sessions the limit never binds and I wouldn’t set one.

An admission. The CREATE ROLE page says, below its parameter list, that the limit is “only enforced approximately” and its one example fails safe — two sessions racing for the last slot can both be refused — but whether there’s a window going the other way I don’t know, and I haven’t read the code.

Data exfiltration through the model’s output is a transport problem, not a GRANT

Nothing you write in a GRANT touches what happens after rows leave the database. The agent is allowed to read the clause table; that’s the point of it. Every control so far decides which rows it reads and for how long. None decides where the answer goes.

Remember COPY TO, folded into the SELECT privilege by section 5.8. It writes where the server can write, though well down the COPY documentation naming a file turns out to be “only allowed to database superusers or users who are granted one of the roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program”. The GRANT is about rows; exfiltration is about bytes already past it.

The Model Context Protocol specification agrees, and puts the control beyond a database administrator’s reach. On the Tools page, under Security Considerations, the last heading on a long page, clients SHOULD “Show tool inputs to the user before calling the server, to avoid malicious or accidental data exfiltration”. No GRANT expresses that.

What’s left to the transport is authentication, plus two controls outside the database: Origin checking and localhost binding. Under Security & Endpoint, a Streamable HTTP server “MUST validate the Origin header on all incoming connections to prevent DNS rebinding attacks”. That revision also removed protocol-level sessions and the GET stream: a GET to the MCP endpoint gets 405 Method Not Allowed. Anyone whose mental model of MCP includes a session is reading 2025-11-25 or earlier.

What goes in the migration that creates the agent’s role

The migration is the role, two grants, three role-scoped parameters, two revokes — one of which almost everybody forgets — and no ALTER DEFAULT PRIVILEGES.

-- Lamplighter's agent role. Ships in the same pull request as the execute_sql tool.
-- CREATE ROLE defaults to NOLOGIN; CREATE USER, its alternative spelling, does not.
CREATE ROLE lamplighter_agent LOGIN PASSWORD :'agent_pw' CONNECTION LIMIT 12;

-- CONNECT and TEMPORARY reach PUBLIC by default. Taking TEMPORARY off the agent
-- takes it off everyone, so check what else connects to this database first.
REVOKE TEMPORARY ON DATABASE contracts FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- redundant on a cluster created at 15 or later

GRANT USAGE ON SCHEMA app TO lamplighter_agent;
GRANT SELECT ON app.clause, app.contract, app.counterparty TO lamplighter_agent;

-- The first two are defaults the session can raise. The third is a ceiling it cannot.
ALTER ROLE lamplighter_agent SET statement_timeout = '10s';
ALTER ROLE lamplighter_agent SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE lamplighter_agent SET temp_file_limit = '480MB';

-- Deliberately absent: ALTER DEFAULT PRIVILEGES.

The absent line is the one worth arguing about. With no ALTER DEFAULT PRIVILEGES, a table created in app next month stays invisible until somebody grants it deliberately. That’s the whole difference between this and one line of pg_read_all_data, and it’ll irritate you in six weeks, which is right.

One line needs a check first. On a cluster created at 15 or later the REVOKE on public is redundant: the 15.0 release notes say under Migration to Version 15 that the change was to “Remove PUBLIC creation permission on the public schema”, and that “Upgrading a cluster or restoring a database dump will preserve public’s existing permissions.” On one upgraded from 14 a SELECT-only role can still CREATE TABLE there; section 5.10.6 gives the repair. Revoking ALL takes USAGE off every other role too.

We ship the migration in the same pull request as the tool definition: a tool that runs SQL and a role that decides which SQL are one change, not two. And we won’t start an applied AI engagement where the agent points at the primary with the application’s credentials. That credential can write, and no prompt stops it. Read why most of what gets sold as an agent should be a workflow first.

Run this against whatever you believe is locked down.

SELECT name, context, boot_val
FROM pg_settings
WHERE name IN ('statement_timeout', 'temp_file_limit', 'default_transaction_read_only')
ORDER BY name;

Three rows, alphabetical. The context column reads user, user, superuser. default_transaction_read_only is in there because it sounds like a control and is not: PGC_USERSET, default off, and the session sets it straight back. The one the agent can’t change is the one hardly anybody sets.