MCP 2.0 PostgreSQL Server

A Python MCP server for a self-hosted PostgreSQL instance. It uses the official MCP Python SDK 2.x, Psycopg 3 async connections, and a lifespan-managed connection pool.

Tools

• postgres_server_info: server/database/role/version, recovery state, and default transaction policy.
• postgres_list_schemas: schema discovery; optionally includes system schemas via include_system.
• postgres_list_relations: tables, foreign tables, views, and materialized views in a schema; supports name_pattern (ILIKE) filtering.
• postgres_describe_relation: columns, defaults, comments, constraints, and indexes.
• postgres_query_sql: one arbitrary SQL statement inside a PostgreSQL READ ONLY transaction with timeout and row limits.
• postgres_execute_sql: one transactional SQL command, disabled by default and protected by MCP human elicitation when enabled.

Install

cp .env.example .env
$EDITOR .env
uv sync --dev

DATABASE_URL is required and can be supplied either in the process environment or the located .env file. To use an env file outside the launch directory, set:

export MCP_PG_ENV_FILE=/absolute/path/to/.env

Start the MCP Inspector:

uv run mcp dev src/mcp_postgres_server/server.py

Run as a local stdio MCP server:

uv run mcp-postgres

The default transport is stdio. For local Streamable HTTP:

MCP_PG_TRANSPORT=streamable-http uv run mcp-postgres
# endpoint: http://127.0.0.1:8765/mcp

Alternative ASGI launch:

uv run uvicorn mcp_postgres_server.asgi:app --host 127.0.0.1 --port 8765

Client configuration

VS Code / compatible local stdio host

Use an absolute project path. The host should launch the locked project environment. Run uv sync --dev first so the project has a generated uv.lock before using --frozen:

{
  "servers": {
    "postgres-local": {
      "type": "stdio",
      "command": "/absolute/path/to/uv",
      "args": [
        "run",
        "--directory",
        "/absolute/path/to/mcp-postgres-server",
        "--frozen",
        "mcp-postgres"
      ],
      "env": {
        "MCP_PG_ENV_FILE": "/absolute/path/to/mcp-postgres-server/.env"
      }
    }
  }
}

For Qwen Code or another host using the common mcpServers shape, keep the same command/args and place the entry under mcpServers.

SQL parameters

The two SQL tools use Psycopg positional placeholders. Parameters are deliberately limited to JSON scalar values; cast strings in SQL for UUID/date/network/domain types when needed.

{
  "sql": "SELECT * FROM public.accounts WHERE id = %s::uuid",
  "params": ["12345678-1234-5678-1234-567812345678"],
  "max_rows": 100
}

Do not quote %s; Psycopg sends the value separately from the SQL text.

Write-command policy

Write execution is off by default:

MCP_PG_ALLOW_WRITE_COMMANDS=false

To enable it while retaining a mandatory human approval prompt:

MCP_PG_ALLOW_WRITE_COMMANDS=true
MCP_PG_CONFIRM_WRITE_COMMANDS=true

The approval is an MCP 2.0 resolver dependency, not a model-visible Boolean parameter. The prompt displays the complete SQL and its bound parameter list, so a model cannot self-approve or hide the effective values behind placeholders. Each accepted command runs in one managed transaction and commits only after successful completion. Explicit transaction-control SQL and commands prohibited inside a transaction are rejected or fail without committing.

Setting MCP_PG_CONFIRM_WRITE_COMMANDS=false removes the human approval gate and is not recommended for a general-purpose agent.

PostgreSQL role hardening

Do not point the server at a PostgreSQL superuser. Create a dedicated login and grant only the schemas/tables/actions the agent needs. A typical read-only role is:

CREATE ROLE mcp_agent LOGIN PASSWORD 'replace-with-a-long-random-password';
GRANT CONNECT ON DATABASE appdb TO mcp_agent;
GRANT USAGE ON SCHEMA public TO mcp_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_agent;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_agent;

For controlled writes, grant only the required INSERT, UPDATE, DELETE, sequence, or DDL privileges. PostgreSQL privileges remain the authoritative boundary even if MCP write tools are enabled.

Operational limits

• Read tools use SET TRANSACTION READ ONLY. Each SQL call accepts exactly one statement.
• Statement and lock timeouts are applied with transaction-local PostgreSQL settings.
• Query and RETURNING rows are truncated at configured limits.
• Database values are converted to JSON-safe output; numeric/Decimal values become strings to preserve precision and bytea values use a base64: prefix.
• The pool opens once in MCP lifespan and closes cleanly on server shutdown.
• Logs are sent to stderr so stdio protocol output is not corrupted.
• Streamable HTTP binds to 127.0.0.1 by default and uses MCP's localhost DNS-rebinding protection.
• MCP_PG_APPLICATION_NAME (default: mcp-postgres-server) is sent as the PostgreSQL application_name, visible in pg_stat_activity for connection tracking.
• MCP_PG_LOG_LEVEL (default: INFO) controls logging verbosity; set to DEBUG for detailed query logs.
• Connection pool size is configurable: MCP_PG_POOL_MIN_SIZE (default: 1) and MCP_PG_POOL_MAX_SIZE (default: 5). Pool acquire and open timeouts are set via MCP_PG_POOL_ACQUIRE_TIMEOUT_SECONDS (default: 10) and MCP_PG_POOL_OPEN_TIMEOUT_SECONDS (default: 20).

Optional systemd service

The example service assumes a dedicated mcp-postgres system account and a project installed at /opt/mcp-postgres-server with uv sync already completed. Copy and adjust it before enabling:

sudo cp systemd/mcp-postgres-http.service.example /etc/systemd/system/mcp-postgres-http.service
sudo systemctl daemon-reload
sudo systemctl enable --now mcp-postgres-http.service

Validation

uv run ruff check .
uv run ruff format --check .
uv run mypy
uv run pytest

PostgreSQL integration tests are opt-in:

TEST_DATABASE_URL='postgresql://...' uv run pytest tests/test_integration.py

Known boundary

postgres_execute_sql intentionally does not provide an autocommit escape hatch. PostgreSQL commands such as VACUUM, CREATE DATABASE, and some concurrent index operations cannot execute in its managed transaction. Add a separate, narrowly allowlisted maintenance tool rather than weakening the general command tool.