MCPg Architecture

How MCPg is built. Describes the current shape; the running record of how it got there lives in ../CHANGELOG.md and the adr/ directory.

Overview

MCPg is a single-process, async (asyncio) MCP server. An MCP client connects via stdio or HTTP, calls tools, and gets typed results. Every call passes through the same layers:

flowchart TD
    client["MCP client<br/>(Claude Desktop · Cursor · …)"]
    client -->|"stdio · streamable-HTTP · SSE"| fastmcp["AuditedFastMCP<br/>rate-limit · audit · metrics"]
    fastmcp -->|"capability gate<br/>(mcpg.policy)"| wrapper["Tool wrapper<br/>(mcpg.tools)"]
    wrapper --> logic["Logic module<br/>query · health · search · migrations · …"]
    logic --> drivers["Driver stack (composable)<br/>SafeSqlDriver → parse + allowlist<br/>RoutedSqlDriver → replica fan-out<br/>TenantSqlDriver → SET LOCAL ROLE"]
    drivers --> pool["psycopg3 pool<br/>+ optional replica pools"]
    pool --> primary[("PostgreSQL<br/>primary")]
    pool -.->|"force_readonly"| replicas[("read replicas")]

The Mermaid diagram renders on GitHub; on the docs site it shows as source. In words: the tool wrapper translates the MCP call into a typed Python call and enforces the capability gate, the logic module builds and runs the SQL, and the composable driver stack validates / forces read-only / picks a pool / sets the tenant role before the psycopg3 pool reaches PostgreSQL.

Request lifecycle

The client invokes a tool. AuditedFastMCP.call_tool (a FastMCP[AppContext] subclass) wraps every invocation:
- Checks the rate limiter (mcpg.middleware.rate_limit) when MCPG_RATE_LIMIT_ENABLED=true.
- Records an audit event on completion (success or failure) with the tool name, redacted arguments, and outcome.
- Updates the Prometheus counter + histogram (mcpg_tool_calls_total{tool,status} / mcpg_tool_duration_seconds).
The tool wrapper in mcpg.tools pulls the request’s AppContext (settings + database + listen manager + cursor manager) from the server lifespan and obtains a SqlDriver.
The wrapper delegates to a logic module that builds and runs the SQL and maps rows to typed dataclasses.
The driver stack decides exactly which pool the SQL hits:
- SafeSqlDriver — agent-supplied SQL is parsed and allowlisted via mcpg.sql (the first-party kernel) before execution.
- RoutedSqlDriver — when MCPG_REPLICA_URLS is set, force_readonly=True queries round-robin across healthy replicas; writes always go to the primary.
- TenantSqlDriver — wraps a base driver to issue BEGIN ... SET LOCAL ROLE "<role>" ... <stmt> ... COMMIT when a static or per-request role is in play.
The result is mapped to a typed result class and returned through the tool wrapper. The audit hook records the outcome.

Module map (103 modules)

Every mcpg.* module and what it owns, alphabetical. The layered request path through these lives in the Overview diagram; this table is the exhaustive index. Regenerate with python tools/generate_doc_tables.py --modules.

mcpg.__main__ is the mcpg console entry point; handles the --version flag and falls through to run(load_settings()).

The SQL-safety kernel (first-party)

src/mcpg/sql/ is MCPg’s own SQL-safety kernel, split into policy and mechanism:

sql/allowlist.py — the permitted statement / pglast AST node / function / extension sets, as data (the single auditable policy surface).
sql/safety.py — SafeSqlDriver: the pglast parse + AST-walker
- read-only execute path. Reads policy from allowlist.py; the walker can’t widen it.
sql/driver.py — SqlDriver / DbConnPool / obfuscate_password (pool + execution + credential redaction; no policy).

It’s fully inside the coverage gate + mypy --strict + ruff + bandit. Adversarial + fuzz tests live in tests/unit/test_sql_kernel_*.py; the threat model and security sign-off are in reviews/devendor-sql-kernel-security-review.md. It was de-vendored from crystaldba/postgres-mcp (MIT) — see ADR-0007, which supersedes ADR-0001.

Access-mode & capability model

mcpg.policy maps each access mode to a set of capabilities:

Access mode	Capabilities granted
`read-only`	`READ`
`restricted`	`READ`, `WRITE`
`unrestricted`	`READ`, `WRITE`, `DDL`, `SHELL`, `LISTEN`, `MIGRATE`

restricted is the “safe read-write” tier — data writes (DML) but no schema changes, subprocess, LISTEN/NOTIFY, or migrations.

The higher-blast-radius capabilities that unrestricted grants (DDL, SHELL, LISTEN, MIGRATE) additionally require their per-feature opt-in gate — enforced where tools register, not in the policy table:

