Skip to content

devopam/MCPg

Repository files navigation

MCPg

A production-grade Model Context Protocol server for PostgreSQL. Lets AI agents safely inspect, query, operate, and tune a Postgres database — over 100 tools spanning catalog introspection, query intelligence, natural-language SQL, structural diffs, hybrid search, graph queries, data movement, live ops, and more.

PyPI version Python versions License: MIT CI


Why MCPg

  • Safe by default. Read-only access mode. Every user-supplied SQL statement parses through a validated AST allowlist before execution. Identifier interpolation flows through a strict [A-Za-z_][A-Za-z0-9_]* regex — a design constraint that means user input never reaches the database through string concatenation. Capabilities like DDL, shell, and LISTEN/NOTIFY are off until you opt in.
  • One server, broad surface. Application data access (queries, search, cursors, NL→SQL) and DBA-grade operations (health checks, index tuning, EXPLAIN analysis, locks, vacuum, dumps, replicas, migrations) in a single MCP server. Agents don't have to switch tools to switch tasks.
  • PostgreSQL-native everything. No ORM, no abstraction tax — uses psycopg3 directly, speaks every pg_* system view, integrates with TimescaleDB, pgvector, PostGIS, Apache AGE, and pg_stat_statements where they're available, and degrades gracefully when they aren't.
  • Production-shaped, not demo-shaped. Connection pooling, per-request SET ROLE multi-tenancy, read-replica routing with degraded-host detection, server-side cursors with dedicated connections, rate-limiting, audit trail with regex redaction, PG TLS enforcement on startup, OIDC JWT bearer auth, per-session statement / lock timeouts.
  • Observability built in. Prometheus /metrics endpoint on the HTTP transport surfaces mcpg_tool_calls_total{tool,status} + mcpg_tool_duration_seconds. Every tool call records a structured audit event with credential-redacted arguments.
  • Test-driven, multi-version. 800+ unit tests plus an integration suite that runs against a real PostgreSQL container in CI — matrix covers PG 14, 15, 16, 17, 18 on every push.

Install

From PyPI (recommended)

pip install mcpg
# or, in an isolated venv exposed globally:
uv tool install mcpg

Verify:

mcpg --version

Docker

docker build -t mcpg https://github.com/devopam/MCPg.git
docker run --rm -p 8000:8000 \
    -e MCPG_DATABASE_URL=postgresql://user:pass@host:5432/db \
    -e MCPG_ACCESS_MODE=read-only \
    mcpg

Multi-stage image: runtime stage drops the build toolchain, runs as uid=10001 / gid=10001 with nologin shell, application files root-owned and read-only to the runtime user.

From source (developers)

git clone https://github.com/devopam/MCPg && cd MCPg
uv sync

uv sync creates a venv with all runtime + dev dependencies and exposes the mcpg console script.

More detail in the Installation Guide.


Quick start

Wire MCPg into Claude Desktop (stdio transport)

Drop this into your claude_desktop_config.json (macOS: ~/Library/Application Support/Claude/claude_desktop_config.json; Windows: %APPDATA%\Claude\claude_desktop_config.json):

{
  "mcpServers": {
    "mcpg": {
      "command": "uvx",
      "args": ["mcpg"],
      "env": {
        "MCPG_DATABASE_URL": "postgresql://user:pass@localhost:5432/mydb"
      }
    }
  }
}

Restart Claude Desktop. The MCPg toolset is now available to the model. You can ask Claude things like:

"What schemas exist in this database? For each one, summarise the three biggest tables."

"Why is this query slow? SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC"

Run as an HTTP server (for IDE integrations, web apps, etc.)

MCPG_DATABASE_URL=postgresql://user:pass@localhost:5432/mydb \
MCPG_TRANSPORT=streamable-http \
MCPG_HTTP_PORT=8000 \
mcpg

Then point any MCP-aware client at http://localhost:8000. Set MCPG_HTTP_AUTH_TOKEN=... for a static bearer, or MCPG_AUTH_MODE=oidc for full JWT validation against an OIDC issuer.


Configuration

MCPg is configured entirely through environment variables — no config file, no flags. The only required one is MCPG_DATABASE_URL; everything else has a safe default.

Common scenarios

