Set up a read-only PostgreSQL MCP server

Giving a model query access to a real database should not mean trusting it not to write. The account behind the MCP server should be structurally unable to change your data — and you should finish by proving that, not by believing the word “read-only” in a config file.

The short version

A safe setup is a dedicated login role with no ownership, no administrative attributes, no membership in application roles, USAGE on one schema, SELECT on named tables, the PUBLIC defaults revoked, timeouts attached to the role, and a final set of statements that fail on purpose.

This page is an explanation, not a benchmark. Nothing here was measured — the numbers below are examples, and the last section is a set of checks you run against your own database.

Why GRANT SELECT is not enough

This looks like the whole job and is not:

GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO mcp_reader;

PostgreSQL computes a role’s effective privileges from direct grants, membership in other roles, and privileges handed to PUBLIC. Revoking something from the role does nothing if the same privilege arrives by another route.

Treat the MCP login as a security boundary, not a username with one convenient grant.

1. Create a dedicated login role

Run the setup as the database owner or another suitably privileged administrator.

CREATE ROLE mcp_reader
  LOGIN
  PASSWORD 'use-a-long-random-secret-from-your-secret-manager'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 5;

Most of those restrictions are already the default for an ordinary role. Spelling them out makes the intended security properties visible to whoever reviews this later, which is the point.

Two rules that matter more than the flags:

• Do not make mcp_reader an owner of the database, schemas, tables, views or functions it reads.
• Do not grant it membership in app_user, developer or any similar role. Effective privileges include everything inherited.

2. Revoke what the defaults hand out

The public schema

Behaviour here depends on your version, and this is the detail most guides get wrong:

• PostgreSQL 15 and newer — CREATE on the public schema is no longer granted to PUBLIC. The schema is owned by pg_database_owner and the default is already the safe one.
• PostgreSQL 14 and earlier — every role can create objects in public until you revoke it. Databases upgraded from those versions keep the old, permissive ACL, so a modern server can still be exposed.

If you are on 14 or came from it:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

This changes privileges for every user of that database, not just the MCP role. Check what depends on it before running it on an established system.

Temporary tables

TEMPORARY on a database is granted to PUBLIC by default, and you cannot revoke it from one role while PUBLIC still has it. You have to change the database-wide policy and grant it back:

REVOKE TEMPORARY ON DATABASE production FROM PUBLIC;
GRANT TEMPORARY ON DATABASE production TO application_role;

Again: existing software may create temp tables. Do not apply this blind.

3. Grant back exactly what is needed

GRANT CONNECT ON DATABASE production TO mcp_reader;
GRANT USAGE ON SCHEMA reporting TO mcp_reader;

USAGE only lets the role resolve names inside the schema; tables still need their own privileges. Do not grant CREATE on the schema.

Then name the tables rather than sweeping the schema:

GRANT SELECT ON TABLE
  reporting.customers,
  reporting.orders,
  reporting.products
TO mcp_reader;

ON ALL TABLES IN SCHEMA is the convenient version and the one that quietly hands over the table you forgot about. For sensitive databases, expose purpose-built views instead of base tables:

GRANT SELECT ON reporting.customer_summary TO mcp_reader;

The role should never receive INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES or TRIGGER. PostgreSQL defines each of those separately from SELECT.

4. Cover the tables that do not exist yet

GRANT SELECT ON ALL TABLES applies to tables that exist at the moment you run it. Tomorrow’s migration creates a table your role cannot see — or, worse, one your role can see because someone fixed the symptom with a broader grant.

Default privileges are attached to the role that creates the objects:

ALTER DEFAULT PRIVILEGES
  FOR ROLE app_owner
  IN SCHEMA reporting
  GRANT SELECT ON TABLES TO mcp_reader;

If several roles create objects, configure this for each one. And note it does nothing to tables that already exist — those still need the explicit grant above.

5. Treat callable functions as part of the boundary

A SELECT can call a function, and a function can write:

SELECT some_function();

EXECUTE on newly created functions is granted to PUBLIC by default. A SECURITY DEFINER function runs with its owner’s privileges, which is exactly the escape hatch a carefully scoped reader role was meant to close.