Env var	Capability
`MCPG_ALLOW_DDL=true`	`DDL` (and `MIGRATE`, which piggybacks on it)
`MCPG_ALLOW_SHELL=true`	`SHELL`
`MCPG_ALLOW_LISTEN=true`	`LISTEN`

register_tools consults the policy so a tool is only exposed to the MCP client when its required capability is permitted. There is no module-level mutable state — settings, the database, the listen manager, and the cursor manager all live in the server lifespan’s AppContext, passed to tools via Context.lifespan_context.

Transport & HTTP middleware stack

For MCPG_TRANSPORT=streamable-http or sse, mcpg.http_runtime constructs a Starlette app with this middleware stack (outermost first):

Bearer / OIDC authentication. Static (MCPG_HTTP_AUTH_TOKEN constant-time compare) or full JWT validation against an OIDC issuer’s JWKS. /metrics / /healthz / /readyz are exempt by design.
Per-request role propagation. Reads X-MCPG-Role (or the OIDC role claim when MCPG_OIDC_ROLE_CLAIM is set), validates against MCPG_ALLOWED_ROLES, and stashes the value in the current_role ContextVar that the TenantSqlDriver reads.
The MCP transport handler (FastMCP-provided).

Plus three first-party endpoints under the same auth-exempt rules:

GET /metrics — Prometheus text format
GET /healthz — liveness
GET /readyz — readiness (verifies a pool connection)

Security model (summary)

Read-only by default; every agent-supplied SQL statement is parsed and allowlist-checked before execution; writes are validated as a single statement of an expected kind; identifiers everywhere flow through a [A-Za-z_][A-Za-z0-9_]* regex; credentials are redacted from logs and audit trail; PG TLS is enforced on startup; HTTP transports require a bearer token or OIDC JWT; per-session statement_timeout and lock_timeout are set on each pool checkout. The full threat model is in security.md; the shipped-vs-queued roadmap is in security-hardening.md.

Graceful degradation for optional extensions

Tools that depend on an optional extension check for it at call time and return an available: false result instead of failing when it’s absent. Affected tools:

Tool	Required extension
`fuzzy_search`	`pg_trgm`
`analyze_workload`, `detect_n_plus_one`	`pg_stat_statements`
`vector_search`, `vector_range_search`, `hybrid_search`, `recommend_vector_`, `analyze_vector_`	`vector` (pgvector)
`geo_search`	`postgis`
`pg_cron.*`	`pg_cron`
`partman.*`	`pg_partman`
`list_hypertables`, `create_hypertable`, `add_compression_policy`, `add_retention_policy`, `list_chunks`	`timescaledb`
`list_graphs`, `describe_graph`, `run_cypher`, `create_graph`, `drop_graph`, `generate_graph_diagram`	`age` (Apache AGE)

describe_table and list_indexes surface pgvector / index-method details when present without requiring the extension otherwise.

Testing approach

MCPg is test-driven across three suites:

Suite	Scope
`tests/unit/`	Fake-driver tests with a 90% coverage gate — all first-party code, including the SQL-safety kernel (`test_sql_kernel_*.py`: the adversarial allowlist suite + a fuzz/robustness pass).
`tests/integration/`	Real PostgreSQL — requires `MCPG_TEST_DATABASE_URL`. CI runs the matrix against PostgreSQL 14, 15, 16, 17, 18 on every push (pgvector + PostGIS + AGE image), plus an experimental PG 19 lane (pgvector built from source against `postgres:19beta1`) and a WarehousePG (Greenplum-derived MPP) characterisation lane.
`tests/contract/`	Tool-surface snapshot (`tool_surface.snapshot.json`) + the doc-table drift-guard (`test_doc_tables.py`), so a new tool or module can’t ship undocumented.

The integration container is built from .github/ci-postgres.Dockerfile and includes pgvector, postgis, pg_trgm, pg_stat_statements, and Apache age.

Configuration & deployment

Configuration is entirely environment-variable driven — no config file, no flags beyond --version. The full env-var reference is in the README; the narrative is in installation.md.

MCPg ships as both a PyPI package (pip install mcpg) and a hardened multi-stage Docker image — the runtime stage drops the build toolchain, runs as uid=10001 / gid=10001 with nologin, and keeps application files root-owned and read-only.

Scaling characteristics, pool sizing, and observability guidance live in scaling.md.

MCPg Architecture

A production-grade PostgreSQL Model Context Protocol (MCP) server.

MCPg Architecture

Overview

Request lifecycle

Module map (103 modules)

The SQL-safety kernel (first-party)

Access-mode & capability model

Transport & HTTP middleware stack

Security model (summary)

Graceful degradation for optional extensions

Testing approach

Configuration & deployment

See also