We Built an MCP Server for Postgres: Architecture Deep Dive

We've built MCP servers for half a dozen enterprise clients this year. The simplest one — a read-only Postgres query server — turned into the most instructive. This is the architecture breakdown, including the mistakes.

Why Postgres + MCP?

The use case is common: a client has years of business data in Postgres and wants their AI agents (internal Slack bots, CrewAI crews, Claude Desktop) to query it in plain English. The naive approach — give the agent raw SQL access — is a security disaster. MCP gives you a principled middle layer.

The goal: AI agents can ask business questions in natural language, get accurate answers from live data, with no SQL injection risk, no schema leakage, and row-level security respected.

Architecture overview

Three layers:

• Schema intelligence layer: on startup, the server introspects Postgres and builds a tool-per-table model with descriptions generated from column names and comments
• Query layer: natural language → structured tool call → parameterized SQL → results
• Security layer: read-only connection, query allowlist, row-level security passthrough, result size limits

Layer 1 — Schema-aware tool generation

Rather than exposing a generic "run_sql" tool (dangerous), we generate one tool per table on startup.

The server connects to Postgres, fetches the schema, and generates FastMCP tools dynamically. Each tool's description includes the table purpose, column names and types, and example queries. This means the LLM knows exactly what each tool does without seeing raw SQL.

For a "customers" table with columns id, email, plan, mrr, created_at — the generated tool description reads: "Query the customers table. Columns: id (integer, primary key), email (text), plan (text: free/pro/enterprise), mrr (numeric, monthly recurring revenue in USD), created_at (timestamp). Filter by plan, date range, or revenue threshold."

The LLM can now answer "how many enterprise customers signed up last month?" by calling the right tool with the right filters — without ever writing SQL.

Layer 2 — Parameterized query execution

All queries use parameterized statements. The tool accepts filter arguments (not raw SQL), constructs a safe query internally, and returns results as JSON.

We expose three filter types per tool: equality filters, range filters (for numeric and date columns), and text search. The tool validates all inputs against the schema before executing.

Layer 3 — Security

Four controls that are non-negotiable in production:

Read-only database user. The MCP server connects with a Postgres role that has SELECT only. No INSERT, UPDATE, DELETE, DROP. Even if the tool layer fails, the database refuses write operations.

Result size limit. All queries include LIMIT 1000 by default. The tool rejects requests for unlimited result sets. This prevents both data exfiltration and accidentally melting your database with an unindexed full-table scan.

Row-level security passthrough. If your Postgres tables have RLS policies, the MCP server connects as the requesting user's service account (not a superuser), so RLS is enforced automatically. We use JWT claims to map MCP session identity to Postgres roles.

Query timeout. Every query runs with statement_timeout set to 5 seconds. Slow queries fail fast.

What burned us

Three mistakes in our first production deployment:

Connection pool exhaustion. Each MCP tool call opened a new connection. With 10 concurrent agent sessions, we hit Postgres connection limits in minutes. Fix: use asyncpg with a connection pool (min 2, max 10), shared across all tool invocations.

Schema changes breaking tool descriptions. When the client added columns, the cached tool descriptions were stale. Fix: reload schema on SIGHUP and cache with a 5-minute TTL backed by a schema_version hash.

Overly broad tool descriptions causing wrong table selection. When two tables had similar names, the LLM picked the wrong one. Fix: add explicit "do NOT use this tool for X" clauses in tool descriptions when you have similar tables.

Production deployment

We run the server as a containerized FastAPI + FastMCP process behind an HTTPS nginx proxy. Authentication uses short-lived JWTs issued by the main application. The MCP server validates the JWT, extracts the user role, and sets the Postgres session role before each query.

Monitoring: we log every tool call with duration, rows returned, and the user session ID to a structured log stream. Grafana dashboards show query latency p95 and error rate per table.

Results

After two weeks in production: 340 queries from AI agents, 0 security incidents, average query latency 180ms (p95: 420ms). The client's support team now uses a Claude Desktop integration to answer customer data questions that previously required a SQL-literate analyst.

FAQ

Can an AI agent modify data through this MCP server? No. The database connection is read-only at the Postgres role level. Even if a bug in the tool layer attempted an INSERT, the database would reject it.

What about sensitive columns like passwords or PII? We exclude sensitive columns from the schema introspection at the configuration level. They never appear in tool descriptions and are never queryable.

Does this work with other databases? The pattern works with any database that supports parameterized queries and connection pooling. We have variants for MySQL, BigQuery, and Snowflake using the same architecture.

How do you handle schema migrations? Schema is reloaded on SIGHUP (sent by our deployment pipeline after migrations) and has a 5-minute cache TTL as a fallback.

What's the biggest risk? Prompt injection in user-controlled data. If attacker-controlled text is stored in the database and returned as a query result, a vulnerable agent might act on it. We sanitize all returned text through an allow-list of safe characters before it reaches the LLM context.