Skip to content

Connect Claude Code to Postgres over MCP without delete rights

From the work/ 19 min read

A Postgres role with GRANT SELECT, an MCP server set up in Claude Code with no password in the repo, and a test that shows DELETE, UPDATE and DROP being rejected.

  • Claude Code
  • Databases
  • Security
  • AI
In this article (9)
  1. What an MCP server is and what it does with your database
  2. The risk of giving it full access
  3. Create a read-only role with GRANT SELECT
  4. Configure the Postgres MCP server in Claude Code with that role
  5. Point it at a replica, not the primary
  6. Test that a DELETE is rejected
  7. Common mistakes
  8. Frequently asked questions
  9. Checklist

To connect Claude Code to Postgres over MCP without the risk of deleted data, create a role that only has GRANT SELECT, put it in the MCP server's connection string and, if you can, point it at a replica. The server's read-only mode helps, but Postgres is what rejects the DELETE.

Below are the role's SQL, the MCP server configuration for Claude Code with no password in the repo, and the test that shows a DELETE failing. We ran all the SQL on PGlite 0.5.10, which is PostgreSQL 18.6 compiled to WebAssembly, and tested the MCP server with a stub client, with no model involved.

What an MCP server is and what it does with your database

MCP (Model Context Protocol) is the protocol Claude Code uses to connect to external tools. An MCP server is a program that exposes named tools with parameters; Claude Code decides when to call them and gets back what they return. Local servers run as a process on your machine and talk to Claude Code over stdio, standard input and output, with JSON-RPC messages, as the test at the end shows.

A Postgres MCP server holds the connection string and offers a tool that runs SQL. In DBHub, the server we use here, that tool is called execute_sql: Claude writes the query, the server runs it as the user in the connection string and returns the rows. Claude Code never opens the connection itself. What can happen to your database depends on that user.

Claude Code's MCP documentation uses DBHub as its PostgreSQL example and tells you to use a read-only database user in the connection string so the queries Claude runs can't modify data. The same page says to verify you trust each server before connecting it, because servers that fetch external content expose you to prompt injection. In a store, a product review or an order note is text written by someone outside, and when Claude reads it, it enters Claude's context.

Claude Code never touches Postgres: an MCP server runs the SQLClaude Codewrites the SQLcalls execute_sqlMCP serverDBHub 1.6.1readonly = truePostgresreplicarole mcp_readonlystdio · JSON-RPCDSN = MCP_PG_DSNa DELETE at each layermay ask for any SQLfilter + BEGIN READ ONLYdefault: readonly = falsepermission deniedthe real boundarythe role's privileges decide, not the prompttested locally: stub MCP client, DBHub 1.6.1 and PGlite 0.5.10

Scroll the figure sideways to see all of it

The risk of giving it full access

The easy path is to paste your app's connection string into the MCP server config. That user usually owns the tables and runs the migrations, so it can DELETE, TRUNCATE and DROP. One bad WHERE, one misread instruction or one injected line of text in a row is enough for Claude to run something you did not want, with every permission it needs to do it.

A common answer is to make the server read-only. The reference server, @modelcontextprotocol/server-postgres, ran every query inside a read-only transaction, according to its README. Its repository was archived on May 29, 2025, and on npm version 0.6.2 is marked deprecated with the notice "Archived and unmaintained; no security guarantees" (package page).

On August 21, 2025, Datadog Security Labs showed how to get past that protection. The Node client the server used accepts several statements separated by semicolons, so a query that starts with COMMIT; ends the read-only transaction and whatever follows runs with the user's full privileges. According to the same write-up, the fix landed in the repository but was never released to npm, and its advice includes using a Postgres user with restricted privileges.

We reproduced it on PGlite with the same sequence: open BEGIN TRANSACTION READ ONLY, send the whole query, close with ROLLBACK. As the superuser, COMMIT; DELETE FROM throwaway; left the table empty. As this article's role, COMMIT; SET default_transaction_read_only = off; DELETE FROM orders; got permission denied for table orders and the rows stayed.

default_transaction_read_only is not enough either. The PostgreSQL documentation describes it as the default read-only status of each new transaction, and the SET TRANSACTION page explains that those defaults are changed with SET and can be overridden per transaction. In our test, with the setting on, the role got cannot execute DELETE in a read-only transaction; after SET default_transaction_read_only = off in the same session, only the missing privilege stopped the DELETE.

