MCPg User Guide

How to use MCPg once it’s installed. For getting it installed see installation.md; for the full per-tool parameters see tools.md; for short task-shaped recipes see cookbook.md.

This guide is the narrative walkthrough — read it top-to-bottom the first time, skim it as a reference after.

Contents

  1. What MCPg is
  2. Access modes & capability gates
  3. Connecting an MCP client
  4. Working with the tools
  5. Multi-tenancy
  6. Read-replica routing
  7. Natural-language SQL
  8. Server-side cursors
  9. Data movement
  10. Reactive workflows: LISTEN / NOTIFY
  11. Staged migrations
  12. Migration history
  13. ORM bridges
  14. Audit trail
  15. Observability
  16. Rate limiting
  17. Caching
  18. Feature Flags
  19. Security defaults
  20. Troubleshooting

What MCPg is

MCPg is an MCP server that exposes a PostgreSQL database to an AI agent through a fixed, audited set of 252 tools (read-only mode exposes ~185). The agent never gets a raw database connection — it can only call the tools MCPg registers, every call is validated, and every call is logged. MCPg runs as a single async process and ships as both a PyPI package (pip install mcpg) and a hardened Docker image.

Access modes & capability gates

The MCPG_ACCESS_MODE setting decides the default tool surface; additional capability gates unlock higher-blast-radius families within unrestricted mode.

Mode What’s exposed
read-only (default) Catalog introspection, querying, search, health, EXPLAIN — all read-only.
restricted The above plus data writes (DML): run_write, run_maintenance, cancel_query, terminate_backend. No schema changes (DDL), subprocess/shell, LISTEN/NOTIFY, or migrations — the “safe read-write” tier.
unrestricted Everything: the write tools above plus the gated DDL / shell / listen / migrate families below.

Within unrestricted, the gate vars decide which additional families come along:

Gate Unlocks
MCPG_ALLOW_DDL=true run_ddl, enable_extension, hypertable tools (create_hypertable, add_compression_policy, add_retention_policy), AGE graph DDL (create_graph, drop_graph), staged migrations (prepare_migration / complete_migration / cancel_migration / validate_migration / validate_migration_schema / list_pending_migrations).
MCPG_ALLOW_SHELL=true Subprocess tools — dump_database, restore_database, copy_table_between_databases. Requires the PostgreSQL client binaries (pg_dump / pg_restore / psql) on PATH.
MCPG_ALLOW_LISTEN=true subscribe_channel, poll_notifications, unsubscribe_channel, list_notification_subscriptions.

Read-only is the default because the typical agent workflow is inspect first, modify second. Each gate is a deliberate opt-in so an experimentation deployment can’t drop a table by accident.

Connecting an MCP client

stdio (Claude Desktop and other local clients)

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

(Use "command": "mcpg" with no args if you’ve done a pip install mcpg on the system Python.)

Streamable HTTP (Cursor, Continue, custom web clients, etc.)

export MCPG_DATABASE_URL=postgresql://user:pass@localhost:5432/mydb
export MCPG_TRANSPORT=streamable-http
export MCPG_HTTP_AUTH_TOKEN=<random_long_token>
mcpg

Then point the client at http://<host>:8000/mcp and have it send Authorization: Bearer <random_long_token>.

For production, prefer OIDC over a static token — set:

export MCPG_AUTH_MODE=oidc
export MCPG_OIDC_ISSUER=https://your-issuer/
export MCPG_OIDC_AUDIENCE=mcpg
# Optional — map a JWT claim to the per-request PG role:
export MCPG_OIDC_ROLE_CLAIM=pg_role

OIDC validates every request’s JWT against the issuer’s JWKS (asymmetric algorithms only — RS256/RS384/RS512 + ES256/ES384/ES512).

Working with the tools

The tools group into a few common workflows. Full discovery surface is in tour.md; long-form parameters in tools.md; task-shaped recipes in cookbook.md.

Explore a schema

list_schemas → list_tables → describe_table maps a database. list_indexes shows a table’s indexes and their access method (B-tree / GIN / HNSW / IVFFlat / GiST / BRIN). list_extensions and list_available_extensions show what extensions are installed or available; describe_table reports the dimension of a pgvector vector(N) column.

