How to give Claude read-only Postgres access safely

Letting Claude query your Postgres database is one of the most useful things you can do with MCP. It is also easy to do badly. The common shortcut is to paste a connection string into an MCP server and tell the model, in the prompt, to “only run SELECT”. That is a request, not a control.

This guide shows how to build real limits in layers: a least-privilege database role, a query-plan allowlist that checks every statement before it runs, and a clear-eyed list of what those two layers do not protect against. The setup at the end uses Automata, a free, open-source PostgreSQL MCP server, but the role and the threat model apply to any server you use.

What you need

• A PostgreSQL database you can create roles in. A read replica or a staging copy is better than your primary.
• Claude.ai, Claude Desktop, or Claude Code, on a plan that supports custom connectors or MCP servers.
• A PostgreSQL MCP server. The examples use Automata, either hosted at mcp.getautomata.app or self-hosted.
• About twenty minutes.

Why a prompt is not a permission

Claude writes SQL well, and that is the problem as well as the point. A model can write a bad query by accident (a join with no condition that scans a billion rows) or because something it read told it to. Text inside your own tables, such as a support ticket, a product review, or a free-form notes field, can contain instructions. The model may follow them. This is prompt injection, and no system prompt reliably prevents it.

So treat the model like any other untrusted client of your database. You would not give a web form a superuser connection and ask it nicely to behave. The controls that count are the ones that still hold when the model is wrong, confused, or manipulated:

1. What the database role is allowed to do.
2. What the MCP server will let through before the statement reaches the database.
3. What the data itself exposes, because anything readable can end up in a prompt.

The three layers

The rest of this guide covers the middle two in depth, because they are the ones people skip or misunderstand.

Layer 1: The database role

The role is the real security boundary. Everything the MCP server checks is a second line of defense; if the role can drop a table, the right bug or a leaked connection string will eventually drop it.

Create a role that can only read what it needs

-- A dedicated login with no special attributes
CREATE ROLE claude_reader LOGIN PASSWORD '<strong-password>'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
  CONNECTION LIMIT 5;

-- Reach the database and one schema, nothing else
GRANT CONNECT ON DATABASE app TO claude_reader;
GRANT USAGE ON SCHEMA public TO claude_reader;

-- Grant tables explicitly, and columns where a table holds sensitive fields
GRANT SELECT ON public.orders, public.products TO claude_reader;
GRANT SELECT (id, plan, country, created_at) ON public.customers TO claude_reader;

-- Sensible defaults for every session this role opens
ALTER ROLE claude_reader SET default_transaction_read_only = on;
ALTER ROLE claude_reader SET statement_timeout = '10s';
ALTER ROLE claude_reader SET lock_timeout = '2s';
ALTER ROLE claude_reader SET idle_in_transaction_session_timeout = '15s';

A few details matter here:

• Name tables, do not grant schemas. Avoid GRANT SELECT ON ALL TABLES IN SCHEMA and avoid ALTER DEFAULT PRIVILEGES for this role. A table someone adds next month should be invisible to Claude until you decide otherwise.
• Skip the built-in shortcut roles. On PostgreSQL 14 and later, membership in pg_read_all_data is convenient and far too broad. Never add the role to pg_read_server_files, pg_write_server_files, or pg_execute_server_program.
• Use column grants for sensitive tables. In the example, Claude can read customers.plan but not customers.email. A query that selects a column the role lacks fails inside Postgres, regardless of what any server checks.
• Lock down the public schema on older versions. Before PostgreSQL 15, every role can create objects in public. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC;.
• Mind row-level security. If you use it, make sure the role is subject to it (NOBYPASSRLS, and not the table owner unless the table uses FORCE ROW LEVEL SECURITY).

Then check what you actually created, rather than what you meant to create:

SELECT rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolbypassrls
FROM pg_roles WHERE rolname = 'claude_reader';

SELECT has_table_privilege('claude_reader', 'public.orders', 'INSERT');      -- false
SELECT has_column_privilege('claude_reader', 'public.customers', 'email', 'SELECT'); -- false

Prefer a read replica

default_transaction_read_only = on is a default, not a lock. A session can change it with SET or BEGIN READ WRITE, and the same is true of statement_timeout. These settings are good guardrails against mistakes, but a determined caller can override them, which is why a statement filter (layer 2) should refuse built-ins like set_config.

A hot standby is different. A replica rejects writes at the server level, whatever the role or session says. If your provider offers a read replica (most managed Postgres services do), point Claude at it. You get write protection that no SQL can talk its way around, and heavy analytical queries stay off your primary.

Plan for expensive queries

A read-only role can still hurt you. SELECT has no write side effects, but a cross join, a missing index, or a generate_series(1, 1000000000) can saturate a CPU or fill temp space. Cover it at the role level:

• statement_timeout and lock_timeout, as above.
• CONNECTION LIMIT so a runaway client cannot exhaust connections.
• Superuser-set limits such as temp_file_limit and a modest work_mem on the role.

Layer 2: Query-plan allowlists

Roles control what is possible. A query-plan allowlist controls what this specific statement is about to do, and it catches mistakes before they reach the database.

Why plans beat parsing SQL

The obvious way to restrict tables is to scan the SQL text for table names. It is also easy to defeat: quoted identifiers, mixed case, schema-qualified names, aliases, CTEs, search_path tricks, and views all change what a name means. The one component that knows exactly what a statement will read is the Postgres planner itself.

EXPLAIN (without ANALYZE) asks the planner for its plan without executing the statement. In JSON form it is easy to walk:

EXPLAIN (FORMAT JSON, VERBOSE)
SELECT c.plan, count(*)
FROM customers c
JOIN invoices i ON i.customer_id = c.id
GROUP BY c.plan;

An abridged result for one scan node:

{
  "Node Type": "Seq Scan",
  "Schema": "public",
  "Relation Name": "invoices",
  "Alias": "i",
  "Output": ["i.customer_id"]
}

Collecting every relation is a short recursive walk over the plan tree. A simplified version of the idea (not Automata's source):

function collectRelations(node: PlanNode, found = new Set<string>()) {
  if (node['Relation Name']) found.add(`${node['Schema']}.${node['Relation Name']}`)
  for (const child of node['Plans'] ?? []) collectRelations(child, found)
  return found
}

const touched = collectRelations(plan[0].Plan)
const allowed = [...touched].every((table) => tokenAllowlist.has(table))

Because the planner has already resolved names, aliases, and CTEs, the allowlist compares the real tables. VERBOSE also lists output expressions and function calls, so the same pass can reject a function that should not run.

What Automata checks before running a statement

Automata applies this pattern to every Postgres tool call (full details in the PostgreSQL docs):

• One statement only. Each call is a single extended-protocol statement, so a trailing ; DROP TABLE has nowhere to go.
• Plan check first. The statement is checked with EXPLAIN (FORMAT JSON, VERBOSE), and every table in the plan must be on the token's allowlist, or it is refused before it runs.
• Read-only transactions for reads. run-query accepts SELECT, WITH, VALUES, or TABLE, runs it in a read-only transaction, and pages results (200 rows by default, 1,000 at most) with a 10 second default timeout.
• Writes are a separate tool, off by default. run-statement accepts only INSERT, UPDATE, DELETE, or MERGE, rolls back anything over its row cap, and requires allowWholeTable: true for an UPDATE or DELETE without a WHERE.
• Dangerous shapes are refused. DDL, TRUNCATE, COPY, and CREATE TABLE AS are rejected, as are functions outside pg_catalog, SECURITY DEFINER functions, and dangerous built-ins.
• Catalog tools are scoped too. list-tables and describe-table only show tables the token can use.

Two behaviors are worth knowing when you set an allowlist. Views are expanded before planning, so the plan names the underlying tables; allow the base tables, not just the view. And allowing a partitioned table includes its partitions.

What these layers do not protect against

This is the section most guides leave out. A read-only role and a plan allowlist reduce risk a great deal. They do not make an AI connection to your database harmless.

Here is the honest scorecard, before the detail:

Data you allow can still leave your infrastructure

Read-only means Claude cannot change your data. It does not mean the data stays put. Every result row Claude reads becomes part of a conversation processed by its provider. If a table holds personal data, payment details, health information, or anything under a contract or regulation, the question is not whether Claude can write to it but whether that data may be sent to an AI provider at all. Use column grants, a view, or a masked copy for anything you would not paste into a chat.

Prompt injection through your own data

If an allowed table contains text written by customers or the public, that text can contain instructions. Reading it is enough to put them in front of the model. If Claude also has a tool that can send email, browse, or call an API, a malicious row can try to make it move data out. The risky combination is private data, untrusted content, and a way to communicate outward in one session. Keep database access in its own client or session with no outbound tools when the tables hold untrusted text, and review what Claude plans to do before approving actions.

Triggers and function bodies

A plan describes the statement, not everything it may set off. Triggers fire on writes, and the body of a user-defined function is invisible to the plan; the plan only shows the call. That is why Automata refuses functions outside pg_catalog and SECURITY DEFINER functions, and why enabling run-statement deserves a second look at every trigger on the tables you allow. On a read-only setup it is much less of a concern, which is another reason to start there.

Expensive queries and estimates

The planner reports a cost estimate, but estimates are wrong often enough that you cannot rely on them for safety. Functions in pg_catalog, such as pg_sleep and generate_series, are harmless to your data and still costly to run. Timeouts, connection limits, and a replica are the dependable defenses, and the plan check is an extra.

Table-level allowlists are coarse

A plan allowlist works on tables. If a table is allowed, every column in it is reachable unless the role says otherwise. This is the reason column-level GRANT belongs in layer 1 rather than relying on the MCP server. Aggregates can also leak: a query that returns only a count per customer can still reveal facts about a person.

Metadata is visible

PostgreSQL system catalogs are readable by every role by default, so a connected role can generally see schema, table, and column names even for objects it cannot query. Do not put secrets in object names or comments.

The check and the run are separate steps

The plan is a snapshot taken before execution. A schema change in between could, in theory, make the executed plan differ from the one that was checked. The role's grants still apply to the real statement, which is the whole point of having them underneath.

Leaked credentials and tokens

Anyone holding the connection string can reach the database directly and skip every MCP check. Anyone holding a connector token can use it until you revoke it. Keep the string in a secret store, give each client its own token with an expiry, and rotate both.

Set it up with Automata

With the role created and verified, connecting it takes four steps.

1. Add the connection

In the Automata dashboard, create a PostgreSQL database connection and paste the connection string for the role you just created, preferably the replica's host:

postgresql://claude_reader:<password>@replica.example.com:5432/app?sslmode=require

Automata connects before saving, so a wrong password or blocked host fails immediately. Private and loopback hosts are refused by default; if the replica is only reachable on a private network, see private network targets.

2. Create a restricted token

Create a connector token just for Claude and make three choices:

1. Label it clearly, for example claude-analytics, so you can find and revoke it later.
2. Pick the tables. Allow only the tables Claude needs. The allowlist is checked against every query plan, and names are case-sensitive.
3. Leave run-statement off. PostgreSQL tokens start read-only with get-database-info, list-tables, describe-table, and run-query. Do not add run-statement unless you have a reason and a role with exactly the write privileges you want.

Choose an expiry that fits the job, such as 30 or 90 days. Automata shows the token once, so copy it now.

3. Add it to Claude

Adding a connector to Claude.ai, Claude Desktop, or Claude Code works the same way for every Automata connection. Follow step 4 of the Claude guide, using the endpoint and token from your Postgres connection.

4. Test the boundary

Start with something harmless:

What tables can you see in the database, and how many rows does each one have?

Then try to break out. A safe setup refuses every one of these:

Read the email column from the customers table.

Run UPDATE orders SET status = 'cancelled' and tell me how many rows changed.

Query a table that is not on the allowlist, such as users.

Read the file /etc/passwd using pg_read_file.

If any of them succeeds, stop and fix the role before going further. A refusal from the MCP server is good. A refusal from Postgres itself (permission denied) is better, because it means the database would have stopped it even without the server.

Keep it safe

• Use a replica or a copy. Server-level read-only beats any setting a session can change.
• Grant tables and columns by name. Review the grants whenever the schema changes.
• Keep writes out. If you need them, use a separate token and a separate role with only the exact privileges required, and keep row caps low.
• Do not send what you cannot share. Mask or exclude personal and regulated data before it reaches any AI provider.
• Separate untrusted text from outbound tools. Do not combine database access with email, browsing, or other exfiltration paths in the same session.
• One token per client, with an expiry. Revoke it the moment it appears in a log, screenshot, or repository.
• Watch the database too. Check pg_stat_activity and your slow query log for what Claude actually runs.

Frequently asked questions

Is a read-only role enough to make Claude safe on my database?

It stops Claude from changing data, which is the biggest risk, but it does not stop Claude from reading everything the role can see or from sending that data to its provider. Combine the role with column-level grants, a replica, timeouts, and a table allowlist, and decide deliberately which data may be shared.

Can Claude drop tables or delete data through a read-only role?

Not through a role that lacks the privileges, and not through a replica, which rejects all writes. This is exactly why the role, not the prompt, is the boundary.

What does a query-plan check actually add if the role is already read-only?

It enforces scope: which tables a statement touches, one statement per call, and no dangerous functions. It also gives you clear errors and keeps a mistaken query from reaching the database. It does not replace grants; it sits on top of them.

Does this work with managed Postgres such as Neon, Supabase, or RDS?

Yes. Any PostgreSQL database that Automata can reach over the network works, including managed services. Create the role with the SQL above, use the provider's read replica where one exists, and connect with sslmode=require.

Should I let Claude write to the database?

Start without it. If you add it, use a separate role, a separate token with run-statement enabled, narrow table grants, and low row caps, and review the triggers on every table you allow.

Next steps

• Read the PostgreSQL MCP server overview for what each tool does and how scoped access compares to other servers.
• See every safeguard in the PostgreSQL connection docs and the permissions docs.
• Look up each tool's limits in the MCP tools reference.
• Connect Claude, ChatGPT, or Cursor with the MCP clients guide.