DBHub, which is maintained, applies two layers when you turn on readonly = true: a keyword filter and running the statement inside BEGIN READ ONLY. But readonly defaults to false and can only be set in its TOML file, and the example in Claude Code's docs passes only --dsn, so there the user is the only barrier. The execute_sql documentation is blunt about it: always connect DBHub with a least-privilege, read-only database user, and do not point a read-only tool at an admin DSN and rely on read-only mode alone.

LayerWhat it blocksHow it gets bypassed
The archived server's read-only transactionWrites inside that transactionCOMMIT; at the start of the query (Datadog; reproduced on PGlite)
default_transaction_read_only = onWrites in each new transactionSET default_transaction_read_only = off from the session itself (tested)
readonly = true in DBHubStatements outside its list (select, with, explain, show), and runs in BEGIN READ ONLYWe did not bypass it; it ships off, and its docs still ask for a least-privilege user
The role's privilegesAnything without a GRANTOnly a more privileged role can grant more

That is why this article goes role first, server second.

Create a read-only role with GRANT SELECT

This is role.sql, the same file the tests load. Run it as a user that can create roles, and replace app_owner with the role that owns your tables:

CREATE ROLE mcp_readonly WITH LOGIN CONNECTION LIMIT 3;

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

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_readonly;

ALTER ROLE mcp_readonly SET default_transaction_read_only = on;
ALTER ROLE mcp_readonly SET statement_timeout = '10s';
  1. CREATE ROLE ... WITH LOGIN creates a role that can log in, and per the CREATE ROLE documentation, CONNECTION LIMIT caps how many concurrent connections it can open. By default it is not a superuser and cannot create databases or roles; the test confirmed that in pg_roles.
  2. GRANT USAGE ON SCHEMA public lets it reach the objects in the schema. Per the schemas documentation, every role already has it on public by default; in any other schema, without it no table is reachable.
  3. GRANT SELECT ON ALL TABLES IN SCHEMA public gives read access to the tables that exist today. The GRANT documentation notes that ALL TABLES also covers views and foreign tables.
  4. ALTER DEFAULT PRIVILEGES FOR ROLE app_owner makes the tables app_owner creates later readable too. It does not touch existing ones.
  5. The two ALTER ROLE ... SET lines set defaults for every session of the role: read-only transactions and a 10-second limit per statement. They are an extra net, not the barrier.

The password is missing, and it should not go in the SQL. The CREATE ROLE page warns that a password in the command travels in cleartext and may end up in the client's command history or the server log, and points to psql's \password command for changing it safely. Connect with psql as an admin and run \password mcp_readonly.

With the role in place, we ran each statement as mcp_readonly against an orders table owned by app_owner. These are the messages PostgreSQL 18.6 returned:

StatementResponse
SELECT count(*) FROM orders2 rows
DELETE FROM orderspermission denied for table orders
UPDATE orders SET total = 0permission denied for table orders
INSERT INTO orders VALUES (3, 'x@example.com', 1)permission denied for table orders
TRUNCATE orderspermission denied for table orders
DROP TABLE ordersmust be owner of table orders
ALTER TABLE orders ADD COLUMN note textmust be owner of table orders
CREATE TABLE scratch (id int)permission denied for schema public

DROP and ALTER fail for a different reason: per the privileges documentation, the right to modify or destroy an object is inherent in being its owner and cannot be granted, and the role owns nothing. CREATE TABLE fails because since PostgreSQL 15 the public schema no longer lets every role create objects. If your database was upgraded from an older version, the schemas documentation says to run REVOKE CREATE ON SCHEMA public FROM PUBLIC.

what the mcp_readonly role can and cannot do in PostgresstatementPostgres answersSELECT count(*) FROM orders2 rowsDELETE FROM orderspermission denied for table ordersUPDATE orders SET total = 0permission denied for table ordersINSERT INTO orders VALUES (…)permission denied for table ordersTRUNCATE orderspermission denied for table ordersDROP TABLE ordersmust be owner of table ordersALTER TABLE orders ADD COLUMN …must be owner of table ordersCREATE TABLE scratch (id int)permission denied for schema publiconly SELECT gets through; the database rejects the restreal messages from PGlite 0.5.10 (PostgreSQL 18.6) under SET ROLE mcp_readonly

Scroll the figure sideways to see all of it

New tables and your migrations role

ALTER DEFAULT PRIVILEGES has a catch. Its documentation says that at creation time only the default privileges of the creating role apply. In the test, a new table from app_owner was readable right away; one created by a migrator role got permission denied for table coupons. Put the role that runs your migrations in FOR ROLE, or repeat the line for each role that creates tables.

