How to give Claude Code read-only access to a production database

Run this as a role that owns the schema, or as a superuser. Replace app with your database name and pick a real password. Every statement here ran cleanly on PostgreSQL 17.11.

CREATE ROLE agent_reader LOGIN PASSWORD 'replace-me' CONNECTION LIMIT 5;

-- Only roles you name can reach this database at all.
REVOKE ALL ON DATABASE app FROM PUBLIC;
GRANT CONNECT ON DATABASE app TO agent_reader;

GRANT USAGE ON SCHEMA public TO agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_reader;

-- Tables created after today are readable too, so the agent does not silently
-- lose its view of the schema every time you ship a migration. Drop this line
-- if you would rather grant each new table by hand.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_reader;

-- Guards, not boundaries. See "what this does not cover" below.
ALTER ROLE agent_reader SET default_transaction_read_only = on;
ALTER ROLE agent_reader SET statement_timeout = '30s';
ALTER ROLE agent_reader SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE agent_reader SET lock_timeout = '5s';

CONNECTION LIMIT 5 matters more than it looks. An agent in a retry loop will happily open connections until your database runs out of them, and a connection cap is the cheapest way to make sure a misbehaving agent degrades itself rather than your application. Pick a number slightly above what the agent needs.

lock_timeout is the one people leave out. A long SELECT does not block writers in Postgres, but a query that waits on a lock still holds a slot and can pile up behind a migration. Five seconds is a reasonable default for an agent that is exploring rather than serving traffic.

Postgres 14 added a predefined role that covers the schema walking in one statement. It reads every table in every schema, including tables that do not exist yet.

CREATE ROLE agent_reader2 LOGIN PASSWORD 'replace-me';
GRANT CONNECT ON DATABASE app TO agent_reader2;
GRANT pg_read_all_data TO agent_reader2;

Verified on 17.11: a role holding pg_read_all_data reads a table created after the grant, reads columns you never thought about, and still gets permission denied for table brand_new on an INSERT. So it is genuinely read-only, and it is genuinely everything.

That last part is the tradeoff. pg_read_all_data is the right call when you want the agent to explore an entire database and you are relaxed about what is in it. It is the wrong call when there is a users table with an ssn column in it, because the grant does not distinguish. If you are going to narrow anything later, start from the explicit grants in the previous section instead.

Claude Code reaches a database in one of two ways: an MCP server it launches or connects to, or ordinary tooling in your repo (psql, an ORM, a script) reading a DATABASE_URL. The role above works for both, because the enforcement is in the database and not in the client.

For MCP, crystaldba/postgres-mcp in restricted mode is a good, maintained choice for a local or read-only setup. It parses SQL against a Postgres grammar and allows only read-shaped statements, which is a stronger design than wrapping a string in a read-only transaction.

{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": ["postgres-mcp", "--access-mode=restricted"],
      "env": {
        "DATABASE_URI": "postgresql://agent_reader:REPLACE_ME@db.internal:5432/app"
      }
    }
  }
}

Keep the DSN out of the config file if you can. .mcp.json gets committed, synced to other machines, and copied into issue comments more often than anyone plans. Reference an environment variable, or use whatever secret handling your MCP client supports, and treat any DSN that has been in a config file as already leaked.

This is the step that separates a rule that is enforced from a rule that is written down, and it takes about thirty seconds. Connect as the new role and run the following. The output below is what a real 17.11 instance returned.

SELECT current_setting('default_transaction_read_only') AS drt,
       current_setting('statement_timeout')            AS st;

INSERT INTO public.users VALUES (3, 'c@example.com', '333', 'Cy');

SET default_transaction_read_only = off;
INSERT INTO public.users VALUES (3, 'c@example.com', '333', 'Cy');

SET statement_timeout = 0;
SELECT current_setting('statement_timeout') AS st_after;

 drt | st
-----+-----
 on  | 30s
(1 row)

ERROR:  cannot execute INSERT in a read-only transaction
SET
ERROR:  permission denied for table users
SET
 st_after
----------
 0
(1 row)

Read those two errors carefully, because they are not the same error and the difference is the whole point of this page.

• cannot execute INSERT in a read-only transaction is the transaction setting talking. The agent turned it off with one SET and the setting went away.
• permission denied for table users is the privilege system talking. There is no SET that clears it. That is your boundary.
• SET statement_timeout = 0 succeeded, and the timeout you set on the role is now gone for that session. So the timeout is a guard against accidents, not against a determined or confused agent.

You can check this yourself rather than taking it from us. Every one of those settings reports its own reachability in the catalog:

SELECT name, context
  FROM pg_settings
 WHERE name IN ('default_transaction_read_only',
                'statement_timeout',
                'idle_in_transaction_session_timeout',
                'application_name',
                'log_statement');

Four gaps, in the order people hit them. None of these is a criticism of Postgres. They are all consequences of the privilege system being designed to answer one question, which is whether a role may touch an object, and not the other questions an agent raises.

It does not redact a column

A GRANT is binary. You can grant SELECT on a subset of columns, which is a real and underused feature, but the result is that a query naming the excluded column fails rather than returning something safe. Verified on 17.11: after GRANT SELECT (id, name) ON public.users TO agent_reader, a SELECT id, name works and SELECT * returns permission denied for table users.

For an agent that is a bigger problem than it sounds. Agents write SELECT * constantly, and a hard failure gives the model nothing to work with, so it retries, guesses column lists, and burns turns. What you usually want is the row back with the sensitive value replaced, which the privilege system has no way to express. That question has its own page: how to mask PII before it reaches an LLM.

It does not cap a runaway scan