For a one-call snapshot use summarize_table(schema, table) — columns + PK + FKs + indexes + stats + sample rows, all in one round-trip. Excellent agent UX.

Query data

run_select(sql, max_rows=1000) runs a read-only SQL query — validated against the SafeSQL allowlist, executed under a forced read-only transaction, and capped with a truncated flag. run_select_parallel(statements) fans out concurrently with per-statement error isolation.

explain_query(sql) returns a query plan; analyze_query_plan(sql) summarises it (cost, node types, sequential scans); why_is_this_slow(sql) rolls EXPLAIN + active queries + locks + cache + index suggestions into one call.

fuzzy_search (trigram, needs pg_trgm), full_text_search (tsvector / tsquery, web-search syntax), vector_search (pgvector k-NN, choose <-> / <#> / <=> operator), vector_range_search (distance threshold), hybrid_search (vector + FTS via reciprocal-rank fusion), geo_search (PostGIS k-NN over a geography column).

When the ParadeDB pg_search extension is installed, pg_search_run adds BM25 keyword search (single or multi-column OR), pg_search_more_like_this does similar-document recall with nine optional pdb.more_like_this tuning knobs, and hybrid_bm25_vector_search fuses a BM25 leg with a pgvector leg via reciprocal-rank fusion. See tools.md for the full pg_search row and the BM-25 integration plan for design context.

For vector index sizing: recommend_vector_index (HNSW vs IVFFlat) and recommend_vector_quantization (vector → halfvec / bit storage advisor).

Diagnose and tune

Change data (restricted or unrestricted mode)

Visualise

Multi-tenancy

MCPg supports per-request PostgreSQL role switching via SET LOCAL ROLE so one process can safely serve many tenants from a single pool.

# Static default role applied to every query that doesn't override:
export MCPG_DEFAULT_ROLE=tenant_a

# Allowlist — when set, the X-MCPG-Role header / OIDC claim must
# be in the list (otherwise 403):
export MCPG_ALLOWED_ROLES=tenant_a,tenant_b,tenant_c

HTTP requests override the default by sending X-MCPG-Role: tenant_b. OIDC deployments override via MCPG_OIDC_ROLE_CLAIM — the named JWT claim’s value becomes the per-request role automatically.

Per-request role selection is honoured per message on the streamable-http / sse transports — every tool call resolves the role from its own request, so two tenants sharing one MCP session each run under their own role. (This is threaded through the SDK’s per-message request context; earlier releases up to 0.6.10 pinned the role to a session’s first request — fixed in 0.6.11.)

Role names are identifier-validated ([A-Za-z_][A-Za-z0-9_]*) so they’re safe to inline into SET LOCAL ROLE "<name>".

RLS policies keyed on current_user then isolate tenants correctly, and the audit trail records which role ran each call.

Read-replica routing

export MCPG_REPLICA_URLS=postgresql://u:p@replica-1/db?sslmode=require,postgresql://u:p@replica-2/db?sslmode=require

When MCPG_REPLICA_URLS is non-empty, every force_readonly=true query is round-robin-routed to a healthy replica; writes always go to the primary. Replica failures fall back to the primary once and mark the replica degraded for 30 s before re-trying.

Each replica has its own connection pool; per-request SET LOCAL ROLE composes across replicas (each replica’s pool gets its own TenantSqlDriver).

list_replicas() reports per-replica index, password-obfuscated DSN, degraded flag, last error, and seconds-until-retry. Routing decisions land in the Prometheus mcpg_tool_calls_total counter under the synthetic tool name __replica_route with statuses primary / primary_no_healthy / fallback / replica_<n>.

Natural-language SQL

translate_nl_to_sql(question, schema, provider=None, execute=false) asks an LLM provider to produce read-only SQL for the supplied natural-language question, given the named schema’s catalog. The generated SQL is passed through the same SafeSQL allowlist as any hand-written query before any optional execution.

Provider configuration

MCPg auto-discovers every configured provider from the environment at startup. Set as many of these as you want active — each becomes callable through the tool:

Provider Set in env Default model
anthropic ANTHROPIC_API_KEY recent Claude Sonnet
openai OPENAI_API_KEY gpt-4o-mini
gemini GEMINI_API_KEY (falls back to GOOGLE_API_KEY) recent Gemini Flash
deepseek DEEPSEEK_API_KEY deepseek-chat
qwen DASHSCOPE_API_KEY (falls back to QWEN_API_KEY) qwen-plus
openrouter OPENROUTER_API_KEY openai/gpt-4o-mini
perplexity PERPLEXITY_API_KEY sonar
xai XAI_API_KEY grok-3-mini
groq GROQ_API_KEY llama-3.1-8b-instant
mistral MISTRAL_API_KEY mistral-small-latest
together TOGETHER_API_KEY meta-llama/Llama-3.3-70B-Instruct-Turbo
fireworks FIREWORKS_API_KEY accounts/fireworks/models/llama-v3p1-8b-instruct
deepinfra DEEPINFRA_TOKEN meta-llama/Meta-Llama-3.1-8B-Instruct
cerebras CEREBRAS_API_KEY qwen-3-32b
nebius NEBIUS_API_KEY meta-llama/Meta-Llama-3.1-8B-Instruct
huggingface HF_TOKEN openai/gpt-oss-20b
github GITHUB_TOKEN (GitHub Models) openai/gpt-4o-mini
sambanova SAMBANOVA_API_KEY Meta-Llama-3.1-8B-Instruct
moonshot MOONSHOT_API_KEY (Kimi) kimi-k2.5
glm ZAI_API_KEY (falls back to ZHIPUAI_API_KEY) glm-4.5-flash
doubao ARK_API_KEY (Volcengine Ark; model must be activated in the console) doubao-seed-1-6-251015
sakana SAKANA_API_KEY (Fugu) fugu

All but the first three (anthropic / openai / gemini) are OpenAI-compatible vendors: MCPg drives them through one chat-completions client with vendor-preset endpoints. The whole list is a single declarative registry (_PROVIDERS in nl2sql.py), so adding a vendor or refreshing a default model as vendors retire them is a one-line data change. Base URLs and key env vars were verified against each vendor’s docs; the default_model column is the volatile one — override it in configuration with MCPG_NL2SQL_MODEL (applies to the default provider).

Bring your own provider (no code change)

Any other OpenAI-compatible vendor — or a local model server — is declared through configuration alone:

export MCPG_NL2SQL_CUSTOM_PROVIDERS="
  myvendor=https://api.myvendor.example/v1|big-model,
  privategw=https://llm.internal/v1|my-model|PRIVATEGW_TOKEN,
  ollama=http://localhost:11434/v1|llama3.1
"
export MYVENDOR_API_KEY=...    # <NAME>_API_KEY convention
export PRIVATEGW_TOKEN=...     # explicit third segment for a deviating key var
# ollama needs no key — it's a loopback endpoint

The names above must not collide with a built-in provider (the 19 in the table); a clash is rejected at startup. Use custom entries for vendors not already built in, or for a local model server.

Each entry is name=base_url|model with an optional |KEY_ENV_VAR third segment. Rules, stated plainly:

export ANTHROPIC_API_KEY=sk-ant-...      # configures anthropic
export OPENAI_API_KEY=sk-...             # configures openai too
# Both available; MCPg auto-picks anthropic as the default. Preference
# is the registry order — anthropic → openai → gemini stay first, then
# the OpenAI-compatible fleet (deepseek, qwen, …, xai, groq, …) — so
# existing setups keep their current default.

To pin a specific default, set MCPG_NL2SQL_PROVIDER explicitly:

export MCPG_NL2SQL_PROVIDER=openai       # default is now openai

MCPG_NL2SQL_API_KEY (when set) supplies the key for the configured default provider; it requires MCPG_NL2SQL_PROVIDER to also be set so MCPg knows which provider it’s for.

Per-call routing

Multiple providers configured? Any caller can route per-call by passing the optional provider= argument:

translate_nl_to_sql(question, schema, provider="anthropic")
translate_nl_to_sql(question, schema, provider="openai")
translate_nl_to_sql(question, schema, provider="gemini")
translate_nl_to_sql(question, schema)              # uses default

This is the recommended shape for one MCPg server, many MCP clients — set every vendor key on the host, run one MCPg over the HTTP transport, let each agent / IDE pick its preferred LLM per call. get_server_info() reports nl2sql_default_provider and nl2sql_available_providers so a caller can introspect.

Model + endpoint overrides

Override the default provider’s model with MCPG_NL2SQL_MODEL, or point at an OpenAI-compatible self-hosted endpoint (Ollama, vLLM, OpenRouter) with MCPG_NL2SQL_BASE_URL. Both apply only when the call targets the default provider — an explicit provider="openai" call when the default is anthropic falls back to OpenAI’s default model + endpoint, since the operator’s overrides are provider-specific. The per-call response budget is capped by MCPG_NL2SQL_MAX_TOKENS (default 2048, hard limit 16384).

Server-side cursors

For pageable reads over millions of rows.

open_cursor(sql) → { cursor_id: "mcpg_e3a91f", … }
fetch_cursor(cursor_id, batch_size=100) → { rows: [...], exhausted: false }
fetch_cursor(cursor_id, batch_size=100) → { rows: [...], exhausted: true }   # stop polling
close_cursor(cursor_id)                                                       # idempotent
list_cursors()

Each cursor holds a dedicated connection so long-lived cursors don’t starve the main pool. Cursors auto-close after a 5-minute idle timeout. The opening SQL passes through the same SafeSQL allowlist as run_select, so cursors can only read.

Data movement

Five tools, three blast-radius tiers:

Reactive workflows: LISTEN / NOTIFY

subscribe_channel(channel) opens a PG LISTEN on a dedicated connection and returns a subscription_id. poll_notifications(id, timeout_ms=0, max_messages=100) drains the per-sub bounded queue, optionally waiting up to timeout_ms. unsubscribe_channel(id) removes the subscription; list_notification_subscriptions() reports the active ones.

Queue size cap: MCPG_LISTEN_QUEUE_MAX (default 1000); overflow drops the oldest message and surfaces dropped_count on the next poll. Requires MCPG_ACCESS_MODE=unrestricted + MCPG_ALLOW_LISTEN=true.

Staged migrations

prepare_migration(name, target_schema, candidate_sql, ttl_minutes=60) clones the target schema’s structure into a shadow, applies the candidate SQL there, and runs compare_schemas so you can review the structural diff. validate_migration separately applies the candidate to a transient shadow seeded with sampled real data so failures the diff misses (NOT NULL on existing NULLs, CHECK violations, type narrowings) surface before apply.

validate_migration_schema(target_schema, reference_schema, candidate_sql) clones the target structure, applies the candidate SQL, and diffs the resulting shadow schema against a reference schema using compare_schemas to ensure the proposed migration exactly achieves the reference state.

complete_migration(id) applies to the target; cancel_migration(id) drops the shadow without applying. list_pending_migrations() shows what’s staged.

All staged-migration tools require unrestricted + MCPG_ALLOW_DDL=true.

Migration history

read_migration_history(schema=None) queries and summarizes applied migrations for popular frameworks (Alembic, Flyway, Diesel, Django, Prisma, Golang Migrate, Goose, Sequelize). Since it is read-only, it does not require DDL privileges or unrestricted mode.

ORM bridges

Eight read-only exporters generate a starting schema/model file from the live PG catalog:

All eight cover: base tables, columns, primary keys, single-column intra-schema FKs, and enums. Cross-schema and composite FKs are documented gaps.

Audit trail

Every tool call — success or failure — is logged to the mcpg.audit Python logger with the tool name, redacted arguments (see below), and outcome. Configure where that logger’s records are shipped via your deployment’s logging stack.

Log Format

By default, the server writes human-readable log messages. You can toggle structured JSON logging using the MCPG_LOG_FORMAT environment variable:

export MCPG_LOG_FORMAT=json  # text (default) or json

When set to json, all logging events from mcpg (including tool execution audit events) are serialized as a single-line JSON object containing standard keys (timestamp, level, logger, message). For audit events, the fields (tool, status, arguments, and optionally error) are merged directly into the top level of the JSON log record.

Persistence

With MCPG_AUDIT_PERSIST=true, every run_write and run_ddl is also persisted to a mcpg_audit.events table (auto-created idempotently on first write) with redacted arguments + result + status. Query the table via the list_audit_events tool.

Redaction

Argument values are masked when their key name matches a case-insensitive regex (matched via re.search, so password also covers PGPASSWORD, user_password, app.password). Default patterns:

password, passwd, secret, token, api[_-]?key, bearer,
authorization, database_url, dsn, conninfo

Extend the pattern list via MCPG_AUDIT_REDACT_KEYS (comma-separated regex fragments). Walks nested dicts / lists / tuples so credentials buried in result payloads are masked too.

String leaves are passed through the obfuscate_password helper so an embedded connection-string credential nested anywhere is scrubbed.

Integrity

To prevent tampering (unauthorized alterations, insertions, or deletions) of your persisted audit events, MCPg supports a signature chain:

When enabled, each event carries a signature computed over the deterministic payload and the preceding event’s signature. Verify the entire log using the verify_audit_chain tool, which sequentially checks each link and reports any tampering or deletions.

Observability

The HTTP transports expose:

For stdio deployments, the get_metrics_exposition() MCP tool returns the same Prometheus payload as a string the agent can hand to a scrape target.

Slow-call latency logging

To flag slow MCP-side tool calls, MCPg supports a latency threshold warning log:

export MCPG_SLOW_CALL_THRESHOLD_MS=1000  # Threshold in milliseconds (default: 1000)

OpenTelemetry tracing

MCPg can emit one OpenTelemetry span per call_tool so traces from the agent → MCPg → PostgreSQL hop can be stitched together in Jaeger / Tempo / Honeycomb / your APM of choice.

pip install 'mcpg[otel]'                   # adds the OTel SDK + exporters
export MCPG_OTEL_ENABLED=true
export MCPG_OTEL_SERVICE_NAME=mcpg-prod    # optional, default "mcpg"

Standard OTel env vars (OTEL_EXPORTER_OTLP_ENDPOINT, OTEL_RESOURCE_ATTRIBUTES, …) are honoured by the SDK as usual.

Each span carries mcp.tool.name, mcp.tool.argument_count, and the outcome status. Argument values are deliberately not attached — span exporters often ship to third-party SaaS, and attaching agent-supplied SQL or other args would turn the trace backend into a side channel for credentials or PII. Audit logging (local-only by default) is the place to look for full per-call payloads.

Rate limiting

export MCPG_RATE_LIMIT_ENABLED=true
export MCPG_RATE_LIMIT_MAX_REQUESTS=60       # global cap per window
export MCPG_RATE_LIMIT_WINDOW_SECONDS=60     # window length
export MCPG_RATE_LIMIT_HEAVY_MAX=5           # cap for heavy tools
export MCPG_RATE_LIMIT_HEAVY_WINDOW=60       # heavy-window length

Token-bucket per-tool rate limiting. “Heavy” tools include run_write, run_ddl, dump_database, restore_database, and similarly expensive operations. A rate-limited call returns an error immediately rather than queueing.

Caching

export MCPG_CACHE_ENABLED=true             # enable or disable caching (default: true)
export MCPG_CACHE_TTL_SECONDS=300          # default cache TTL in seconds (default: 300)
export MCPG_CACHE_MAXSIZE=1024             # maximum LRU cache entry capacity (default: 1024)
export MCPG_REDIS_URL=redis://localhost:6379/0  # optional Redis connection string for external caching

MCPg provides a high-performance caching layer to save context window tokens and prevent database connection pool saturation from duplicate schema reads:

Feature Flags

export MCPG_ENABLE_HEAVY_DIAGNOSTICS=true   # toggle computationally heavy diagnostics (default: true)

Enables operational gating for administrators over expensive diagnostic tools. When set to false, diagnostic, diagramming, and advisor tools (run_advisors, recommend_indexes, generate_schema_diagram, etc.) remain registered for client discovery but raise a friendly, administrator-disabled RuntimeError at call-time.

Security defaults

MCPg ships with defence-in-depth defaults:

See security.md for the full threat model and security-hardening.md for the living roadmap of shipped (✅) and queued (⬜) hardening items.

Troubleshooting