What we tested, and what we did not

PGlite has a single session, so SET ROLE mcp_readonly stood in for a login. That leaves two gaps. The ALTER ROLE documentation says per-role settings apply only at login and that neither SET ROLE nor SET SESSION AUTHORIZATION processes them: in the test, SHOW default_transaction_read_only returned off, so those two ALTER ROLE lines went untested. And with SET ROLE the session still belongs to the superuser; under SET SESSION AUTHORIZATION mcp_readonly, which is closer to a real login, SET ROLE postgres got permission denied to set role "postgres". All 18 tests in role.test.mjs passed with node --test.

Configure the Postgres MCP server in Claude Code with that role

We use DBHub because it is the example in Claude Code's docs and because it is maintained: the repository is not archived and shipped three releases in November 2026, from 1.5.1 to 1.6.1. We did not compare it with other servers. The role works the same with any Postgres MCP server; what changes is where its read-only mode is configured.

DBHub only accepts readonly in its TOML file. Its configuration docs say tool options cannot be set with command-line flags, and the old --readonly flag is gone: in its code, passing it stops startup with an error. This is dbhub.toml, the same one the test loads:

[[sources]]
id = "replica"
dsn = "${MCP_PG_DSN}"

[[tools]]
name = "execute_sql"
source = "replica"
readonly = true
max_rows = 200
  1. [[sources]] defines the connection. dsn = "${MCP_PG_DSN}" reads it from an environment variable when the file loads, so the TOML holds no password and can go in git. If the variable is missing, DBHub rejects the source and names the variable.
  2. [[tools]] configures execute_sql for that source, and readonly = true turns on the filter and the read-only transaction.
  3. max_rows = 200 trims each SELECT to 200 rows; if the query already has a smaller LIMIT, the smaller one wins. Fewer rows means less customer data in Claude's context.

The MCP_PG_DSN variable holds the role's connection string, shaped like this: postgres://mcp_readonly:<password>@<replica-host>:5432/<database>?sslmode=require. Set it in your shell or your secrets manager, never in a file in the repo. Per DBHub's docs, sslmode=require encrypts the connection without verifying the certificate, and verify-full checks the certificate and the hostname too. In our test, with this config the server exposed a single tool, execute_sql.

To register it just for yourself, use local scope, which is what claude mcp add uses by default:

claude mcp add --env MCP_PG_DSN="$MCP_PG_DSN" --transport stdio postgres-ro \
  -- npx -y @bytebase/dbhub@1.6.1 --config /absolute/path/dbhub.toml
  1. --env MCP_PG_DSN="$MCP_PG_DSN" passes the variable to the server process; your shell expands it when you run the command. The docs warn that if the server name comes right after --env, the CLI reads it as another pair and rejects it, which is why --transport stdio sits in between.
  2. postgres-ro is the name you will see in /mcp. Without --scope, Claude Code stores it in ~/.claude.json under the project's path, with the value already expanded: it stays out of git, but that file now holds the password and should be treated as a secret.
  3. Everything after -- reaches the server untouched. npx -y downloads and runs @bytebase/dbhub pinned to 1.6.1, so a new release cannot change behavior before you review it; DBHub needs Node 22.5 or later and uses stdio by default.
  4. --config takes an absolute path because Claude Code's docs do not say which directory a stdio server starts in, and DBHub looks for ./dbhub.toml in the current directory when you do not give it one.

To check that it started, run /mcp in Claude Code: per the docs, the server's row should show a checkmark. We did not run that command or the file below; we checked them against claude mcp add --help in Claude Code 2.1.238 and the documentation.

If your team wants the same server, project scope stores it in .mcp.json at the repo root, and the docs recommend checking that file into git. So it must only hold variable references:

{
  "mcpServers": {
    "postgres-ro": {
      "command": "npx",
      "args": ["-y", "@bytebase/dbhub@1.6.1", "--config", "${DBHUB_CONFIG}"],
      "env": {
        "MCP_PG_DSN": "${MCP_PG_DSN}"
      }
    }
  }
}
  1. command and args start the same server as the command above. ${DBHUB_CONFIG} is the absolute path to the TOML on each person's machine.
  2. env hands MCP_PG_DSN to the server. Claude Code expands ${VAR} in command, args, env, url and headers, and accepts ${VAR:-default} for a fallback.
  3. If a variable is unset, the config still loads: claude mcp list shows a warning and the server receives the literal ${VAR} text. In interactive sessions, Claude Code asks for approval before using servers from a .mcp.json; with claude -p it loads them without asking.

