postgres-mcp

!!! DO NOT USE IN PRODUCTION !!! !!! ONLY FOR LOCAL DEV & DB !!!

A PostgreSQL MCP (Model Context Protocol) server that enables Claude to safely interact with PostgreSQL databases via stdio/JSON-RPC 2.0.

Features

• Execute queries — run parameterized SELECT statements with row limits
• Explore structure — list tables/views and inspect columns, indexes, foreign keys
• Analyze performance — generate EXPLAIN / EXPLAIN ANALYZE plans
• Schema context — expose full DDL-like schema as an MCP resource
• Security — read-only mode, schema whitelisting, SQL injection prevention

Requirements

• Node.js ≥ 18 (ESM)
• PostgreSQL database

Installation

npm install

Configuration

Copy .env.example to .env and fill in your values:

POSTGRES_URL=postgresql://user:password@localhost:5432/dbname
POSTGRES_SCHEMAS=public
POSTGRES_READONLY=true
POSTGRES_QUERY_TIMEOUT=30000
POSTGRES_MAX_ROWS=500

Variable	Default	Description
POSTGRES_URL	(required)	PostgreSQL connection string
POSTGRES_SCHEMAS	public	Comma-separated list of allowed schemas
POSTGRES_READONLY	true	Block any write statement
POSTGRES_QUERY_TIMEOUT	30000	Query timeout in milliseconds
POSTGRES_MAX_ROWS	500	Maximum rows returned per query

Usage

Integration with Claude Code

The server is designed to be run via npx directly from its local directory — no global install needed.

Add it to your Claude Code config (~/.claude/settings.json for global use, or .claude/settings.json at project level):

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["/absolute/path/to/postgres_mcp"],
      "env": {
        "POSTGRES_URL": "postgresql://user:password@localhost:5432/dbname",
        "POSTGRES_SCHEMAS": "public",
        "POSTGRES_READONLY": "true"
      }
    }
  }
}

Replace /absolute/path/to/postgres_mcp with the actual path to this repository on your machine. All configuration is passed via env — no .env file required when using this approach.

Note: npx runs npm install automatically on first launch if node_modules is absent.

Testing with MCP Inspector

The MCP Inspector provides a web UI to test the server interactively without Claude Code.

POSTGRES_URL=postgresql://user:password@localhost:5432/dbname \
npx @modelcontextprotocol/inspector npx /absolute/path/to/postgres_mcp

Then open http://localhost:5173 in your browser. From there you can:

• Call any tool with custom parameters and see the response
• Read the postgres://schema resource
• Inspect the raw JSON-RPC messages exchanged

Tools

query

Execute a SQL SELECT query.

{
  "sql": "SELECT * FROM users WHERE id = $1",
  "params": [42]
}

Returns: rows, rowCount, fields, truncated (if row limit was hit).

list_tables

List all accessible tables and views.

Returns an array of { schema, name, type } objects.

describe_table

Get the full structure of a table: columns, indexes, and foreign keys.

{
  "table": "users",
  "schema": "public"
}

Returns: columns (name, type, nullable, default, primary_key), indexes, foreign_keys.

explain_query

Generate an execution plan for a SQL query.

{
  "sql": "SELECT * FROM orders WHERE user_id = 1",
  "analyze": false
}

Set analyze: true to run EXPLAIN ANALYZE (actually executes the query).

Resource

postgres://schema

Auto-loaded resource exposing the full database schema in DDL-like format. Gives Claude upfront context about all accessible tables and their columns.

Security

• Read-only mode (default): blocks INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE, GRANT, REVOKE, and other write operations via regex validation
• Schema isolation: queries are restricted to schemas listed in POSTGRES_SCHEMAS
• Parameterized queries: prevents SQL injection
• Connection pooling: max 5 connections with idle/connection timeouts
• Row and time limits: configurable caps prevent runaway queries

Tests

npm test

Tests use Vitest with mocked database calls — no live database required.

Project Structure

src/
├── index.js          # MCP server entry point
├── db.js             # Connection pool & configuration
├── security.js       # SQL and schema validation
├── tools/
│   ├── query.js
│   ├── list_tables.js
│   ├── describe_table.js
│   └── explain_query.js
└── resources/
    └── schema.js
tests/
├── security.test.js
├── query.test.js
├── list_tables.test.js
├── describe_table.test.js
├── explain_query.test.js
└── schema_resource.test.js