Audit the functions your MCP role can resolve, and revoke EXECUTE on anything with side effects.

6. Make read-only structural, and add the timeouts

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

Those durations are examples, not recommendations — pick values that match your workload.

What each one does:

• default_transaction_read_only makes new transactions read-only by default. It prevents changes to non-temporary tables, but it is a transaction setting, not an ACL. It is a seatbelt, not the boundary.
• statement_timeout aborts a statement that runs too long. A read-only role can still take a database down with one query.
• idle_in_transaction_session_timeout kills a connection idling inside an open transaction, which is what stops a forgotten transaction holding locks and old snapshots.

7. When SELECT is still too broad

If the model may read a table but only some of its rows — one tenant, one region — that is row-level security, not privileges:

ALTER TABLE reporting.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY mcp_reader_tenant ON reporting.orders
  FOR SELECT TO mcp_reader
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Remember the bypass list from the first table: the table owner is normally exempt from its own policies, which is one more reason mcp_reader must not own anything.

8. Point the MCP server at the role

The connection string carries a password, so it belongs in whatever secret mechanism your MCP host provides — not in a file you commit.

Beyond that, the configuration is implementation-specific. PostgreSQL MCP servers differ in executable name, argument format and environment variable names, and this guide will not invent a stanza that looks authoritative and is wrong. Use the documentation for the server you actually deploy, and make the chain end up as:

MCP client → PostgreSQL MCP server → connection as mcp_reader → database production

The database restrictions above still matter even when a server advertises that it only exposes query tools. That claim is a feature of the software; the grants are a property of the database.

9. Prove it

This is the part that separates a configured setup from a safe one. Run these as mcp_reader.

Check the privileges the role actually holds:

SELECT has_table_privilege('mcp_reader', 'reporting.customers', 'SELECT') AS can_read,
       has_table_privilege('mcp_reader', 'reporting.customers', 'INSERT') AS can_insert,
       has_table_privilege('mcp_reader', 'reporting.customers', 'UPDATE') AS can_update,
       has_table_privilege('mcp_reader', 'reporting.customers', 'DELETE') AS can_delete;

You want true, false, false, false.

Then attempt a write inside a transaction, so that an unexpected success can still be rolled back:

BEGIN;
CREATE TABLE public.__mcp_readonly_probe (id integer);
ROLLBACK;

The CREATE TABLE should fail. If it succeeds, roll back immediately and fix the privileges before any model touches this connection.

Confirm the read path still works:

SELECT * FROM reporting.customers LIMIT 1;

A correct deployment needs both: intended reads succeed, attempted writes fail.

What this still does not protect against

Read-only does not mean harmless.

The model can run expensive queries, read every row its grants expose, hold locks while querying, and carry retrieved data somewhere else in its workflow. Timeouts and connection limits reduce that risk; they do not remove it.

Database privileges also cannot compensate for a compromised MCP server, leaked credentials, an over-permissive RLS policy, or data that should never have been readable by this role in the first place.

Treat the role as one layer: narrow the data, protect the secret, restrict network access where you can, audit privileged functions, log what the account does, and re-run the checks in section 9 after every migration.

FAQ

Is default_transaction_read_only enough on its own? No. It is a transaction default, not a privilege boundary, and it can be overridden within a session. Use it alongside the grants, never instead of them.

Do I need to revoke CREATE ON SCHEMA public on PostgreSQL 16? Not on a database created there — 15 and newer already ship the safe default. You do if the database was created on 14 or earlier and upgraded, because the old ACL comes across with it.

Why name tables instead of using ALL TABLES IN SCHEMA? Because ALL means “all the ones that exist right now”, and because the table you forgot about is exactly the one you did not want in an answer.

Does this guide’s setup need testing? Yes — that is section 9, and it is the only part that tells you the truth about your own database. Benchivo did not run these statements for you; nothing on this page is a measurement.

Where this sits

This is a reference page: an explanation, with nothing measured. What Benchivo has actually run and scored is in the tests, and the rule that keeps the two apart is in the methodology. The transport-level measurements for MCP are in stdio vs HTTP latency.