Connecting Claude to Postgres with an MCP server

Wiring Claude directly to a production database is a bad idea unless you put a strict boundary between the model and the wire protocol. An mcp server postgres deployment gives you that boundary: Claude sees a small set of typed tools, and Postgres enforces the actual access rules. This guide walks through standing up that bridge with real configs you can copy.

Step 1: Provision Postgres and a locked-down role

Never let an LLM connect as superuser. Create a dedicated role with read-only access to a schema, or narrower per-table grants if you want tighter scoping.

-- as superuser
CREATE ROLE claude_ro LOGIN PASSWORD 'rotate-me';
REVOKE ALL ON DATABASE app FROM claude_ro;
GRANT CONNECT ON DATABASE app TO claude_ro;
GRANT USAGE ON SCHEMA public TO claude_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO claude_ro;

Use a connection string with that role. Store it in a secret manager, not in source control.

export DATABASE_URL="postgres://claude_ro:rotate-me@db.internal:5432/app?sslmode=require"

If you need write access for specific flows, grant INSERT or UPDATE on individual tables only, and never expose DDL. Rotate the password on a schedule; the mcp server postgres process reads it at boot.

Step 2: Run the MCP server for Postgres

The reference implementation ships as a Node package and speaks the MCP stdio transport by default, which is what Claude Desktop and most local clients expect.

npx -y @modelcontextprotocol/server-postgres "$DATABASE_URL"

That single command boots an mcp server postgres bridge that advertises query and list_tables tools. The server parses the connection string from argv; pass it explicitly to avoid environment ambiguity.

For containerized deployments, wrap it in a minimal Dockerfile:

FROM node:20-alpine
RUN npm install -g @modelcontextprotocol/server-postgres
ENV DATABASE_URL=""
CMD ["sh","-c","npx -y @modelcontextprotocol/server-postgres \"$DATABASE_URL\""]

Keep the container off public networks. The MCP server holds database credentials; treat it like any other privileged sidecar. If you need remote access, put it behind an SSE proxy using the MCP SDK rather than exposing the raw port.

Step 3: Register the server with your Claude client

If you run Claude Desktop, edit claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "postgres://claude_ro:rotate-me@db.internal:5432/app"]
    }
  }
}

Restart the app. Claude now has a postgres tool namespace and will discover the available tools automatically.

When you call Claude through an API instead of the desktop app, the transport shifts but the protocol doesn’t. If you route model traffic through n4n.ai, point your OpenAI-compatible client at its endpoint and attach the same MCP stdio server on the client side; the mcp server postgres tools are forwarded as function schemas unchanged. The model gateway is oblivious to MCP—it just streams tool definitions and responses.

For a Python client using the Anthropic SDK plus the MCP client library:

import os
from mcp import ClientSession, StdioServerParameters
from anthropic import Anthropic

params = StdioServerParameters(
    command="npx",
    args=["-y", "@modelcontextprotocol/server-postgres", os.environ["DATABASE_URL"]]
)

async def run():
    async with ClientSession(params) as session:
        tools = await session.list_tools()
        anthropic = Anthropic()
        # Inject tools into Claude's message and handle tool_use blocks

The key point: the LLM never sees SQL unless you explicitly echo it back. It sees tool names and JSON params, which makes prompt injection surface smaller.

Step 4: Constrain what the tools can do

Out of the box, the Postgres MCP tool runs arbitrary SELECT statements. That is acceptable for internal analytics, but for customer-facing agents you want guardrails.

Option A: DB-side views. Expose only a view that joins and masks columns.

CREATE VIEW public.orders_summary AS
SELECT id, status, created_at, amount
FROM public.orders
WHERE created_at > now() - interval '30 days';
GRANT SELECT ON public.orders_summary TO claude_ro;

Then revoke access to the base table from claude_ro. The mcp server postgres tool can only query objects the role can see.

Option B: Statement timeout. Set statement_timeout for the role so a bad query can’t sink the DB.

ALTER ROLE claude_ro SET statement_timeout = '5s';

Option C: Row limits in the view. Add LIMIT 1000 to the view definition to cap result sets.

Start with views and timeouts. A query-rewriting proxy is overkill for most teams.

Step 5: Drive a real query through Claude

Open a session and ask a natural question:

“How many orders were created yesterday and what was the total amount?”

Claude should call the query tool with something like:

{
  "sql": "SELECT count(*) AS cnt, sum(amount) AS total FROM public.orders_summary WHERE created_at::date = current_date - 1"
}

Verify the tool returned rows, not an error. In Claude Desktop you’ll see the tool invocation in the transcript. Via API, inspect the tool_use block in the response:

{
  "type": "tool_use",
  "name": "query",
  "input": { "sql": "SELECT count(*) AS cnt, sum(amount) AS total FROM public.orders_summary WHERE created_at::date = current_date - 1" }
}

If the model hallucinates a table name, Postgres throws an undefined_table error and the MCP server returns it as a tool error. Claude can then self-correct. That feedback loop is the main reason to use an mcp server postgres setup instead of gluing raw SQL into prompts.

Step 6: Verify success and operationalize

Success means three things: Claude calls the tool, Postgres executes under the restricted role, and you can trace it.

Check active connections:

SELECT usename, state, query
FROM pg_stat_activity
WHERE usename = 'claude_ro';

You should see idle in transaction or active rows with the exact query Claude issued.

Enable query logging for that role temporarily:

ALTER ROLE claude_ro SET log_statement = 'all';

Watch Postgres logs. Confirm no DDL, no writes, and that statement_timeout fires if you send a deliberate SELECT pg_sleep(10).

For CI, script a smoke test:

import os, asyncio
from mcp import ClientSession, StdioServerParameters

async def smoke():
    params = StdioServerParameters(
        command="npx",
        args=["-y","@modelcontextprotocol/server-postgres", os.environ["DATABASE_URL"]]
    )
    async with ClientSession(params) as s:
        tools = await s.list_tools()
        assert any(t.name == "query" for t in tools)
        res = await s.call_tool("query", {"sql": "SELECT 1 AS ok"})
        assert res.rows[0]["ok"] == 1
asyncio.run(smoke())

If that passes, your mcp server postgres link is live.

Why MCP instead of raw function calls?

You can hand-write a Python function that runs SQL and expose it to Claude as a function schema. The difference is discovery and isolation. MCP standardizes how a tool server announces its capabilities and how errors map back. When you later swap Claude for another model, or Postgres for MySQL, the client code barely changes. The mcp server postgres adapter is a known quantity; your prompt logic stays put.

Pitfalls we’ve hit in production

Connection pooling: the Node MCP server opens a new client per call unless you wrap it in pgbouncer. Under bursty agent traffic, set max_connections accordingly.

Credential rotation: the server reads DATABASE_URL at boot. Rotate by rolling the container, not by editing env on a live process.

Schema drift: if you rename a column, the view breaks. Add a migration check that runs the smoke test above.

Claude will occasionally try SELECT * on a large table. The statement_timeout and a row limit in the view save you.

Closing note on architecture

The mcp server postgres pattern decouples model capabilities from database internals. You can swap Claude for another model, or Postgres for another engine with a different MCP adapter, without rewriting prompt logic. That separation is why MCP is worth the small upfront cost.