Scenario Set
Local exploration, read-only MCPG_DATABASE_URL
Read-write app data access MCPG_ACCESS_MODE=restricted
DBA toolkit (DDL, vacuum, etc.) MCPG_ACCESS_MODE=unrestricted + MCPG_ALLOW_DDL=true
HTTP transport with bearer auth MCPG_TRANSPORT=streamable-http + MCPG_HTTP_AUTH_TOKEN=…
Multi-tenant SaaS MCPG_DEFAULT_ROLE=tenant_a + MCPG_ALLOWED_ROLES=tenant_a,tenant_b,…
Read-replica fan-out MCPG_REPLICA_URLS=postgresql://…?sslmode=require,postgresql://…?sslmode=require
NL→SQL — single provider Set ANTHROPIC_API_KEY (or OPENAI_API_KEY / GEMINI_API_KEY). MCPg auto-picks the default.
NL→SQL — multiple providers, caller picks Set all vendor keys you want active. Each call to translate_nl_to_sql can pass provider="anthropic"|"openai"|"gemini".

Full reference

Core

Variable Default Description
MCPG_DATABASE_URL required Primary PostgreSQL DSN. Supports URI (postgresql://…) and keyword (host=… user=…) forms. Remote hosts require sslmode=require (or stronger).
MCPG_ACCESS_MODE read-only read-only | restricted (allows write tools) | unrestricted (also unlocks DBA tools when paired with the gate vars).
MCPG_TRANSPORT stdio stdio (default, for Claude Desktop) | streamable-http | sse.
MCPG_LOG_LEVEL INFO DEBUG | INFO | WARNING | ERROR | CRITICAL.
MCPG_HTTP_HOST 127.0.0.1 Bind address for HTTP transports. Set to 0.0.0.0 inside containers.
MCPG_HTTP_PORT 8000 Listen port for HTTP transports (1–65535).

Capability gates (opt-in for higher-blast-radius tools)

Variable Default Description
MCPG_ALLOW_DDL false Expose DDL tools (run_ddl, create_graph, drop_graph, hypertable tools, migration tools). Requires MCPG_ACCESS_MODE=unrestricted.
MCPG_ALLOW_SHELL false Expose subprocess-backed tools (dump_database, restore_database, run_pg_binary). Required PG client binaries must be on PATH.
MCPG_ALLOW_LISTEN false Expose LISTEN/NOTIFY tools (subscribe_channel, poll_notifications, unsubscribe_channel, list_notification_subscriptions).

Authentication (HTTP transports only)

Variable Default Description
MCPG_AUTH_MODE static static (compare bearer to MCPG_HTTP_AUTH_TOKEN) | oidc (full JWT validation).
MCPG_HTTP_AUTH_TOKEN Required bearer token when MCPG_AUTH_MODE=static. Constant-time compare.
MCPG_OIDC_ISSUER OIDC issuer URL (required when MCPG_AUTH_MODE=oidc).
MCPG_OIDC_AUDIENCE Expected aud claim (required when MCPG_AUTH_MODE=oidc).
MCPG_OIDC_JWKS_URL discovered Override JWKS endpoint (auto-discovered from issuer's .well-known otherwise).
MCPG_OIDC_ROLE_CLAIM JWT claim whose value becomes the per-request PG role (SET LOCAL ROLE). Composes with the tenancy driver.

HTTP hardening (HTTP transports only)

Variable Default Description
MCPG_HTTP_MAX_BODY_BYTES 1048576 (1 MiB) Request bodies above this get a 413. Counts streamed bytes, so a missing/lying Content-Length can't bypass it.
MCPG_HTTP_ALLOWED_ORIGINS Comma-separated CORS allowlist. Unset = no CORS middleware (no cross-origin headers emitted).
MCPG_HTTP_HSTS_MAX_AGE 31536000 Strict-Transport-Security max-age. 0 disables the HSTS header. Security headers (CSP, X-Frame-Options, X-Content-Type-Options, Referrer-Policy) are always added unless the app already set them.
MCPG_HTTP_REQUEST_TIMEOUT_SECONDS 0 Per-request wall-clock cap (504 on expiry). 0 = disabled. Leave off if you rely on long-lived SSE / streamable-http streams — a hard cap also severs those.

Multi-tenancy (SET ROLE)

Variable Default Description
MCPG_DEFAULT_ROLE Static PG role applied to every query. Identifier-validated.
MCPG_ALLOWED_ROLES Comma-separated allowlist. When set, the X-MCPG-Role header / OIDC role claim must be in this list.

Read replicas

Variable Default Description
MCPG_REPLICA_URLS Comma-separated replica DSNs. force_readonly queries round-robin across healthy replicas; primary fallback on failure; 30 s degraded-replica retry window.

Pool / timeouts / TLS

Variable Default Description
MCPG_POOL_MIN_SIZE 1 Minimum pool connections.
MCPG_POOL_MAX_SIZE 5 Maximum pool connections. Must be ≥ MCPG_POOL_MIN_SIZE.
MCPG_STATEMENT_TIMEOUT_MS 30000 Per-session statement_timeout set on connection checkout. Runaway queries self-terminate.
MCPG_LOCK_TIMEOUT_MS 5000 Per-session lock_timeout. Hanging lock waits self-terminate.
MCPG_ALLOW_INSECURE_TLS false Bypass the startup TLS check that refuses remote DSNs without sslmode=require (or stronger). Loopback hosts are always exempt.
MCPG_SHUTDOWN_DRAIN_SECONDS 30 On SIGTERM, wait up to this long for in-flight tool calls to finish before closing the pool and cursors.

Subprocess tools (MCPG_ALLOW_SHELL=true only)

Variable Default Description
MCPG_SHELL_TIMEOUT_SEC 60 Max wall-clock for pg_dump / pg_restore / psql invocations.
MCPG_SHELL_MAX_OUTPUT_BYTES 67108864 (64 MiB) Cap on captured stdout per subprocess call.
MCPG_SUBPROCESS_BIN_ALLOWLIST Comma-separated absolute dirs the resolved pg_dump / pg_restore / psql must live under. Empty = trust PATH. Defeats a PATH-shim of these binaries.
MCPG_SUBPROCESS_CPU_SECONDS Per-child RLIMIT_CPU (seconds). POSIX only; unset = inherit.
MCPG_SUBPROCESS_MEMORY_MB Per-child RLIMIT_AS (MiB). POSIX only; unset = inherit.

LISTEN/NOTIFY (MCPG_ALLOW_LISTEN=true only)

Variable Default Description
MCPG_LISTEN_QUEUE_MAX 1000 Per-channel buffer; oldest notifications dropped on overflow.

Audit

Variable Default Description
MCPG_AUDIT_PERSIST false When true, every run_write / run_ddl call persists to a mcpg_audit.events table (auto-created idempotently).
MCPG_AUDIT_REDACT_KEYS Comma-separated regex fragments added to the secret-name pattern (defaults already cover password, passwd, secret, token, api[_-]?key, bearer, authorization, database_url, dsn, conninfo).
MCPG_AUDIT_INTEGRITY false When true, each persisted event is signed with an HMAC chained over the previous event; the verify_audit_chain tool walks the chain and reports the first break. Requires MCPG_AUDIT_HMAC_KEY.
MCPG_AUDIT_HMAC_KEY Secret key for the audit HMAC chain. Required when MCPG_AUDIT_INTEGRITY=true. Never appears in repr/logs.

Secrets backend

By default every secret is read straight from the environment. Set MCPG_SECRETS_BACKEND=file to instead load API keys / bearer token / HMAC key from a mounted file — a name in the file wins; anything absent falls back to the env var, so partial files work.

Variable Default Description
MCPG_SECRETS_BACKEND env env (read every secret from the environment) | file (overlay a secrets file on top of the environment).
MCPG_SECRETS_FILE_PATH Required when MCPG_SECRETS_BACKEND=file. Path to a flat name → value map: JSON always, or YAML (.yaml/.yml) when PyYAML is installed. Covers ANTHROPIC_API_KEY / OPENAI_API_KEY / GEMINI_API_KEY / GOOGLE_API_KEY / MCPG_NL2SQL_API_KEY, MCPG_HTTP_AUTH_TOKEN, and MCPG_AUDIT_HMAC_KEY.

Rate limiting

Variable Default Description
MCPG_RATE_LIMIT_ENABLED false Enable token-bucket per-tool rate limiting.
MCPG_RATE_LIMIT_MAX_REQUESTS 60 Global cap per window across all tools.
MCPG_RATE_LIMIT_WINDOW_SECONDS 60 Window length for the global quota.
MCPG_RATE_LIMIT_HEAVY_MAX 5 Cap for heavy tools (run_write, run_ddl, dump_database, etc.).
MCPG_RATE_LIMIT_HEAVY_WINDOW 60 Window length for the heavy-tool quota.

Caching & Feature flags

Variable Default Description
MCPG_CACHE_ENABLED true Enable or disable the adaptive cache layer.
MCPG_CACHE_TTL_SECONDS 300 Default cache Time-To-Live in seconds.
MCPG_CACHE_MAXSIZE 1024 Maximum LRU capacity bound for the memory cache.
MCPG_REDIS_URL Optional Redis backend connection string for external, multi-node caching.
MCPG_ENABLE_HEAVY_DIAGNOSTICS true Toggle computationally heavy diagnostic, diagram, and advisor tools.

Natural-language SQL

MCPg auto-discovers every configured provider from the environment at startup. Set as many of ANTHROPIC_API_KEY / OPENAI_API_KEY / GEMINI_API_KEY (or GOOGLE_API_KEY) as you have — each becomes callable. When MCPG_NL2SQL_PROVIDER is unset, MCPg picks the default in preference order anthropic → openai → gemini. The translate_nl_to_sql tool accepts an optional provider="…" argument so a caller can route between providers per call; get_server_info reports which are available.

Variable Default Description
ANTHROPIC_API_KEY Vendor-conventional key for Anthropic / Claude.
OPENAI_API_KEY Vendor-conventional key for OpenAI.
GEMINI_API_KEY or GOOGLE_API_KEY Vendor-conventional key for Google / Gemini.
MCPG_NL2SQL_PROVIDER auto-picked anthropic | openai | gemini. Pins the default provider used when the tool is called without provider=. When unset and any vendor key is in the env, MCPg auto-picks anthropic → openai → gemini.
MCPG_NL2SQL_API_KEY Explicit key for the configured MCPG_NL2SQL_PROVIDER. Overrides the vendor-conventional env var for that provider only. Requires MCPG_NL2SQL_PROVIDER to be set.
MCPG_NL2SQL_MODEL provider default Override the default model (e.g. claude-sonnet-4-6, gpt-4o-mini, gemini-2.5-flash). Applies only to the default provider.
MCPG_NL2SQL_BASE_URL OpenAI-compatible endpoint override (Ollama, vLLM, OpenRouter). Applies only when the default provider is openai.
MCPG_NL2SQL_MAX_TOKENS 2048 Cap on generated tokens (hard limit: 16384).

Usage examples

The MCP tools are invoked by the agent (Claude, Cursor, Continue, …) in response to your natural-language instructions. A handful of illustrative round-trips:

Inspect the schema

You: What tables live in the public schema, and which ones are the biggest by row count?

Agent (using list_tables + summarize_table × N): Six tables: customers (1.2M rows), orders (4.7M), line_items (18.3M), products (340), addresses (1.4M), audit_log (45.8M — the largest, accumulates without retention).

Diagnose a slow query

You: Why does SELECT * FROM orders o JOIN line_items l ON o.id = l.order_id WHERE o.created_at > now() - interval '7 days' take 8 seconds?

Agent (using why_is_this_slow + recommend_indexes): EXPLAIN ANALYZE shows a sequential scan over orders (4.7M rows) filtered by created_at. There's no index on orders.created_at. Recommended: CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at DESC); Expected improvement: ~95% reduction (index scan touches ~33K rows instead of full table). Run validate_migration on it before applying to confirm there's no locking surprise.

Translate plain English to SQL

You: Find customers who placed an order this month but never in the previous three months — show me their email and current lifetime spend.

Agent (using translate_nl_to_sql):

SELECT c.email, SUM(l.price * l.quantity) AS lifetime_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN line_items l ON l.order_id = o.id
WHERE EXISTS (
  SELECT 1 FROM orders o2 WHERE o2.customer_id = c.id
    AND o2.created_at >= date_trunc('month', now()))
  AND NOT EXISTS (
  SELECT 1 FROM orders o3 WHERE o3.customer_id = c.id
    AND o3.created_at >= date_trunc('month', now()) - interval '3 months'
    AND o3.created_at <  date_trunc('month', now()))
GROUP BY c.email;

Picture the schema

You: Draw me an ER diagram of the public schema.

Agent (using generate_schema_diagram): Returns a Mermaid diagram you can paste straight into GitHub / Notion / Obsidian.

Audit the database

You: How healthy is this database right now?

Agent (using audit_database): Returns a graded report: Memory & I/O score 92 (GOOD), Transaction & Connection 78 (WARNING: rollback rate 0.4%, look at app logs), Concurrency & Locks 60 (CRITICAL: 14 backends waiting), Cleanliness & Bloat 88 (GOOD), Slow queries 70 (WARNING: top query template runs 5000×, mean 90 ms — see optimize_query).

Run a guarded write

You: Soft-delete every order older than 5 years.

Agent (using run_write with MCPG_AUDIT_PERSIST=true): Validates the statement through the safe-SQL kernel, runs it inside a transaction, returns affected row count, persists the call (sql + arguments — with secrets regex-redacted — + status) to mcpg_audit.events for after-the-fact review.

For dozens more recipes — multi-tenant routing, RLS testing, NL→SQL, hybrid vector + FTS search, Apache AGE Cypher, TimescaleDB, ORM schema exports, server-side cursors — see docs/cookbook.md.


What's in the box

Compact category list. For the full, current tool reference see docs/tools.md; for a guided walkthrough see docs/tour.md.

  • Catalog introspection — schemas, tables, columns, indexes, constraints, views, functions, triggers, sequences, partitions, policies, roles, grants, enums, domains, composite types, FDWs, publications, subscriptions, extensions, generated columns.
  • Query intelligencerun_select, run_select_parallel, explain_query, analyze_query_plan, why_is_this_slow, recommend_indexes, analyze_workload, check_database_health, detect_n_plus_one, audit_database.
  • Searchfuzzy_search (trigram), full_text_search, vector_search, hybrid_search (pgvector + FTS via RRF), geo_search (PostGIS k-NN).
  • Natural language → SQLtranslate_nl_to_sql (Anthropic, OpenAI, or Gemini; output passes through the same safe-SQL kernel as hand-written queries).
  • Visualisationgenerate_schema_diagram (ER), generate_fk_cascade_graph (blast-radius of ON DELETE CASCADE), generate_graph_diagram (Apache AGE property graphs).
  • Structural diff & migrationscompare_schemas, validate_migration, staged prepare_migration / complete_migration / cancel_migration workflow.
  • Apache AGE graph + Cypherlist_graphs, describe_graph, run_cypher, create_graph, drop_graph, generate_graph_diagram.
  • Composite + advisor toolssummarize_table, find_unused_objects, find_sensitive_columns (PII heuristic), lint_naming_conventions, test_rls_for_role, list_locks, find_blocking_chains, read_pg_stat_io (PG16+), generate_test_data.
  • Live ops & maintenancelist_active_queries, verify_connection_encryption (TLS status of the live link), run_maintenance (VACUUM/ANALYZE), prune_audit_events (audit retention), cancel_query, terminate_backend, run_write, run_ddl, enable_extension.
  • Data movementexport_query / export_table (CSV/JSON), dump_database / restore_database, import_csv / import_json (COPY FROM STDIN), copy_table_between_databases.
  • Server-side cursorsopen_cursor, fetch_cursor, close_cursor, list_cursors for pageable reads over millions of rows.
  • TimescaleDBlist_hypertables, list_chunks, create_hypertable, add_compression_policy, add_retention_policy.
  • ORM schema exporters — Prisma, Drizzle, SQLAlchemy, sqlc, Diesel, jOOQ, Ent, Ecto.
  • Event streamssubscribe_channel, poll_notifications, unsubscribe_channel, list_notification_subscriptions bridging PostgreSQL LISTEN/NOTIFY into the MCP poll model.
  • Observability — Prometheus /metrics endpoint + get_metrics_exposition tool for stdio; structured audit trail with regex-based credential redaction.

Documentation


Security

  • Vulnerability reporting: see SECURITY.md. 90-day coordinated-disclosure window; reports to devopam@gmail.com.
  • Defence-in-depth: capability gates, SafeSQL kernel, identifier allowlist, audit redaction, PG TLS enforcement at startup, rate-limiting, OIDC JWT validation, per-session timeouts.
  • See docs/security-hardening.md for the living roadmap of shipped (✅) and queued (⬜) hardening items.

Release notes & changelog

See CHANGELOG.md for the full version history, docs/release-process.md for how releases are cut, and the GitHub Releases page for downloadable artifacts.


Contributing

Pull requests welcome — see CONTRIBUTING.md for the dev-loop setup, test conventions, and the per-PR review checklist.


License

MIT — see LICENSE. The vendored SQL-safety kernel at src/mcpg/_vendor/sql/ is also MIT-licensed; see NOTICE for provenance.

Disclaimer. Best efforts have been made to bring MCPg to production grade, but it remains an actively developed project and may contain issues. See the License terms for indemnity details.

About

Postgres MCP Server

Resources

License

Contributing

Security policy

Stars

Watchers

Forks

Packages

 
 
 

Contributors

Languages