A read-only role can still run SELECT * FROM events against a table with two hundred million rows, and it can still write an accidental cross join. The two mechanisms Postgres gives you here are statement_timeout, which the agent can reset as shown above, and CONNECTION LIMIT, which bounds concurrency rather than any single query. Neither caps the number of rows a statement returns, and rows are what actually hurts: they are what fills the agent's context window, what leaves your network, and what your provider bills you for.

It does not tell you which agent ran what

The database knows the role. If you point three agents at agent_reader, every statement in the log says agent_reader and nothing distinguishes them. You can push identity into application_name, but as the table above shows its context is user, so the agent sets it and the agent can change it mid-session. Getting a per-agent trail out of stock Postgres means one role per agent, which multiplies the grant management you just did. How to audit what an agent ran covers what the log-based options do and do not give you.

It cannot be revoked for one consumer

A role is a shared secret. When you want to cut off one agent, your options are to rotate the password, which cuts off everything using that role, or to DROP the role, which is worse. There is no revoke-just-this-one. In practice this is the gap that shows up during an incident, which is the worst possible time to find it.

Before adding anything to your stack, there are three more things the database can do on its own. Each is real, and each carries a cost worth knowing in advance.

At this point you have a decision rather than a task. If the database is a staging copy, a local database, or a production database whose worst case you are comfortable with, stop here. You have a real boundary that costs nothing and you did not add a dependency. If the answer is that you need the agent to read a table that has personal data in it, or you need to know which agent ran what, or you need to stop one agent at 2am, the privilege system is not going to get you there and you need something in the connection path.

PgBeam sits in the PostgreSQL wire protocol between the agent and your database, on a globally distributed proxy in the region nearest your database. It parses every statement into a real Postgres parse tree before forwarding it and checks every statement in a batch, so a stacked COMMIT; CREATE TABLE ... on a read-only credential is refused with SQLSTATE 42501 and a message beginning blocked by PgBeam agent policy:. Unparseable SQL, unknown statement types, and COPY fail closed rather than passing through.

The reason that matters here is not that it blocks writes. Your GRANTs already do that, and they do it well. It is that a layer sitting between the statement and the result set can answer the other three questions, which the privilege system structurally cannot.

The same policy applies to both front doors. A credential comes with a scoped Postgres connection string and a hosted MCP endpoint, so an agent using MCP tools and a script using psql are held to the same rules. That is the part an MCP server cannot cover: the moment someone points an ORM at the database directly, a control that lives in the MCP layer is not in the path.

pgbeam policies create --name agent-read-only --mode read_only \
  --max-rows 1000 \
  --budget-queries-per-day 5000 \
  --statement-timeout-ms 10000

pgbeam agents create --name claude-code --policy pol_1a2b3c

{
  "mcpServers": {
    "pgbeam": {
      "type": "http",
      "url": "https://<project>.proxy.pgbeam.app/mcp",
      "headers": { "Authorization": "Bearer pba_..." }
    }
  }
}

Enforcement is at the wire, so the database can be on RDS, Aurora, self-hosted, or any managed provider, and nothing about your application changes. If you would rather see the comparison directly, PgBeam versus a read-only role is the side-by-side.

These are demoable, so you would find them within an afternoon anyway.

• **Relation-level allowlists do not see through views.** A view over an allowlisted table is a separate relation and has to be allowlisted itself.
• **SET search_path is blocked for agent credentials.** Leaving it open would let an allowlist be evaded by resolving the same bare name in another schema. If your agent workflow sets a search path, it needs changing.
• **Binary-format result columns are masked to NULL.** Text-format columns get a redaction token or a deterministic hash; a binary column cannot carry either, so it comes back empty rather than fake.
• **A masked column may be selected, not filtered on.** Using one in WHERE, JOIN ... ON, GROUP BY, ORDER BY, or DISTINCT is refused, because those positions let an agent recover the value one comparison at a time. Queries written that way have to be rewritten.
• **Query budgets are per-region approximations, not globally coordinated.** Row caps and statement timeouts are exact; a windowed query count can drift at the margins.
• **No SOC 2 Type II.** The audit log is hash-chained and tamper-evident and the retention is real. The certification is on the roadmap and is not claimed.

• **A local or throwaway database.** You own the blast radius. Do the role, skip everything after it.
• **A read replica with no personal data on it.** If the worst case is a slow query on a replica nobody serves from, a role and a statement_timeout are proportionate.
• **One agent, one operator, one database.** The per-agent attribution and revocation arguments are about having several agents. With one, a role and a rotation are fine.
• **You want to own the code.** Self-hosting an MCP server with your own checks is legitimate. The tradeoff is that you also own the policy, the masking, the audit pipeline, and keeping all three current as the schema moves.

Can I just tell Claude not to write?

It reduces accidents and it is worth doing in your CLAUDE.md. It is not a control. The instruction and the untrusted content the model just read (an issue comment, a support ticket, a row in a table) arrive through the same channel, and the model is not the enforcement layer. Put the limit somewhere the model's input cannot reach.

Is a read replica enough on its own?

A replica removes the write risk, which is real progress. It does not remove the read risk, and read risk is most of what an agent presents: the data is identical, so every column of personal data on the primary is on the replica too. A replica plus a read-only role plus masking is a good posture. A replica alone is a partial one.

What about giving it a database dump instead?

A sanitised dump on a local database is genuinely the safest option and it is underrated for exploratory work. It stops being an option the moment the task needs current data, and it introduces its own leak, because a dump is a copy of production sitting on a laptop.

Does any of this change my application code?

No. A role is a role, and a connection string is a connection string. Every Postgres driver, ORM, and agent framework works unchanged, because the protocol on both sides of the change is the same protocol.