# 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

```bash
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:

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

Start the MCP Inspector:

```bash
uv run mcp dev src/mcp_postgres_server/server.py
```

Run as a local stdio MCP server:

```bash
uv run mcp-postgres
```

The default transport is stdio. For local Streamable HTTP:

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

Alternative ASGI launch:

```bash
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`:

```json
{
  "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.

```json
{
  "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:

```dotenv
MCP_PG_ALLOW_WRITE_COMMANDS=false
```

To enable it while retaining a mandatory human approval prompt:

```dotenv
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:

```sql
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:

```bash
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

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

PostgreSQL integration tests are opt-in:

```bash
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.