Never write the connection string with its password into .mcp.json: the file is made to be shared. If a password reached a commit, change it with \password, because deleting the file does not remove it from history. It is the same rule we covered in Next.js NEXT_PUBLIC_ variables and secrets: whatever lands in the repo or the browser stops being secret.

where each piece of the Postgres MCP setup livespieceholdslives inin git?role.sqlGRANT SELECT, no writesyour migrationsyesdbhub.tomlreadonly = true · max_rowsthe projectyes.mcp.json${VAR} refs, no valuesrepo rootyes~/.claude.jsonclaude mcp add (local)your home dirnoMCP_PG_DSNuser, password, hostshell or vaultneverthe repo holds references, never the passwordClaude Code expands ${VAR} in command, args, env, url and headers

Scroll the figure sideways to see all of it

Point it at a replica, not the primary

The role stops writes, but it does not stop heavy reads. A query with no index over the orders table competes with your checkout for CPU and disk if it runs on the primary. On a replica, that load lands on another server, and the replica adds a barrier of its own: the hot standby documentation says transaction_read_only is always true there and cannot be changed, and that INSERT, UPDATE, DELETE, TRUNCATE, DDL and GRANT produce errors.

Three things to know before you point the server at the replica:

  • Create the role on the primary. On a standby, CREATE and GRANT error out. With physical replication, the role and its privileges reach the replica along with everything else.
  • Data arrives with a delay. The docs call it eventual consistency: the same query run on primary and standby at nearly the same time can return different results. Keep that in mind when you check an order placed a second ago.
  • Long queries can be canceled. When a query conflicts with changes the replica is applying, Postgres cancels it once max_standby_streaming_delay is exceeded. hot_standby_feedback avoids some of those conflicts at the cost of delaying dead-row cleanup on the primary, which can bloat tables.

Without a replica, the same role works on the primary, where the role's statement_timeout and DBHub's max_rows matter more. We did not test a replica: PGlite has no replication, so this section rests on the documentation alone.

Test that a DELETE is rejected

The end-to-end test uses the same chain you will have in Claude Code, with a stub MCP client in place of the model. A Node script speaks JSON-RPC over stdio to DBHub 1.6.1, and DBHub connects to PGlite on 127.0.0.1 through pglite-socket, with the session set to mcp_readonly. We called execute_sql with the TOML above and with the same TOML minus readonly. This is the output, trimmed:

== dbhub-readonly.toml · DBHub MCP Server 1.6.1
> SELECT current_user, session_user, count(*) AS orders FROM orders
  "current_user": "mcp_readonly", "session_user": "mcp_readonly", "orders": "2"
> DELETE FROM orders
  READONLY_VIOLATION: Read-only mode is enabled. Only the following SQL operations are allowed: select, with, explain, show
> COMMIT; DELETE FROM orders
  READONLY_VIOLATION (same message)
== dbhub-default.toml (no readonly)
> DELETE FROM orders
  EXECUTION_ERROR: permission denied for table orders
> COMMIT; DELETE FROM orders
  EXECUTION_ERROR: permission denied for table orders
rows left in orders: 2
  1. The SELECT works and confirms who is asking: current_user and session_user are both mcp_readonly.
  2. With readonly = true, DBHub rejects the DELETE and the COMMIT; DELETE before they reach Postgres, with the code READONLY_VIOLATION.
  3. Without readonly, which is how DBHub runs when you only pass --dsn, both queries reach Postgres and the role rejects them.
  4. At the end, orders still has its two rows.

What this test does not cover: Claude Code was not part of it. We did not run claude mcp add, touch any .mcp.json or call a model. And since PGlite has a single session, we switched it to mcp_readonly with SET SESSION AUTHORIZATION before opening the socket.

Running the test on your own database

With psql and the same variable you can repeat the role part against your database. This is probe.sql, which we also ran on PGlite. Replace orders with one of your tables:

