Skip to content

CLAUDE.md

Latest commit

 

History

History
74 lines (53 loc) · 3.22 KB

CLAUDE.md

File metadata and controls

74 lines (53 loc) · 3.22 KB

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

What This Is

A PostgreSQL MCP (Model Context Protocol) server that lets AI agents interact with Postgres databases. It exposes database tools (query, list tables/schemas, describe tables, foreign keys, relationships) via the FastMCP framework with async connection pooling. Optional JWT/JWKS auth with YAML-based permissions for multi-user environments.

Commands

# Setup
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
pip install -r dev-requirements.txt

# Run MCP server (stdio, local dev)
python postgres_server.py --conn "postgresql://user:pass@host:5432/db"

# Run MCP server (HTTP transport)
python postgres_server.py --transport streamable-http --host 127.0.0.1 --port 8000

# Tests
pytest -q
pytest tests/test_tools.py                          # unit tests
pytest tests/test_tools.py::test_query_no_dsn_async  # single test

# Linting
ruff check .

Architecture

Single-file async MCP server:

  • postgres_server.py — Uses FastMCP from mcp[cli] to register tools. Connects to Postgres via psycopg with AsyncConnectionPool from psycopg_pool. Supports three transports: stdio (local), sse, streamable-http (remote/Railway). Lifespan context manager owns the pool, config, and permissions.

Key components:

  • ServerConfig — Dataclass parsed from CLI args + env vars
  • AppContext — Lifespan-scoped context holding pool, config, permissions
  • Permissions / RolePermissions — YAML-based role/schema/table/operation enforcement
  • JWKSTokenVerifier — Optional JWT verification against external IdP JWKS endpoint

Tools: query, list_schemas, list_tables, describe_table, get_foreign_keys, find_relationships, server_info, db_identity

Two modes:

  • No-auth (default): Shared connection pool, no permission checks
  • Auth-enabled: JWT tokens verified via JWKS, permissions enforced per-user from permissions.yaml

Key Environment Variables

Variable Purpose
DATABASE_URL / POSTGRES_CONNECTION_STRING Default DB connection
POSTGRES_READONLY true to restrict to SELECT-like queries
POSTGRES_STATEMENT_TIMEOUT_MS Query timeout in ms
MCP_TRANSPORT stdio, sse, or streamable-http
MCP_HOST / MCP_PORT Server bind address (default 127.0.0.1:8000)
MCP_POOL_MIN / MCP_POOL_MAX Connection pool sizing (default 2-10)
MCP_AUTH_ISSUER JWT issuer URL (enables auth mode)
MCP_AUTH_AUDIENCE Expected JWT audience
MCP_AUTH_JWKS_URL JWKS endpoint (auto-derived from issuer if not set)
MCP_PERMISSIONS_FILE Path to permissions.yaml

Testing Conventions

  • Tests must pass without a database connection. All tools return friendly empty/notice results when no DSN is configured.
  • tests/conftest.py clears DSN env vars to simulate no-DSN mode.
  • tests/test_tools.py — Unit tests for tools, permissions, and auth.
  • tests/test_integration.py — Integration tests (skipped unless DATABASE_URL is set).

Important Operational Note

After changes to postgres_server.py, the MCP server must be restarted before testing (there is no hot-reload).