dharmit01/postgres-mcp

PostgreSQL MCP Server

A Model Context Protocol (MCP) server exposing PostgreSQL schema exploration and query tools over STDIO (primary transport) plus an experimental SSE HTTP interface.

Features

• Tools:
  • health_check – quick connectivity check
  • get_schema – full columns listing (excluding system schemas)
  • list_tables – list user tables
  • describe_table – describe a specific table ("schema.table")
  • select – safe SELECT-only query execution
  • execute – INSERT/UPDATE/DELETE/DDL (non-SELECT) returning rowCount
  • run_query – unrestricted SQL (use with caution)
• Config via CLI args or environment variables
• Connection pooling via pg
• Works with any MCP-compatible IDE / client on STDIO
• Optional SSE endpoint for experimentation (postgres-mcp-sse binary)

Installation

npm install -g postgres-mcp-server

(Or clone this repo and run npm install && npm run build, then use node dist/index.js.)

Configuration

Provide either a full connection URL or discrete parameters.

Environment Variables

CLI Flags

Any of the above (lowercase) can be supplied as --host, --port, --user, etc. Example:

postgres-mcp --host localhost --database mydb --user me --password secret

STDIO Usage (Primary)

In most MCP-enabled tools you configure a command. Example JSON snippet (e.g. for a hypothetical IDE config):

{
  "name": "Postgres",
  "command": "postgres-mcp",
  "args": ["--host", "localhost", "--database", "mydb", "--user", "me"],
  "env": {"PGPASSWORD": "secret"}
}

The client will perform the MCP initialization handshake, then list and call tools.

Example Direct Invocation

You can manually test with the reference MCP client (if available) or by sending JSON-RPC over stdio (advanced).

Simple health check (using jq for readability) with a minimal mock client is beyond scope here, but the server is ready for any compliant MCP client.

Experimental SSE Server

Start the SSE server:

postgres-mcp-sse --host localhost --database mydb --user me --password secret

Endpoints:

• GET /tools – list tools
• POST /tool/:name – invoke tool (JSON body = params)
• GET /stream/:name – invoke tool and stream a single event (demo only)

Example:

curl -X POST http://localhost:3333/tool/list_tables | jq

Security Notes

• run_query is unrestricted; restrict its use when embedding into shared environments.
• No SQL sanitization is performed beyond parameterization support; always prefer parameter arrays (params) to avoid injection.
• SSE layer is a demo and does no auth; protect or disable in production scenarios.

Development

Clone and build locally:

git clone <repo>
cd postgres-mcp
npm install
npm run build
node dist/index.js --host localhost --database mydb --user me --password secret

Run directly with TypeScript in dev mode:

npm run dev -- --host localhost --database mydb

Tool Output

All tool responses are returned as text content with JSON encoded payload for compatibility. Future enhancements could add structuredContent and output schemas per tool.

Roadmap / Ideas

• Add prompt resources for common admin tasks
• Add streaming for large result sets (pagination)
• Add role-based restrictions
• Add structured output schemas

License

MIT (adjust if needed).