safe-postgres-mcp

An MCP server that lets a model explore and query PostgreSQL, built so that it cannot modify anything — not by convention, but because three independent layers each have to fail before a write becomes possible.

Runs locally over stdio (Claude Desktop today), with the transport isolated in a single file so an HTTP transport can be added later for a ChatGPT connector.

The three layers

1. Startup privilege audit. Before a single tool is registered, the server interrogates the connected role. If the role is too powerful, the process exits non-zero and the tools never exist. There is no degraded mode.

2. Lexical guard. Every statement must be a single read-only command. Comments and literals are stripped before analysis, so a semicolon hidden inside 'a;b' is not a statement separator and a DROP hidden behind a -- comment is still caught. Queries also travel over the extended query protocol (queryMode: 'extended'), under which the backend refuses to parse more than one command — a protocol-level defence that does not depend on our lexer being correct.

Passing values: [] is not enough to get that protection. pg's requiresPreparation() returns values.length > 0, so an empty array falls back to the simple query protocol, which accepts SELECT 1; SELECT 2. queryMode must be set explicitly — see extended() in src/db.ts.

3. Read-only transaction. Every query runs inside BEGIN TRANSACTION READ ONLY with a statement timeout, and is always rolled back — including on success. The session additionally sets default_transaction_read_only=on.

Why layer 1 is not redundant

A read-only transaction is strong but, in PostgreSQL's own words, "does not prevent all writes to disk". It reliably blocks INSERT/UPDATE/DELETE, all DDL, and those statements nested inside functions, all with SQLSTATE 25006. It does not block:

Escape	Why it works	Required privilege
dblink / postgres_fdw	Opens a separate connection with its own read-write transaction	EXECUTE on the extension
COPY (SELECT …) TO PROGRAM	Runs a shell command; never writes to a table	superuser or pg_execute_server_program
plpython3u, plperlu	Arbitrary filesystem and network I/O inside a SELECT	USAGE on the language
pg_terminate_backend()	Not a write, so read-only does not apply	pg_signal_backend

Every one of those needs a privilege the audit refuses. The audit is what turns the read-only transaction from a strong default into a guarantee.

What the audit checks

Always fatal — these let a query escape the read-only transaction:

• SUPERUSER, BYPASSRLS, REPLICATION
• membership in pg_execute_server_program, pg_write_server_files, pg_read_server_files
• a reachable dblink, postgres_fdw, file_fdw, or untrusted procedural language

Fatal by default, downgraded to a warning by ALLOW_WRITABLE_ROLE=true — the read-only transaction does block these, so overriding is defensible:

• INSERT / UPDATE / DELETE / TRUNCATE on any reachable table
• CREATE on any reachable schema or database
• CREATEDB, CREATEROLE

Privileges are resolved with has_table_privilege() and friends, which account for inheritance through role membership and through PUBLIC — something scanning information_schema misses.

Setup

npm install

Create a read-only role

CREATE ROLE mcp_reader LOGIN PASSWORD 'choose-a-strong-password';
GRANT USAGE ON SCHEMA tenant_acme TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA tenant_acme TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA tenant_acme GRANT SELECT ON TABLES TO mcp_reader;

-- PostgreSQL 14 and older grant CREATE on the public schema to PUBLIC by default:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

If you skip this, the server tells you exactly which privilege blocked it and prints this script scoped to your schema.

Claude Desktop (Windows + WSL)

Dependencies are installed under WSL, so node_modules holds Linux binaries (@esbuild/linux-x64). Claude Desktop runs on Windows and would use node.exe, which cannot load them — so the config invokes the server through WSL rather than directly.

The config file lives in different places depending on how Claude Desktop was installed:

Install	Path
Regular installer	%APPDATA%\Claude\claude_desktop_config.json
Microsoft Store (MSIX)	%LOCALAPPDATA%\Packages\Claude_<id>\LocalCache\Roaming\Claude\claude_desktop_config.json

MSIX packages virtualise %APPDATA%, so the Store build never creates %APPDATA%\Claude. On this machine the real path is:

C:\Users\lucas\AppData\Local\Packages\Claude_pzs8sxrjxfjjc\LocalCache\Roaming\Claude\

That file also holds the app's own preferences, so add mcpServers to it — do not overwrite it.

{
  "mcpServers": {
    "postgres-safe": {
      "command": "wsl.exe",
      "args": [
        "-d", "Ubuntu", "--",
        "bash", "-lc",
        "cd /mnt/c/Users/lucas/Desktop/mcpYt && exec npx tsx src/index.ts"
      ],
      "env": {
        "DATABASE_URL": "postgres://mcp_reader:password@host:5432/mydb",
        "WSLENV": "DATABASE_URL/u:PG_SCHEMA/u:ALLOW_WRITABLE_ROLE/u:MAX_ROWS/u:STATEMENT_TIMEOUT_MS/u"
      }
    }
  }
}

WSLENV is what carries the variables across the Windows/Linux boundary — the /u suffix means "forward this from Win32 into WSL". Keeping the URL in the env block rather than inline in the shell command means passwords containing quotes or $ need no escaping.

If you would rather run entirely on Windows, run npm install from a Windows shell so npm fetches @esbuild/win32-x64, then use "command": "npx" with a Windows path. Note that the two installs overwrite each other in the same node_modules, so pick one.

The connection string lives in the client config, so it never enters the conversation.

Claude Code

.mcp.json in the project root already configures the server for Claude Code running inside WSL — no wsl.exe wrapper needed. Fill in DATABASE_URL and it is picked up on the next session.

Configuration

Variable	Default	Meaning
DATABASE_URL	(required)	Connection string
PG_SCHEMA	auto-detected	Tenant schema. Required only when the role can reach more than one
ALLOW_WRITABLE_ROLE	false	Downgrade the write-grant checks to warnings
MAX_ROWS	200	Row cap per query
STATEMENT_TIMEOUT_MS	10000	Per-query timeout

Multi-tenant scoping

The server anchors to exactly one schema and every tool operates inside it, so unqualified table names resolve there. The role's USAGE grants are the source of truth: if it can reach exactly one non-system schema, that schema is detected automatically; if it can reach several, PG_SCHEMA picks between them. The tenant boundary is enforced by PostgreSQL's grants, not by parsing SQL for cross-schema references.

Tools

Tool	Purpose
query	Run one read-only statement
list_tables	Tables, views and matviews with estimated rows, size and comments
describe_table	Columns, types, defaults, PK, outbound and inbound FKs, constraints, indexes
list_relationships	The schema's whole foreign-key graph, for writing correct JOINs
explain_query	Execution plan, never ANALYZE
get_database_info	Connection details and which safety checks passed

Tests

npm run test:unit          # lexical guard, no database needed
TEST_ADMIN_URL=postgres://owner:pw@host:5432/db npm run test:integration

The integration suite creates a throwaway mcp_test schema with a read-only role and a writable role, then drops both. Its load-bearing test issues an INSERT with a role that genuinely holds INSERT, bypassing the lexical guard entirely, and asserts PostgreSQL rejects it with 25006 — proving layer 3 works on its own rather than assuming it.