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)
- What an MCP server is and what it does with your database
- The risk of giving it full access
- Create a read-only role with GRANT SELECT
- Configure the Postgres MCP server in Claude Code with that role
- Point it at a replica, not the primary
- Test that a DELETE is rejected
- Common mistakes
- Frequently asked questions
- 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.
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.
| Layer | What it blocks | How it gets bypassed |
|---|---|---|
| The archived server's read-only transaction | Writes inside that transaction | COMMIT; at the start of the query (Datadog; reproduced on PGlite) |
default_transaction_read_only = on | Writes in each new transaction | SET default_transaction_read_only = off from the session itself (tested) |
readonly = true in DBHub | Statements outside its list (select, with, explain, show), and runs in BEGIN READ ONLY | We did not bypass it; it ships off, and its docs still ask for a least-privilege user |
| The role's privileges | Anything without a GRANT | Only 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';
CREATE ROLE ... WITH LOGINcreates a role that can log in, and per the CREATE ROLE documentation,CONNECTION LIMITcaps how many concurrent connections it can open. By default it is not a superuser and cannot create databases or roles; the test confirmed that inpg_roles.GRANT USAGE ON SCHEMA publiclets it reach the objects in the schema. Per the schemas documentation, every role already has it onpublicby default; in any other schema, without it no table is reachable.GRANT SELECT ON ALL TABLES IN SCHEMA publicgives read access to the tables that exist today. The GRANT documentation notes thatALL TABLESalso covers views and foreign tables.ALTER DEFAULT PRIVILEGES FOR ROLE app_ownermakes the tablesapp_ownercreates later readable too. It does not touch existing ones.- The two
ALTER ROLE ... SETlines 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:
| Statement | Response |
|---|---|
SELECT count(*) FROM orders | 2 rows |
DELETE FROM orders | permission denied for table orders |
UPDATE orders SET total = 0 | permission denied for table orders |
INSERT INTO orders VALUES (3, 'x@example.com', 1) | permission denied for table orders |
TRUNCATE orders | permission denied for table orders |
DROP TABLE orders | must be owner of table orders |
ALTER TABLE orders ADD COLUMN note text | must 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.
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
[[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.[[tools]]configuresexecute_sqlfor that source, andreadonly = trueturns on the filter and the read-only transaction.max_rows = 200trims eachSELECTto 200 rows; if the query already has a smallerLIMIT, 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
--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 stdiosits in between.postgres-rois the name you will see in/mcp. Without--scope, Claude Code stores it in~/.claude.jsonunder 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.- Everything after
--reaches the server untouched.npx -ydownloads and runs@bytebase/dbhubpinned to 1.6.1, so a new release cannot change behavior before you review it; DBHub needs Node 22.5 or later and usesstdioby default. --configtakes an absolute path because Claude Code's docs do not say which directory a stdio server starts in, and DBHub looks for./dbhub.tomlin 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}"
}
}
}
}
commandandargsstart the same server as the command above.${DBHUB_CONFIG}is the absolute path to the TOML on each person's machine.envhandsMCP_PG_DSNto the server. Claude Code expands${VAR}incommand,args,env,urlandheaders, and accepts${VAR:-default}for a fallback.- If a variable is unset, the config still loads:
claude mcp listshows 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; withclaude -pit 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.
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,
CREATEandGRANTerror 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_delayis exceeded.hot_standby_feedbackavoids 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
- The
SELECTworks and confirms who is asking:current_userandsession_userare bothmcp_readonly. - With
readonly = true, DBHub rejects theDELETEand theCOMMIT; DELETEbefore they reach Postgres, with the codeREADONLY_VIOLATION. - Without
readonly, which is how DBHub runs when you only pass--dsn, both queries reach Postgres and the role rejects them. - At the end,
ordersstill 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;
SHOWshould sayonon a real connection, which confirms theALTER ROLE ... SETapplied at login. On PGlite it saidoff, for the reason above.- The
SETturns that default off for this session only. Without it, every write fails withcannot execute DELETE in a read-only transactionbefore privileges are checked, and you would not learn whether the role is right. - The next three queries should show
mcp_readonly, fourfalsevalues and0owned tables. - 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 gotpermission 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_onlygoes off with oneSET, and DBHub'sreadonlyships asfalse. 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 DEFINERfunctions. PostgreSQL grantsEXECUTEon functions toPUBLICby default. In the test, aSECURITY DEFINERfunction owned byapp_ownerthat deletes orders ran frommcp_readonlyand deleted a row. AfterREVOKE EXECUTE ON FUNCTION purge_orders() FROM PUBLICit gotpermission denied for function purge_orders; insideBEGIN READ ONLYit failed withcannot execute DELETE in a read-only transaction. Audit yours.- The password in
.mcp.json. Use${VAR}and set the variables outside the repo. --envright before the name.claude mcp add --env A=1 postgres-ro ...fails; put--transport stdiobetween them.- Granting read on everything.
GRANT SELECT ON ALL TABLESincludes 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
USAGEon the schema andSELECTon the tables it needs, nothing more. ALTER DEFAULT PRIVILEGES FOR ROLEnames the role that creates your tables.- The password was set with
\passwordand lives only inMCP_PG_DSN, outside the repo. dbhub.tomlhasreadonly = trueandmax_rows, and the server version is pinned..mcp.jsononly holds${VAR}references.- The DSN points at a replica, or at the primary with a low
statement_timeout. probe.sqlgetspermission deniedon all three writes and0owned tables.SECURITY DEFINERfunctions that write do not grantEXECUTEtoPUBLIC.
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

Published November 11, 2026