{
  "markdown": "\n\n# MCPg\n\n[![MCP Toplist](https://mcptoplist.com/badge/io.github.devopam%2Fmcpg.svg)](https://mcptoplist.com/server/io.github.devopam%2Fmcpg)\n\n**A production-grade [Model Context Protocol](https://modelcontextprotocol.io)\nserver for PostgreSQL.** It lets AI agents safely inspect, query, operate, and\ntune a Postgres database — 254 tools spanning catalog introspection,\nquery intelligence, natural-language SQL, structural diffs, hybrid search,\ngraph queries, data movement, live ops, and more.\n\n\n[![PyPI version](https://img.shields.io/pypi/v/mcpg.svg)](https://pypi.org/project/mcpg/)\n[![Python versions](https://img.shields.io/pypi/pyversions/mcpg.svg)](https://pypi.org/project/mcpg/)\n[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://github.com/devopam/MCPg/blob/main/LICENSE)\n[![CI](https://github.com/devopam/MCPg/actions/workflows/ci.yml/badge.svg)](https://github.com/devopam/MCPg/actions/workflows/ci.yml)\n[![OpenSSF Scorecard](https://api.scorecard.dev/projects/github.com/devopam/MCPg/badge)](https://scorecard.dev/viewer/?uri=github.com/devopam/MCPg)\n[![OpenSSF Best Practices](https://www.bestpractices.dev/projects/13958/badge)](https://www.bestpractices.dev/projects/13958)\n[![Stars](https://img.shields.io/github/stars/devopam/MCPg)](https://github.com/devopam/MCPg)\n[![smithery badge](https://smithery.ai/badge/devopam/mcpg)](https://smithery.ai/servers/devopam/mcpg)\n[![MCPg MCP server](https://glama.ai/mcp/servers/devopam/MCPg/badges/score.svg)](https://glama.ai/mcp/servers/devopam/MCPg)\n[![AllMCPs Verified](https://allmcps.com/api/badge/devopam-mcpg)](https://allmcps.com/mcp/devopam-mcpg?verify=63d9d537-be6b-4e07-a720-77dd0c41411b)\n[![MCPVault: claimed](https://mcpvault.io/badge/mcpg.svg?theme=dark)](https://mcpvault.io/servers/mcpg/health?utm_source=external_badge&utm_medium=referral&utm_campaign=mcp_health_report)\n\n> **Try it live:** point an MCP client — or the [MCP Inspector](https://github.com/modelcontextprotocol/inspector) — at the hosted, read-only demo endpoint `https://devopam-mcpg-demo.hf.space/mcp`. It serves read tools against throwaway demo data; for real use, run MCPg next to your own database (see [Quick start](#quick-start)).\n\n### 📍 Listed On\n\n- **[Official MCP Registry](https://registry.modelcontextprotocol.io/v0/servers?search=io.github.devopam/mcpg)**\n- **[mcp.so](https://mcp.so/server/mcpg---production-grade-postgresql-mcp-server/Devopam%20Mittra)**\n- **[mcpservers.org](https://mcpservers.org/servers/devopam/mcpg)**\n- **[Smithery](https://smithery.ai/servers/devopam/mcpg)**\n- **[Glama](https://glama.ai/mcp/servers/devopam/MCPg)**\n\n---\n\n| Aspect              | MCPg                              |\n|---------------------|-----------------------------------|\n| Safety              | Read-only default + AST validation |\n| Transport           | stdio + HTTP/SSE                  |\n| Install             | `pip install mcpg`                |\n| Postgres Versions   | 14–19                             |\n| Key Differentiator  | Production observability + multi-tenancy |\n\n## Why MCPg\n\n- **Safe by default.** Read-only access mode. Every user-supplied SQL\n  statement parses through a validated AST allowlist before execution.\n  Identifier interpolation flows through a strict\n  `[A-Za-z_][A-Za-z0-9_]*` regex — a design constraint that means\n  user input never reaches the database through string concatenation.\n  Capabilities like DDL, shell, and `LISTEN/NOTIFY` are off until you\n  opt in. Every tool publishes MCP `ToolAnnotations` (`readOnlyHint`,\n  `openWorldHint`) derived from those same gates, so clients can\n  auto-approve reads and gate writes without guessing.\n- **One server, broad surface.** Application data access (queries, search,\n  cursors, NL→SQL) *and* DBA-grade operations (health checks, index tuning,\n  EXPLAIN analysis, locks, vacuum, dumps, replicas, migrations) in a\n  single MCP server. Agents don't have to switch tools to switch tasks.\n- **PostgreSQL-native everything.** No ORM, no abstraction tax — uses\n  `psycopg3` directly, speaks every `pg_*` system view, integrates with\n  TimescaleDB, pgvector, PostGIS, Apache AGE, and `pg_stat_statements`\n  where they're available, and degrades gracefully when they aren't.\n- **Production-shaped, not demo-shaped.** Connection pooling, per-request\n  `SET ROLE` multi-tenancy, read-replica routing with degraded-host\n  detection, server-side cursors with dedicated connections,\n  rate-limiting, audit trail with regex redaction, PG TLS enforcement\n  on startup, OIDC JWT bearer auth, per-session statement / lock\n  timeouts.\n- **Observability built in.** Prometheus `/metrics` endpoint on the\n  HTTP transport surfaces `mcpg_tool_calls_total{tool,status}` +\n  `mcpg_tool_duration_seconds`. Every tool call records a structured\n  audit event with credential-redacted arguments.\n- **Test-driven, multi-version.** 2,500+ unit tests plus an integration\n  suite that runs against a real PostgreSQL container in CI — matrix\n  covers PG **14, 15, 16, 17, 18** on every push, plus PG **19 (beta)**\n  as an experimental (non-blocking) entry tracked under issue #120.\n\n---\n\n## Install\n\n### From PyPI (recommended)\n\n```bash\npip install mcpg\n# or, in an isolated venv exposed globally:\nuv tool install mcpg\n```\n\nVerify:\n\n```bash\nmcpg --version\n```\n\n### Docker\n\nPull the pre-built image from the GitHub Container Registry (published\non every tagged release — `:latest` tracks the newest, or pin a version\nlike `:0.6.5`):\n\n```bash\ndocker pull ghcr.io/devopam/mcpg:latest\ndocker run --rm --name mcpg -p 8000:8000 \\\n    -e MCPG_DATABASE_URL=postgresql://user:pass@host:5432/db \\\n    -e MCPG_ACCESS_MODE=read-only \\\n    ghcr.io/devopam/mcpg:latest\n```\n\nOn **Windows PowerShell** replace the trailing `\\` with a backtick `` ` ``\n(or put the command on one line); the [installation\nguide](docs/installation.md#option-2--docker) has ready-to-copy\nLinux/macOS, PowerShell, and Command Prompt blocks.\n\nOr build it yourself from source:\n\n```bash\ndocker build -t mcpg https://github.com/devopam/MCPg.git\n```\n\nMulti-stage image: runtime stage drops the build toolchain, runs as\n`uid=10001 / gid=10001` with `nologin` shell, application files\nroot-owned and read-only to the runtime user.\n\n### From source (developers)\n\n```bash\ngit clone https://github.com/devopam/MCPg && cd MCPg\nuv sync\n```\n\n`uv sync` creates a venv with all runtime + dev dependencies and exposes\nthe `mcpg` console script.\n\nMore detail in the [Installation Guide](https://github.com/devopam/MCPg/blob/main/docs/installation.md).\n\n---\n\n## Quick start\n\n**One-click installs:**\n[![Add to Cursor](https://img.shields.io/badge/Add_to-Cursor-000000?logo=cursor)](https://cursor.com/install-mcp?name=mcpg&config=eyJjb21tYW5kIjoidXZ4IiwiYXJncyI6WyJtY3BnIl0sImVudiI6eyJNQ1BHX0RBVEFCQVNFX1VSTCI6InBvc3RncmVzcWw6Ly91c2VyOnBhc3NAbG9jYWxob3N0OjU0MzIvbXlkYiJ9fQ%3D%3D)\n[![Install in VS Code](https://img.shields.io/badge/Install_in-VS_Code-0098FF?logo=githubcopilot)](https://vscode.dev/redirect?url=vscode%3Amcp%2Finstall%3F%257B%2522name%2522%253A%2522mcpg%2522%252C%2522command%2522%253A%2522uvx%2522%252C%2522args%2522%253A%255B%2522mcpg%2522%255D%252C%2522env%2522%253A%257B%2522MCPG_DATABASE_URL%2522%253A%2522%2524%257Binput%253Adatabase_url%257D%2522%257D%252C%2522inputs%2522%253A%255B%257B%2522type%2522%253A%2522promptString%2522%252C%2522id%2522%253A%2522database_url%2522%252C%2522description%2522%253A%2522PostgreSQL%2520connection%2520URL%2520%2528postgresql%253A%252F%252Fuser%253Apass%2540host%253A5432%252Fdb%2529%2522%252C%2522password%2522%253Atrue%257D%255D%257D)\n[![Claude Desktop](https://img.shields.io/badge/Claude_Desktop-.mcpb-D97757?logo=claude)](https://github.com/devopam/MCPg/releases/latest)\n— setup for Windsurf, JetBrains, Zed, Cline, Antigravity, Qwen Code, Perplexity,\nChatGPT, Copilot Studio, Continue, and HTTP\nclients in the [integrations guide](docs/integrations.md).\n\n### One-click install in Claude Desktop (.mcpb)\n\nDownload `mcpg-<version>.mcpb` from the\n[latest release](https://github.com/devopam/MCPg/releases/latest) and\ndouble-click it (or drag it into Claude Desktop's Settings →\nExtensions). You'll be prompted for your PostgreSQL connection URL —\nstored in the OS keychain — and an access mode (defaults to\nread-only). That's the whole install: the bundle is ~2 kB and the\nhost resolves the pinned `mcpg` release from PyPI for your platform.\n\n### Or wire it up manually (stdio transport)\n\nDrop this into your `claude_desktop_config.json` (macOS:\n`~/Library/Application Support/Claude/claude_desktop_config.json`;\nWindows: `%APPDATA%\\Claude\\claude_desktop_config.json`):\n\n```json\n{\n  \"mcpServers\": {\n    \"mcpg\": {\n      \"command\": \"uvx\",\n      \"args\": [\"mcpg\"],\n      \"env\": {\n        \"MCPG_DATABASE_URL\": \"postgresql://user:pass@localhost:5432/mydb\"\n      }\n    }\n  }\n}\n```\n\nRestart Claude Desktop. The MCPg toolset is now available to the model.\nYou can ask Claude things like:\n\n> *\"What schemas exist in this database? For each one, summarise the\n> three biggest tables.\"*\n\n> *\"Why is this query slow?\n> `SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC`\"*\n\n### No interesting data yet? Seed the demo dataset\n\n```bash\nMCPG_DATABASE_URL=postgresql://... mcpg --demo\n```\n\nOne command seeds a small, curated e-commerce dataset (3,000 orders,\n900 product reviews, deliberately planted flaws) into an `mcpg_demo`\nschema — engineered so the index advisor, query-plan analysis,\nfull-text search, PII audit, and graph projection all have something\nreal to find on your first try. See the\n[guided tour](docs/demo.md) for a captured walkthrough, and remove it\nany time with `mcpg --demo-drop`.\n\n### Run as an HTTP server (for IDE integrations, web apps, etc.)\n\n```bash\nMCPG_DATABASE_URL=postgresql://user:pass@localhost:5432/mydb \\\nMCPG_TRANSPORT=streamable-http \\\nMCPG_HTTP_PORT=8000 \\\nmcpg\n```\n\nThen point any MCP-aware client at `http://localhost:8000/mcp` (or\n`/sse` for the SSE transport). The HTTP transport refuses to start\nunless it's authenticated — set `MCPG_HTTP_AUTH_TOKEN=...` for a static\nbearer, or `MCPG_AUTH_MODE=oidc` for full JWT validation against an\nOIDC issuer. To deliberately run without auth (not recommended), set\n`MCPG_HTTP_ALLOW_UNAUTHENTICATED=true`.\n\n---\n\n## Configuration\n\nMCPg is configured **entirely through environment variables** — no\nconfig file, no flags (the CLI's `--version` / `--demo` / `--demo-drop`\nare one-shot commands, not configuration). The only required one is\n`MCPG_DATABASE_URL`; everything else has a safe default.\n\n### Common scenarios\n\n| Scenario | Set |\n|---|---|\n| Local exploration, read-only | `MCPG_DATABASE_URL` |\n| Read-write app data access | `MCPG_ACCESS_MODE=restricted` |\n| DBA toolkit (DDL, vacuum, etc.) | `MCPG_ACCESS_MODE=unrestricted` + `MCPG_ALLOW_DDL=true` |\n| HTTP transport with bearer auth | `MCPG_TRANSPORT=streamable-http` + `MCPG_HTTP_AUTH_TOKEN=…` |\n| Multi-tenant SaaS | `MCPG_DEFAULT_ROLE=tenant_a` + `MCPG_ALLOWED_ROLES=tenant_a,tenant_b,…` |\n| Read-replica fan-out | `MCPG_REPLICA_URLS=postgresql://…?sslmode=require,postgresql://…?sslmode=require` |\n| NL→SQL — single provider | Set any one vendor key (`ANTHROPIC_API_KEY`, `OPENAI_API_KEY`, `GEMINI_API_KEY`, `XAI_API_KEY`, `GROQ_API_KEY`, `HF_TOKEN`, … — 22 built-in providers). MCPg auto-picks the default. |\n| NL→SQL — multiple providers, caller picks | Set all vendor keys you want active. Each call to `translate_nl_to_sql` can pass `provider=\"…\"` (any configured built-in or custom). |\n\n### Full reference\n\n#### Core\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_DATABASE_URL` | **required** | Primary PostgreSQL DSN. Supports URI (`postgresql://…`) and keyword (`host=… user=…`) forms. Remote hosts require `sslmode=require` (or stronger). |\n| `MCPG_ACCESS_MODE` | `read-only` | `read-only` \\| `restricted` (allows write tools) \\| `unrestricted` (also unlocks DBA tools when paired with the gate vars). |\n| `MCPG_TRANSPORT` | `stdio` | `stdio` (default, for Claude Desktop) \\| `streamable-http` \\| `sse`. |\n| `MCPG_LOG_LEVEL` | `INFO` | `DEBUG` \\| `INFO` \\| `WARNING` \\| `ERROR` \\| `CRITICAL`. |\n| `MCPG_HTTP_HOST` | `127.0.0.1` | Bind address for HTTP transports. Set to `0.0.0.0` inside containers. |\n| `MCPG_HTTP_PORT` | `8000` | Listen port for HTTP transports (1–65535). |\n\n#### Capability gates (opt-in for higher-blast-radius tools)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_ALLOW_DDL` | `false` | Expose DDL tools (`run_ddl`, `create_graph`, `drop_graph`, hypertable tools, migration tools). Requires `MCPG_ACCESS_MODE=unrestricted`. |\n| `MCPG_ALLOW_SHELL` | `false` | Expose subprocess-backed tools (`dump_database`, `restore_database`, `run_pg_binary`). Required PG client binaries must be on `PATH`. |\n| `MCPG_ALLOW_LISTEN` | `false` | Expose `LISTEN/NOTIFY` tools (`subscribe_channel`, `poll_notifications`, `unsubscribe_channel`, `list_notification_subscriptions`). |\n\n#### Authentication (HTTP transports only)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_AUTH_MODE` | `static` | `static` (compare bearer to `MCPG_HTTP_AUTH_TOKEN`) \\| `oidc` (full JWT validation). |\n| `MCPG_HTTP_AUTH_TOKEN` | — | Required bearer token when `MCPG_AUTH_MODE=static`. Constant-time compare. The HTTP transport refuses to start (`ConfigError`) unless this, `MCPG_AUTH_MODE=oidc`, or `MCPG_HTTP_ALLOW_UNAUTHENTICATED=true` is set. |\n| `MCPG_HTTP_ALLOW_UNAUTHENTICATED` | `false` | Explicit opt-out of the HTTP transport's fail-closed auth check. Loudly logged on every startup when set; not recommended. |\n| `MCPG_OIDC_ISSUER` | — | OIDC issuer URL (required when `MCPG_AUTH_MODE=oidc`). |\n| `MCPG_OIDC_AUDIENCE` | — | Expected `aud` claim (required when `MCPG_AUTH_MODE=oidc`). |\n| `MCPG_OIDC_JWKS_URL` | discovered | Override JWKS endpoint (auto-discovered from issuer's `.well-known` otherwise). |\n| `MCPG_OIDC_ROLE_CLAIM` | — | JWT claim whose value becomes the per-request PG role (`SET LOCAL ROLE`). Composes with the tenancy driver. |\n\n#### HTTP hardening (HTTP transports only)\n\n| Variable | Default | Description |\n|---|---|---|\n| `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. |\n| `MCPG_HTTP_ALLOWED_ORIGINS` | — | Comma-separated CORS allowlist. Unset = no CORS middleware (no cross-origin headers emitted). |\n| `MCPG_HTTP_HSTS_MAX_AGE` | `63072000` | `Strict-Transport-Security` max-age (2 years, OWASP's current recommendation). `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. |\n| `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. |\n| `MCPG_HTTP_TRUSTED_HOSTS` | — | Comma-separated list of allowed `Host` header values (wildcards like `*.example.com` supported). Unset = no host-header validation (current behaviour). When set, requests with a non-matching Host get a `400` via Starlette's `TrustedHostMiddleware`. |\n\n#### Multi-tenancy (`SET ROLE`)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_DEFAULT_ROLE` | — | Static PG role applied to every query. Identifier-validated. |\n| `MCPG_ALLOWED_ROLES` | — | Comma-separated allowlist. When set, the `X-MCPG-Role` header / OIDC role claim must be in this list. |\n\n#### Read replicas\n\n| Variable | Default | Description |\n|---|---|---|\n| `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. |\n\n#### Multiple databases (read-only secondaries)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_SECONDARY_DATABASE_URLS` | — | Comma- or newline-separated `name=dsn` entries naming additional **read-only** databases this one server can serve (e.g. `analytics=postgresql://…?sslmode=require,reporting=postgresql://…?sslmode=require`). Read-capable tools accept an optional `database` argument selecting a secondary by name; omit it for the primary. Secondaries are read-only — **PostgreSQL-enforced** (every query runs in a `READ ONLY` transaction), so writes / DDL / shell / migrate always target the primary. Names must be simple identifiers (`[a-z0-9_]+`), unique, and not `primary` (the reserved id of `MCPG_DATABASE_URL`). Same TLS rules as the primary DSN. Call `list_databases` to discover the configured ids and their reachability. |\n\n#### Pool / timeouts / TLS\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_POOL_MIN_SIZE` | `1` | Minimum pool connections. |\n| `MCPG_POOL_MAX_SIZE` | `5` | Maximum pool connections. Must be ≥ `MCPG_POOL_MIN_SIZE`. |\n| `MCPG_STATEMENT_TIMEOUT_MS` | `30000` | Per-session `statement_timeout` set on connection checkout. Runaway queries self-terminate. |\n| `MCPG_LOCK_TIMEOUT_MS` | `5000` | Per-session `lock_timeout`. Hanging lock waits self-terminate. |\n| `MCPG_ENABLE_ANALYTICAL_QUERIES` | `true` | Expose `run_analytical_query` (long-running reads on an isolated pool). Set `false` to withdraw the tool. |\n| `MCPG_ANALYTICAL_TIMEOUT_MS` | `120000` | Default per-call budget for `run_analytical_query` (2 min). |\n| `MCPG_ANALYTICAL_MAX_TIMEOUT_MS` | `600000` | Hard ceiling for `run_analytical_query`; a per-call `timeout_ms` is clamped to this (10 min). Must be ≥ `MCPG_ANALYTICAL_TIMEOUT_MS`. |\n| `MCPG_ANALYTICAL_MAX_CONCURRENCY` | `2` | Size of the isolated analytical pool — max simultaneous `run_analytical_query` calls. |\n| `MCPG_ALLOW_INSECURE_TLS` | `false` | Bypass the startup TLS check that refuses remote DSNs without `sslmode=require` (or stronger). Loopback hosts are always exempt. |\n| `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. |\n\n#### Subprocess tools (`MCPG_ALLOW_SHELL=true` only)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_SHELL_TIMEOUT_SEC` | `60` | Max wall-clock for `pg_dump` / `pg_restore` / `psql` invocations. |\n| `MCPG_SHELL_MAX_OUTPUT_BYTES` | `67108864` | (64 MiB) Cap on captured stdout per subprocess call. |\n| `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. |\n| `MCPG_SUBPROCESS_CPU_SECONDS` | — | Per-child `RLIMIT_CPU` (seconds). POSIX only; unset = inherit. |\n| `MCPG_SUBPROCESS_MEMORY_MB` | — | Per-child `RLIMIT_AS` (MiB). POSIX only; unset = inherit. |\n\n#### LISTEN/NOTIFY (`MCPG_ALLOW_LISTEN=true` only)\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_LISTEN_QUEUE_MAX` | `1000` | Per-channel buffer; oldest notifications dropped on overflow. |\n\n#### Audit\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_AUDIT_PERSIST` | `false` | When true, every `run_write` / `run_ddl` call persists to a `mcpg_audit.events` table (auto-created idempotently). |\n| `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`). |\n| `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`. |\n| `MCPG_AUDIT_HMAC_KEY` | — | Secret key for the audit HMAC chain. Required when `MCPG_AUDIT_INTEGRITY=true`. Never appears in `repr`/logs. |\n\n#### Secrets backend\n\nBy default every secret is read straight from the environment. Set\n`MCPG_SECRETS_BACKEND=file` to instead load API keys / bearer token /\nHMAC key from a mounted file — a name in the file wins; anything absent\nfalls back to the env var, so partial files work.\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_SECRETS_BACKEND` | `env` | `env` (read every secret from the environment) \\| `file` (overlay a secrets file on top of the environment). |\n| `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`. |\n\n#### Rate limiting\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_RATE_LIMIT_ENABLED` | `true` | Token-bucket per-tool rate limiting. Set to `false` to restore the pre-breaking-change unlimited behavior. |\n| `MCPG_RATE_LIMIT_MAX_REQUESTS` | `60` | Global cap per window across all tools. |\n| `MCPG_RATE_LIMIT_WINDOW_SECONDS` | `60` | Window length for the global quota. |\n| `MCPG_RATE_LIMIT_HEAVY_MAX` | `5` | Cap for heavy tools (`run_write`, `run_ddl`, `dump_database`, etc.). |\n| `MCPG_RATE_LIMIT_HEAVY_WINDOW` | `60` | Window length for the heavy-tool quota. |\n\n#### Caching & Feature flags\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCPG_CACHE_ENABLED` | `true` | Enable or disable the adaptive cache layer. |\n| `MCPG_CACHE_TTL_SECONDS` | `300` | Default cache Time-To-Live in seconds. |\n| `MCPG_CACHE_MAXSIZE` | `1024` | Maximum LRU capacity bound for the memory cache. |\n| `MCPG_REDIS_URL` | — | Optional Redis backend connection string for external, multi-node caching. |\n| `MCPG_ENABLE_HEAVY_DIAGNOSTICS` | `true` | Toggle computationally heavy diagnostic, diagram, and advisor tools. |\n| `MCPG_ELICIT_CONFIRM_WRITES` | `false` | When true, every write/DDL/shell/listen/migrate-tier tool call (any tool whose `readOnlyHint` annotation isn't true) requires an accepted interactive confirmation (`ctx.elicit()`) before running. **Best-effort, not an enforcement boundary**: it only engages for clients that both pass a request `context` and declare the `elicitation` capability during `initialize` — a client that omits either silently bypasses the gate and the tool runs as normal. |\n\n#### Natural-language SQL\n\nMCPg auto-discovers every configured provider from the environment at\nstartup — set as many vendor keys as you have and each becomes callable.\n**Nineteen providers ship built in.** Three are first-party (Anthropic,\nOpenAI, Gemini); the other sixteen speak the OpenAI-compatible API with\nvendor-preset endpoints: **DeepSeek, Qwen, OpenRouter, Perplexity, xAI\n(Grok), Groq, Mistral, Together, Fireworks, DeepInfra, Cerebras, Nebius,\nHugging Face, GitHub Models, SambaNova, and Moonshot (Kimi)**. Every\nbuilt-in is plug-and-play — set the vendor's conventional API-key env\nvar and it's auto-discovered — and **any *other* OpenAI-compatible\nvendor or local model server (Ollama, vLLM, LM Studio) is still\npluggable through configuration alone** via `MCPG_NL2SQL_CUSTOM_PROVIDERS`.\nThe whole built-in list is one declarative registry in `nl2sql.py`, so\nadding a vendor or refreshing a retired default model is a one-line data\nchange.\n\nWhen `MCPG_NL2SQL_PROVIDER` is unset, MCPg auto-picks the default in\nregistry order — **anthropic → openai → gemini** stay first so existing\ndeployments are unaffected. `translate_nl_to_sql` accepts an optional\n`provider=\"…\"` argument to route per call; `get_server_info` reports\nwhich are configured.\n\n| Variable | Default | Description |\n|---|---|---|\n| `<VENDOR>_API_KEY` | — | Setting a vendor's conventional key enables that provider. Standard slugs: `ANTHROPIC_API_KEY`, `OPENAI_API_KEY`, `DEEPSEEK_API_KEY`, `OPENROUTER_API_KEY`, `PERPLEXITY_API_KEY`, `XAI_API_KEY`, `GROQ_API_KEY`, `MISTRAL_API_KEY`, `TOGETHER_API_KEY`, `FIREWORKS_API_KEY`, `CEREBRAS_API_KEY`, `NEBIUS_API_KEY`, `SAMBANOVA_API_KEY`, `MOONSHOT_API_KEY`. |\n| *(keys that deviate)* | — | A few vendors don't follow `<VENDOR>_API_KEY`: **Gemini** → `GEMINI_API_KEY` or `GOOGLE_API_KEY`; **Qwen** → `DASHSCOPE_API_KEY` or `QWEN_API_KEY`; **Hugging Face** → `HF_TOKEN`; **GitHub Models** → `GITHUB_TOKEN`; **DeepInfra** → `DEEPINFRA_TOKEN`. |\n| `MCPG_NL2SQL_PROVIDER` | auto-picked | Any built-in slug (listed above) or a custom name. Pins the default provider used when the tool is called without `provider=`. Unset + any vendor key present → MCPg auto-picks in registry order. |\n| `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. |\n| `MCPG_NL2SQL_MODEL` | provider default | Override the default model (e.g. `claude-sonnet-4-6`, `gpt-4o-mini`, `grok-3-mini`). Applies only to the default provider. |\n| `MCPG_NL2SQL_BASE_URL` | — | Endpoint override for the default provider (private gateways / regional endpoints). |\n| `MCPG_NL2SQL_CUSTOM_PROVIDERS` | — | **Bring your own provider — no code change.** Comma/newline-separated `name=base_url\\|model` entries declaring *extra* OpenAI-compatible providers beyond the built-ins (local Ollama / vLLM / LM Studio, or any niche vendor). Key from `<NAME>_API_KEY` by convention, or append `\\|KEY_ENV_VAR` for ones that deviate; keyless allowed for loopback endpoints. Each name becomes callable via `provider=`. |\n| `MCPG_NL2SQL_MAX_TOKENS` | `2048` | Cap on generated tokens (hard limit: 16384). |\n\n---\n\n## Usage examples\n\nThe MCP tools are invoked by the agent (Claude, Cursor, Continue, …)\nin response to your natural-language instructions. A handful of\nillustrative round-trips:\n\n### Inspect the schema\n\n> **You:** What tables live in the `public` schema, and which ones are\n> the biggest by row count?\n\n> **Agent (using `list_tables` + `summarize_table` × N):**\n> Six tables: `customers` (1.2M rows), `orders` (4.7M), `line_items`\n> (18.3M), `products` (340), `addresses` (1.4M), `audit_log`\n> (45.8M — the largest, accumulates without retention).\n\n### Diagnose a slow query\n\n> **You:** Why does\n> `SELECT * FROM orders o JOIN line_items l ON o.id = l.order_id WHERE o.created_at > now() - interval '7 days'`\n> take 8 seconds?\n\n> **Agent (using `why_is_this_slow` + `recommend_indexes`):**\n> `EXPLAIN ANALYZE` shows a sequential scan over `orders` (4.7M rows)\n> filtered by `created_at`. There's no index on `orders.created_at`.\n> Recommended:\n> `CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at DESC);`\n> Expected improvement: ~95% reduction (index scan touches ~33K rows\n> instead of full table). Run `validate_migration` on it before applying\n> to confirm there's no locking surprise.\n\n### Translate plain English to SQL\n\n> **You:** Find customers who placed an order this month but never\n> in the previous three months — show me their email and current\n> lifetime spend.\n\n> **Agent (using `translate_nl_to_sql`):**\n> ```sql\n> SELECT c.email, SUM(l.price * l.quantity) AS lifetime_spend\n> FROM customers c\n> JOIN orders o ON o.customer_id = c.id\n> JOIN line_items l ON l.order_id = o.id\n> WHERE EXISTS (\n>   SELECT 1 FROM orders o2 WHERE o2.customer_id = c.id\n>     AND o2.created_at >= date_trunc('month', now()))\n>   AND NOT EXISTS (\n>   SELECT 1 FROM orders o3 WHERE o3.customer_id = c.id\n>     AND o3.created_at >= date_trunc('month', now()) - interval '3 months'\n>     AND o3.created_at <  date_trunc('month', now()))\n> GROUP BY c.email;\n> ```\n\n### Picture the schema\n\n> **You:** Draw me an ER diagram of the `public` schema.\n\n> **Agent (using `generate_schema_diagram`):** *Returns a Mermaid\n> diagram you can paste straight into GitHub / Notion / Obsidian.*\n\n### Audit the database\n\n> **You:** How healthy is this database right now?\n\n> **Agent (using `audit_database`):** Returns a graded report:\n> *Memory & I/O score 92 (GOOD), Transaction & Connection 78 (WARNING:\n> rollback rate 0.4%, look at app logs), Concurrency & Locks 60\n> (CRITICAL: 14 backends waiting), Cleanliness & Bloat 88 (GOOD), Slow\n> queries 70 (WARNING: top query template runs 5000×, mean 90 ms —\n> see `optimize_query`).*\n\n### Run a guarded write\n\n> **You:** Soft-delete every order older than 5 years.\n\n> **Agent (using `run_write` with `MCPG_AUDIT_PERSIST=true`):** Validates\n> the statement through the safe-SQL kernel, runs it inside a transaction,\n> returns affected row count, persists the call (sql + arguments —\n> with secrets regex-redacted — + status) to `mcpg_audit.events` for\n> after-the-fact review.\n\nFor dozens more recipes — multi-tenant routing, RLS testing, NL→SQL,\nhybrid vector + FTS search, Apache AGE Cypher, TimescaleDB, ORM schema\nexports, server-side cursors — see [`docs/cookbook.md`](https://github.com/devopam/MCPg/blob/main/docs/cookbook.md).\n\n---\n\n## What's in the box\n\nCompact category list. For the full, current tool reference see\n[`docs/tools.md`](https://github.com/devopam/MCPg/blob/main/docs/tools.md); for a guided walkthrough see\n[`docs/tour.md`](https://github.com/devopam/MCPg/blob/main/docs/tour.md).\n\n- **Catalog introspection** — schemas, tables, columns, indexes,\n  constraints, views, functions, triggers, sequences, partitions,\n  policies, roles, grants, enums, domains, composite types, FDWs,\n  publications, subscriptions, extensions, generated columns.\n- **Query intelligence** — `run_select`, `run_select_parallel`,\n  `explain_query`, `analyze_query_plan`, `why_is_this_slow`,\n  `recommend_indexes`, `analyze_workload`, `check_database_health`,\n  `detect_n_plus_one`, `audit_database`.\n- **Search** — `fuzzy_search` (trigram), `full_text_search`,\n  `vector_search`, `hybrid_search` (pgvector + FTS via RRF),\n  `geo_search` (PostGIS k-NN).\n- **Natural language → SQL** — `translate_nl_to_sql` (22 built-in\n  providers — Anthropic, OpenAI, Gemini, xAI, Groq, Mistral, Hugging\n  Face, … — plus any custom OpenAI-compatible endpoint; output passes\n  through the same safe-SQL kernel as hand-written queries).\n- **Visualisation** — `generate_schema_diagram` (ER),\n  `generate_fk_cascade_graph` (blast-radius of `ON DELETE CASCADE`),\n  `generate_graph_diagram` (Apache AGE property graphs).\n- **Structural diff & migrations** — `compare_schemas`,\n  `validate_migration`, staged `prepare_migration` /\n  `complete_migration` / `cancel_migration` workflow.\n- **Apache AGE graph + Cypher** — `list_graphs`, `describe_graph`,\n  `run_cypher`, `create_graph`, `drop_graph`, `generate_graph_diagram`.\n- **Composite + advisor tools** — `summarize_table`,\n  `find_unused_objects`, `find_sensitive_columns` (PII heuristic),\n  `lint_naming_conventions`, `test_rls_for_role`, `list_locks`,\n  `find_blocking_chains`, `read_pg_stat_io` (PG16+),\n  `generate_test_data`.\n- **Live ops & maintenance** — `list_active_queries`,\n  `verify_connection_encryption` (TLS status of the live link),\n  `run_maintenance` (VACUUM/ANALYZE), `prune_audit_events`\n  (audit retention), `cancel_query`, `terminate_backend`,\n  `run_write`, `run_ddl`, `enable_extension`.\n- **Data movement** — `export_query` / `export_table` (CSV/JSON),\n  `dump_database` / `restore_database`, `import_csv` / `import_json`\n  (COPY FROM STDIN), `copy_table_between_databases`.\n- **Server-side cursors** — `open_cursor`, `fetch_cursor`,\n  `close_cursor`, `list_cursors` for pageable reads over millions\n  of rows.\n- **TimescaleDB** — `list_hypertables`, `list_chunks`,\n  `create_hypertable`, `add_compression_policy`,\n  `add_retention_policy`.\n- **ORM schema exporters** — Prisma, Drizzle, SQLAlchemy, sqlc,\n  Diesel, jOOQ, Ent, Ecto.\n- **Event streams** — `subscribe_channel`, `poll_notifications`,\n  `unsubscribe_channel`, `list_notification_subscriptions` bridging\n  PostgreSQL `LISTEN/NOTIFY` into the MCP poll model.\n- **Observability** — Prometheus `/metrics` endpoint +\n  `get_metrics_exposition` tool for stdio; structured audit trail\n  with regex-based credential redaction.\n\n---\n\n## Documentation\n\n- [`docs/installation.md`](https://github.com/devopam/MCPg/blob/main/docs/installation.md) — install + configure\n- [`docs/tour.md`](https://github.com/devopam/MCPg/blob/main/docs/tour.md) — guided tool tour\n- [`docs/cookbook.md`](https://github.com/devopam/MCPg/blob/main/docs/cookbook.md) — practical agent recipes\n- [`docs/tools.md`](https://github.com/devopam/MCPg/blob/main/docs/tools.md) — complete tool reference\n- [`docs/architecture.md`](https://github.com/devopam/MCPg/blob/main/docs/architecture.md) — how the pieces fit together\n- [`docs/scaling.md`](https://github.com/devopam/MCPg/blob/main/docs/scaling.md) — pool sizing, replicas, performance\n- [`docs/security-hardening.md`](https://github.com/devopam/MCPg/blob/main/docs/security-hardening.md) — security feature roadmap\n- [`docs/release-process.md`](https://github.com/devopam/MCPg/blob/main/docs/release-process.md) — how releases ship to PyPI\n- [`docs/adr/`](https://github.com/devopam/MCPg/tree/main/docs/adr) — architecture decision records\n- Browse at **https://devopam.github.io/MCPg/**\n\n---\n\n## Security\n\n- Vulnerability reporting: see [`SECURITY.md`](https://github.com/devopam/MCPg/blob/main/SECURITY.md). 90-day\n  coordinated-disclosure window; reports to `devopam@gmail.com`.\n- Defence-in-depth: capability gates, SafeSQL kernel, identifier\n  allowlist, audit redaction, PG TLS enforcement at startup,\n  rate-limiting, OIDC JWT validation, per-session timeouts.\n- See [`docs/security-hardening.md`](https://github.com/devopam/MCPg/blob/main/docs/security-hardening.md) for\n  the living roadmap of shipped (✅) and queued (⬜) hardening items.\n\n## Privacy Policy\n\nMCPg is self-hosted: your database contents never leave your\ninfrastructure, and there is no telemetry or phone-home of any kind.\nThe one documented exception is the opt-in `translate_nl_to_sql` tool,\nwhich sends your question plus schema context (names, not row data) to\nthe LLM provider *you* configure. Full policy — data collection,\nusage, storage, third-party sharing, retention, and contact — is in\n[`PRIVACY.md`](https://github.com/devopam/MCPg/blob/main/PRIVACY.md).\n\n---\n\n## Release notes & changelog\n\nSee [`CHANGELOG.md`](https://github.com/devopam/MCPg/blob/main/CHANGELOG.md) for the full version history,\n[`docs/release-process.md`](https://github.com/devopam/MCPg/blob/main/docs/release-process.md) for how releases\nare cut, and the [GitHub Releases](https://github.com/devopam/MCPg/releases)\npage for downloadable artifacts.\n\n---\n\n## Contributing\n\nPull requests welcome — see [`CONTRIBUTING.md`](https://github.com/devopam/MCPg/blob/main/CONTRIBUTING.md) for\nthe dev-loop setup, test conventions, and the per-PR review\nchecklist.\n\n---\n\n## License\n\nMIT — see [`LICENSE`](https://github.com/devopam/MCPg/blob/main/LICENSE). The SQL-safety kernel\n(`src/mcpg/sql/`) is first-party, re-authored from the MIT-licensed\n`crystaldba/postgres-mcp`; see [`NOTICE`](https://github.com/devopam/MCPg/blob/main/NOTICE) for the lineage.\n\n### Wrapped extensions — licenses you should know about\n\nMCPg's source is MIT, but the PostgreSQL extensions it wraps each carry\ntheir own license. The wrappers themselves are at arm's length (SQL-level\ncalls, no static or dynamic linking into MCPg's Python process), so\nMCPg-the-project is not a derivative work of any of them. **Operators\ndeploying a service built on MCPg + a given extension take on whatever\nobligations that extension's license imposes** — same as installing the\nextension directly. The matrix below names the license per wrapped\nextension so you can make an informed choice.\n\n| Extension | License | Notes for operators |\n|---|---|---|\n| pgvector | PostgreSQL License (BSD-style) | Permissive; no special obligations. |\n| pg_partman | PostgreSQL License | Permissive. |\n| pg_cron | PostgreSQL License | Permissive. |\n| pg_turboquant | MIT | Permissive. |\n| pg_buffercache / pg_walinspect / pgstattuple | PostgreSQL contrib | Permissive. |\n| TimescaleDB | Apache 2.0 (community) + Timescale License (TSL, source-available) for some features | Mixed — see Timescale's docs for which features are TSL-gated. |\n| Apache AGE | Apache 2.0 | Permissive. |\n| pg_search (ParadeDB) | **AGPL-3.0** | **Operators running a network service that lets users interact with `pg_search` are subject to AGPL's network clause** — typically the obligation to offer the source of `pg_search` (and any modifications) to those users. MCPg's wrappers don't extend that obligation to MCPg itself; you take on the obligation when you deploy and \"convey\" the extension over a network. If your service redistribution model is incompatible with AGPL's network clause, pick a different BM25 implementation (the [BM25 plan](docs/plans/bm25-integration.md) lists alternatives). |\n\nThis matrix is a starting point — for the binding answer on your specific\ndeployment, consult the extension's upstream LICENSE file and (if it\nmatters legally) your own counsel.\n\n> **Disclaimer.** Best efforts have been made to bring MCPg to\n> production grade, but it remains an actively developed project and\n> may contain issues. See the License terms for indemnity details.\n\n<!-- mcp-name: io.github.devopam/mcpg -->\n",
  "bytes": 36912,
  "sha": "4ab4760831b28dd89fd94bd71be5b24647456c19d8c76301715c4d5b4536cd22",
  "repo_slug": "devopam/mcpg",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_devopam_mcpg_e2202130/readme"
}