MCP Server for Postgres: Safe Read-Only Setup Guide
Updated 2026-10-15
Letting an AI query your database is powerful and risky. This guide sets up a Postgres MCP server the safe way: a read-only database role, a read-only transaction, a statement timeout and a row limit.
A note on the old reference server: @modelcontextprotocol/server-postgres was moved to the archived servers repository in 2025 and is no longer maintained, so we build a small server you control (about 40 lines). Maintained community options exist too (for example Postgres MCP Pro); evaluate them with the same safety checklist below.
Prerequisites
- PostgreSQL you can create roles on
- Python 3.10+ and
uv(or pip) - An MCP client (Claude Desktop, Cursor, VS Code)
Steps
1. Create a read-only role
CREATE ROLE ai_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE appdb TO ai_reader;
GRANT USAGE ON SCHEMA public TO ai_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ai_reader;
ALTER ROLE ai_reader SET statement_timeout = '5s';Leave sensitive tables (users’ passwords, payment data) out, or expose them through views without those columns.
2. Install dependencies
uv init pg-mcp && cd pg-mcp
uv add "mcp[cli]" "psycopg[binary]"3. Write the server
# server.py
import os, json
import psycopg
from mcp.server.fastmcp import FastMCP
DSN = os.environ["DATABASE_URL"] # postgresql://ai_reader:...@host:5432/appdb
MAX_ROWS = 200
mcp = FastMCP("postgres-readonly")
def run(sql: str, params=None):
with psycopg.connect(DSN, autocommit=False) as conn:
conn.read_only = True # BEGIN READ ONLY: writes fail even if granted
with conn.cursor() as cur:
cur.execute(sql, params)
cols = [d.name for d in cur.description] if cur.description else []
rows = cur.fetchmany(MAX_ROWS)
conn.rollback()
return [dict(zip(cols, r)) for r in rows]
@mcp.tool()
def list_tables() -> str:
"""List tables in the public schema."""
rows = run("SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY 1")
return "\n".join(r["table_name"] for r in rows)
@mcp.tool()
def describe_table(table: str) -> str:
"""Show column names and types for a table."""
rows = run("""SELECT column_name, data_type, is_nullable FROM information_schema.columns
WHERE table_schema='public' AND table_name=%s ORDER BY ordinal_position""", (table,))
return json.dumps(rows, indent=1)
@mcp.tool()
def query(sql: str) -> str:
"""Run a read-only SQL SELECT. Results are capped at 200 rows."""
return json.dumps(run(sql), default=str, indent=1)
if __name__ == "__main__":
mcp.run()4. Register it in your client
{
"mcpServers": {
"postgres": {
"command": "uv",
"args": ["--directory", "/ABSOLUTE/PATH/pg-mcp", "run", "server.py"],
"env": { "DATABASE_URL": "postgresql://ai_reader:change-me@localhost:5432/appdb" }
}
}
}Use this in claude_desktop_config.json or .cursor/mcp.json, then restart the client.
5. Try it
Ask: “Which 5 customers placed the most orders last month?” The model calls list_tables, describe_table, then query.
Safety checklist
- Read-only role and read-only transaction (defense in depth)
statement_timeoutso a bad join can’t hammer production- Point it at a replica if you have one
- Never put a superuser connection string in an MCP config
Common errors
permission denied for table x: working as intended for ungranted tables. Grant SELECT if the agent should see it.
cannot execute INSERT in a read-only transaction: also working as intended.
connection refused from Claude Desktop but works in terminal: the env block is missing. Desktop apps don’t inherit your shell variables.
canceling statement due to statement timeout: the query ran longer than 5 s. Add indexes or ask the model for a narrower query.