pg-ro-mcp

A read-only PostgreSQL server for the Model Context Protocol (MCP). Lets AI assistants query your PostgreSQL database safely — with zero risk of data modification, even against adversarial prompt injection attempts.

Forked from Anthropic's @modelcontextprotocol/server-postgres (v0.6.2), rewritten in TypeScript with security patches applied.

Security

The original server had a known SQL injection vulnerability documented by Datadog Security Labs: the client.query() method accepts stacked queries, allowing an attacker to inject COMMIT; to escape the read-only transaction and execute arbitrary write operations.

This server applies three layers of defense:

1. Prepared statements — the name field in the query config causes PostgreSQL to use the extended query protocol, which rejects multiple statements at the protocol level. This blocks stacked query injection entirely.
2. Read-only transactions — every user query runs inside BEGIN TRANSACTION READ ONLY, so even if an attacker bypasses other defenses, the database rejects writes.
3. Connection destruction — connections are destroyed (not recycled) after each query via client.release(true), preventing session state poisoning across queries.

Note: Internal metadata queries (listing tables, reading schemas) use pool.query() directly without the security layers. These queries are hardcoded against information_schema and do not accept user input, so the overhead of prepared statements and connection destruction is unnecessary.

Installation

npm install pg-ro-mcp

Or clone and build from source:

git clone https://github.com/SimonJang/pg-ro-mcp.git
cd pg-ro-mcp
npm install
npm run build

Usage

pg-ro-mcp --database-url postgresql://user:password@localhost:5432/mydb

The --database-url flag is required. The server communicates over stdio using the MCP protocol.

Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["pg-ro-mcp", "--database-url", "postgresql://user:password@localhost:5432/mydb"]
    }
  }
}

Claude Code

Add to your .mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["pg-ro-mcp", "--database-url", "postgresql://user:password@localhost:5432/mydb"]
    }
  }
}

MCP Resources

The server exposes database tables as MCP resources:

| Resource | URI Pattern | Description | |----------|-------------|-------------| | List tables | (resource listing) | Returns all tables in the public schema with postgres://{table}/schema URIs | | Table schema | postgres://{table}/schema | Returns column names and data types for the given table as JSON |

MCP Tools

query

Execute a read-only SQL query against the database.

| Parameter | Type | Description | |-----------|------|-------------| | sql | string | SQL query to execute |

Returns a JSON array of result rows on success. On failure, returns a structured MCP error response with isError: true containing the error message and the original SQL.

Example:

SELECT id, name, email FROM users WHERE active = true LIMIT 10

Development

npm run test      # run 17 unit tests (no database required)
npm run build     # compile TypeScript to dist/
npx tsc --noEmit  # type check without emitting

All tests use mocked pg.Pool — no real PostgreSQL connection is needed.

Requirements

• Node.js >= 20
• PostgreSQL database

License

MIT