SHOW default_transaction_read_only;
SET default_transaction_read_only = off;
SELECT current_user, session_user;
SELECT rolsuper, rolcreaterole, rolcreatedb, rolbypassrls FROM pg_roles WHERE rolname = current_user;
SELECT count(*) AS owned_tables FROM pg_tables WHERE tableowner = current_user;
DELETE FROM orders WHERE false;
UPDATE orders SET total = total WHERE false;
INSERT INTO orders SELECT * FROM orders WHERE false;
  1. SHOW should say on on a real connection, which confirms the ALTER ROLE ... SET applied at login. On PGlite it said off, for the reason above.
  2. The SET turns that default off for this session only. Without it, every write fails with cannot execute DELETE in a read-only transaction before privileges are checked, and you would not learn whether the role is right.
  3. The next three queries should show mcp_readonly, four false values and 0 owned tables.
  4. Every write has WHERE false: it touches no rows even if the role were misconfigured, and Postgres still checks privileges. In the test, all three got permission denied for table orders.

Run it with psql "$MCP_PG_DSN" -f probe.sql; psql moves on to the next statement when one fails. Do it once against the primary: on the replica, writes always fail for being read-only, and you would not see the privilege check. Then, in Claude Code, ask it to run DELETE FROM orders WHERE false with the Postgres tool and show you the exact error. With readonly = true you should see READONLY_VIOLATION, as in our test.

Common mistakes

  • Using the app's user. If the DSN carries the table owner, the server's read-only mode is the only thing between Claude and a DROP.
  • Trusting the server alone. The archived server fell to COMMIT;, default_transaction_read_only goes off with one SET, and DBHub's readonly ships as false. The barrier is the role's privileges.
  • Forgetting FOR ROLE. Without it, tables your migrations role creates are unreadable, and the temptation is to give the MCP role more privileges.
  • SECURITY DEFINER functions. PostgreSQL grants EXECUTE on functions to PUBLIC by default. In the test, a SECURITY DEFINER function owned by app_owner that deletes orders ran from mcp_readonly and deleted a row. After REVOKE EXECUTE ON FUNCTION purge_orders() FROM PUBLIC it got permission denied for function purge_orders; inside BEGIN READ ONLY it failed with cannot execute DELETE in a read-only transaction. Audit yours.
  • The password in .mcp.json. Use ${VAR} and set the variables outside the repo.
  • --env right before the name. claude mcp add --env A=1 postgres-ro ... fails; put --transport stdio between them.
  • Granting read on everything. GRANT SELECT ON ALL TABLES includes customers, emails and addresses. And the GRANT docs warn that revoking one column after granting the whole table does not do what you might expect: the table-level grant stays.

Frequently asked questions

Can I use pg_read_all_data instead of GRANT SELECT?

Yes, since PostgreSQL 14. Per the predefined roles documentation, pg_read_all_data reads all tables, views and sequences, with USAGE on every schema, and does not bypass row-level security policies. It is broader than GRANT SELECT on one schema, so use it only if you really want Claude to read everything.

Isn't DBHub's read-only mode enough?

It is a good second layer: in our test it rejected the DELETE before it reached Postgres. But it ships off, lives in a file someone can edit, and its own docs ask for a least-privilege user. The role protects you even if you switch servers or configs.

What if I don't have a replica?

Use the same role on the primary, with a low statement_timeout and CONNECTION LIMIT, and max_rows in DBHub. What you lose is load separation: a heavy query Claude writes runs on the same server as your store.

Can Claude see my customers' personal data?

Yes, anything the role can read can end up in Claude's context. If it does not need it, do not grant it: grant SELECT per table or per column instead of ALL TABLES, or create views without those columns in a separate schema and grant access to that schema only.

Checklist

  • The MCP server uses its own role, not the app's user or the migrations user.
  • The role has USAGE on the schema and SELECT on the tables it needs, nothing more.
  • ALTER DEFAULT PRIVILEGES FOR ROLE names the role that creates your tables.
  • The password was set with \password and lives only in MCP_PG_DSN, outside the repo.
  • dbhub.toml has readonly = true and max_rows, and the server version is pinned.
  • .mcp.json only holds ${VAR} references.
  • The DSN points at a replica, or at the primary with a low statement_timeout.
  • probe.sql gets permission denied on all three writes and 0 owned tables.
  • SECURITY DEFINER functions that write do not grant EXECUTE to PUBLIC.

With the role in place, the MCP server can be wrong or change and your data stays put. If you also want Claude Code to stay out of your environment files, see the Claude Code hook that protects your .env, and review which Next.js variables must never hold secrets. If your jobs table lives in the same database, the Postgres job queue with SKIP LOCKED is a good place to try the role.

Saw this on Instagram? Comment MCP on the Reel and I'll send you the guide.

Written by

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published November 11, 2026

← Blog