postgres-mcp

A generic, reusable MCP server for read-only querying of any Postgres database via Claude (or any MCP-compatible client). It has no knowledge of any particular schema — list_tables, describe_table, and run_sql work purely off information_schema and generic jsonb introspection, so it can be pointed at any Postgres database without code changes.

Security model

Two independent layers, defense in depth:

1. DB-level: the server connects as a role that only has SELECT granted — it cannot write even if a query tried to. Create this role with setup_reader_role.sql.
2. App-level: run_sql() rejects anything that isn't a plain SELECT (or WITH ... SELECT), enforces a 200-row cap, and sets a 5-second statement timeout — all as a fast-fail check before even hitting the database.

Setup

1. Create a read-only role in your target database:
   • Open setup_reader_role.sql, replace the password placeholder and <your_db_name> with real values, then run it once against your database (e.g. via psql or your DB provider's SQL console).
2. Set your connection string in a .env file in this directory:

   DATABASE_URL=postgresql://mcp_reader:yourpassword@host:5432/yourdb
   ANTHROPIC_API_KEY=sk-ant-...
   

   (ANTHROPIC_API_KEY is only needed for chat.py, not for running the MCP server itself.)
3. Install dependencies:

   pip install -r requirements.txt
   

Local testing

Test the query layer directly first, without any MCP client in the loop:

python chat.py "what tables do I have?"

Then test it as an actual MCP server with the Inspector:

fastmcp dev inspector mcp_server.py

If the Inspector's Node toolchain gives you trouble (this has happened before), fall back to running the server over HTTP directly and hitting it with the FastMCP Python client:

fastmcp run mcp_server.py --transport http --port 8000

from fastmcp import Client
import asyncio

async def main():
    async with Client("http://localhost:8000/mcp") as client:
        print(await client.call_tool("list_tables", {}))

asyncio.run(main())

Deployment (Prefect Horizon)

FastMCP's hosted deployment platform is currently called Prefect Horizon (it was previously "FastMCP Cloud" — this has rebranded before, so double-check the current name/URL at gofastmcp.com/deployment before following these steps, in case it's changed again).

1. Push this repo to GitHub — a real remote must exist first (see the git commands at the bottom of this README if you haven't already).
2. Go to the current platform's site (as of writing, horizon.prefect.io) and sign in with GitHub.
3. Connect this repository.
4. Configure the deployment:
   • Entrypoint: mcp_server.py:mcp — the :mcp part is the variable name the server object is assigned to in the file (see the mcp = FastMCP(...) line in mcp_server.py). If you ever rename that variable or move the file, this entrypoint string must be updated to match exactly.
   • Authentication: turn this ON. Interactive MCP clients like claude.ai and Claude Desktop require real OAuth discovery endpoints to connect — a server without authentication enabled will not work with those clients, even if it works fine when you test it directly with a raw API call or the FastMCP Python client.
   • Environment variables: add DATABASE_URL in the platform's own dashboard. This is separate from, and does not read, this project's local .env file — the value must be entered directly in the dashboard for the deployed server to have DB access.
5. Deploy, and copy the resulting server URL. It'll look something like https://<your-server-name>.fastmcp.app/mcp (the exact domain may vary — use whatever the platform actually shows you).
6. Before connecting it anywhere else, test it with the platform's own built-in Inspector/testing tool. Call list_tables there first — it needs no arguments, so it's the fastest way to confirm the deployed server can actually reach your database.

Connecting to claude.ai

1. Go to claude.ai → Settings → Connectors → Add custom connector.
2. Paste the deployed server URL from the deployment step above.
3. Complete the OAuth prompt it walks you through.
4. Start a new chat (not one that predates the connector being added) and enable the connector in that chat.
5. Test it with a simple question like "what tables do I have?"
6. If you add new tools later and they don't show up, try "Refresh tools" in the connector's settings before assuming something's broken — this is a known caching behavior, not a bug.

Limits

This is genuinely schema-agnostic, but not limitation-free:

• Postgres-specific. It uses jsonb_object_keys() and Postgres catalog syntax (information_schema, etc.). Pointing it at MySQL or SQLite would need real code changes to db_tools.py, not just a new connection string.
• Schema-agnostic ≠ zero setup per database. Each new target database still needs its own read-only role created (setup_reader_role.sql) and its own deployment (or at minimum its own DATABASE_URL) pointed at it. This isn't a single server that transparently serves multiple databases.

Git setup

If you're starting from this code without git history yet:

git init
git add .
git commit -m "Initial commit: generic read-only Postgres MCP server"
git branch -M main
git remote add origin <your-repo-url>
git push -u origin main