Postgres MCP Server

An MCP server that lets a language model explore a PostgreSQL database and run read-only queries against it. The model can read the schema and SELECT from it; it cannot modify anything.

Four tools:

Tool	Returns
list_schemas()	schemas this server is allowed to inspect
list_tables(schema)	tables, views, materialized views and foreign tables
describe_table(schema, table)	columns, primary key and foreign keys, in one call
run_query(query)	rows from a single read-only SELECT

How it stays read-only

Three independent layers, in decreasing order of authority:

1. The database role. mcp_ro is granted SELECT and nothing else. A write that reached Postgres would be refused regardless of what the code does.
2. The transaction. Every connection is opened with default_transaction_read_only=on, set as a libpq connect option so no code path can forget it.
3. The SQL guard. services/guard.py parses the query with sqlglot and rejects anything that is not a single SELECT — including writes hidden inside CTEs, SELECT ... INTO, and FOR UPDATE.

The guard exists for clear error messages, not for safety. Layers 1 and 2 are the boundary.

Requirements

• Python 3.14+
• PostgreSQL 14+
• uv
• An Azure app registration, if you want the HTTP transport with authentication

Setup

1. Install

uv sync

2. Create the read-only database role

Connect as a superuser and run, substituting your database name:

CREATE ROLE mcp_ro LOGIN PASSWORD 'change-me';

GRANT CONNECT ON DATABASE your_db TO mcp_ro;
GRANT USAGE ON SCHEMA public TO mcp_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_ro;

-- so tables created later are readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_ro;

Grant USAGE but never CREATE on the schema. Verify the role really is read-only before going further:

psql "postgresql://mcp_ro:change-me@localhost:5432/your_db" \
  -c "DELETE FROM some_table;"
# expected: ERROR: permission denied for table some_table

If that succeeds, stop and fix the grants. Everything else assumes it fails.

3. Configure

cp .env.example .env

.env uses two prefixes. DB_* is what the database layer needs, MCP_* is everything else:

Key	Default	Notes
DB_USER	mcp_ro
DB_PASSWORD	—	required
DB_HOST	localhost
DB_PORT	5432
DB_NAME	playground
DB_STATEMENT_TIMEOUT_MS	5000	server-side query timeout
MCP_MAX_ROWS	100	cap on rows returned by run_query
MCP_ALLOWED_SCHEMAS	["public"]	JSON array; a bare string is rejected
MCP_LOG_LEVEL	INFO	applies to mcp_server loggers only
MCP_AZURE_CLIENT_ID	—	required, from the app registration
MCP_AZURE_CLIENT_SECRET	—	required
MCP_AZURE_TENANT_ID	—	required
MCP_AZURE_SCOPE	mcp-access	must match the scope you exposed
MCP_BASE_URL	http://localhost:8000	see the warning below

.env is gitignored. .env.example is the committed template.

Running

stdio — local development

uv run mcp-server
# or: uv run python -m mcp_server

The client launches the server itself, so there is no process to keep alive and no port. There is no authentication on this path — the OS process boundary is the authorization, exactly as with any other local CLI tool.

Register it:

claude mcp add db-explorer -- \
  /full/path/to/uv run --directory /full/path/to/mcp-server python -m mcp_server

Use the absolute path to uv. A stdio server is spawned with a minimal environment, and ~/.local/bin is usually not on its PATH.

HTTP — authenticated

uv run uvicorn mcp_server.app:app --reload --port 8000

You must keep this running; unlike stdio, nothing starts it for you.

claude mcp add --transport http db-explorer http://localhost:8000/
claude mcp list      # prompts for the browser sign-in

Endpoints:

• / — the MCP endpoint (401 without a token)
• /health — liveness probe, does not touch the database
• /.well-known/oauth-protected-resource — OAuth discovery

Azure setup

In App registrations → your app:

• Authentication → Redirect URIs: add http://localhost:8000/auth/callback, platform type Web. This is MCP_BASE_URL plus the provider's redirect_path, and Azure compares it as an exact string — a wrong port or a stray slash produces AADSTS50011 in the browser, not in your logs.
• Certificates & secrets: create a client secret → MCP_AZURE_CLIENT_SECRET.
• Expose an API: define the scope named in MCP_AZURE_SCOPE. Azure issues it as api://<client-id>/<scope>.

MCP_BASE_URL must be the host root. RFC 9728 requires the OAuth .well-known documents to live at the root of the host, and a mounted ASGI app never sees requests outside its own prefix. That is why app.py mounts the MCP app at / rather than /mcp. Setting MCP_BASE_URL to a subpath produces Dynamic Client Registration rejected (HTTP 404).

Verify auth is engaged before involving any client:

curl -s -o /dev/null -w "%{http_code}\n" localhost:8000/health   # 200
curl -s -i -X POST localhost:8000/ | grep -i www-authenticate    # Bearer ...

Layout

src/mcp_server/
├── server.py           composition root — builds and wires everything
├── app.py              FastAPI wrapper for the HTTP transport
├── config.py           settings (DB_* and MCP_*)
├── logging_config.py   stderr logging, never stdout
├── api/                MCP tools; converts ValueError to ToolError
├── services/           rules: allow-lists, the SQL guard, truncation
└── repositories/       database access; nothing above this imports SQLAlchemy

Dependencies point one way: api → services → repositories. Services and repositories never import fastmcp; that separation is what keeps the guard and the introspection logic testable without a server.

repositories/ uses SQLAlchemy's Inspector for schema reads, dropping back to raw SQL only where Inspector has no equivalent — has_schema_privilege and has_table_privilege, both needed because Inspector will happily list tables the current role cannot read.

Development

uv run pytest              # 46 tests
uv run ruff check src tests
uv run ruff format src tests

Fast feedback while writing tools — spawns a fresh process, so it always runs current code:

uv run fastmcp list src/mcp_server/server.py
uv run fastmcp call src/mcp_server/server.py list_tables schema=public

Note that an already-connected MCP client keeps running the code as it was when it connected. Restart the client to pick up changes.

Logs

Logs go to stderr, never stdout — under stdio, stdout carries the JSON-RPC stream and one stray line breaks the protocol. For the same reason, never use print() in this codebase.

Claude Code captures a spawned server's stderr here:

~/Library/Caches/claude-cli-nodejs/<project>/mcp-logs-db-explorer/

At INFO you get query timings, guard rejections and denied schemas. DEBUG adds the SQL and per-inspection timings.

Notes and known limits

• stdio is unauthenticated. Both transports serve the same four tools, but only HTTP checks a token. If that matters, remove the stdio entry point.
• All four tools require a valid token over HTTP, but none require a specific scope. Tiering them is a require_scopes(...) argument away.
• truncated in a run_query result means this server's cap was applied. One row beyond max_rows is fetched to detect that exactly. A smaller LIMIT you write yourself is honoured and is not reported as truncation.
• Revoking SELECT hides a table's data, not its existence: pg_catalog is world-readable, so metadata for unreadable tables can still be reached through run_query. list_tables filters them out.