# pg-mcp

An MCP server for safe, audited PostgreSQL access, with a web UI.

`pg-mcp` puts a PostgreSQL database behind one or more named MCP endpoints. Each endpoint has
its own bearer token and its own set of connections, and every tool call on it is written to an
audit log. A browser UI manages connections, endpoints, database roles and the audit records.

## Features

- **MCP endpoints** at `/<name>/mcp`, on the main listener or on an endpoint's own port.
- **Eleven MCP tools**: `list_connections`, `list_schemas`, `list_tables`, `get_ddl`,
  `get_table_info`, `query`, `list_roles`, `explain`, `count_rows`, `sample_rows`,
  `find_columns`.
- **SQL guard**: read-only and dry-run calls are parsed with PostgreSQL's own grammar before
  anything runs, so transaction control, DDL, `COPY`, `DO` and other escapes are refused.
- **Bearer-token authentication** per endpoint; only a SHA-256 digest of the token is stored.
- **Web UI** at `/ui/` for connections, endpoints, roles and audit records.
- **Audit log** with rotation, grouping and a live event stream.
- **Configuration** in YAML or JSON, in memory, or in a single file rewritten atomically.

## Requirements

- Python 3.12 or newer
- PostgreSQL (any supported server version)
- [uv](https://docs.astral.sh/uv/) for the source install, or `pip`

## Install

From a checkout:

```bash
uv sync
```

Or install the package into a virtual environment:

```bash
python -m venv .venv
. .venv/bin/activate
pip install .
```

## Quick start

Start with a configuration file and an admin password:

```bash
export PG_MCP_ADMIN_PASSWORD='choose-a-strong-password'
uv run pg-mcp serve --config pg-mcp.yaml
```

Open <http://127.0.0.1:8765/ui/> and log in with the admin password.

Without `--config`, the server starts with an empty in-memory configuration:

```bash
uv run pg-mcp serve
```

By default the server listens on `127.0.0.1:8765` and writes `pg-mcp-audit.log` in the working
directory. Run `uv run pg-mcp serve --help` for every flag and environment variable.

## Configuration

```yaml
settings:
  audit_max_bytes: 10485760
  audit_max_files: 5

connections:
  - name: app
    host: 127.0.0.1
    port: 5432
    database: appdb
    user: app_reader
    sslmode: prefer
    read_only: true
    statement_timeout_ms: 30000
    max_rows: 1000

endpoints:
  - name: analyst
    description: Read-only access for the analyst agent
    connections: [app]
```

An endpoint without a token loads disabled. Issue a token from the Endpoints view (or
`POST /api/endpoints/<name>/token`); issuing a token enables the endpoint. The token is shown
once and only its digest is kept.

A hand-written file may contain connection passwords, but it must be readable by its owner only
(mode `0600`); such a file is then read-only and changes stay in memory. Passwords entered
through the UI are never written to the file.

The admin password comes from `--admin-password-stdin`, then `PG_MCP_ADMIN_PASSWORD`, then a
terminal prompt. It is held in memory for the process lifetime and compared in constant time.

## Connect an MCP client

Create an endpoint in the UI, assign a connection to it, and issue a token. Then point the
client at the endpoint:

```json
{
  "mcpServers": {
    "pg": {
      "url": "http://127.0.0.1:8765/analyst/mcp",
      "headers": {
        "Authorization": "Bearer <endpoint-token>"
      }
    }
  }
}
```

If the endpoint has a `listen_port`, use `http://<listen_host>:<listen_port>/<name>/mcp`
instead. The admin session cookie is accepted only on `/api/`, never on an MCP endpoint.

## How read-only access works

Set `read_only: true` on a connection, or pass `read_only` to `query`. A guarded call is parsed
before a database session is opened: only `SELECT`, `VALUES`, `TABLE`, `SHOW` and `EXPLAIN` of a
select are allowed, and data-modifying `WITH`, locking clauses and `SELECT ... INTO` are refused.
The server owns the transaction, forces `client_encoding` to UTF8 and
`standard_conforming_strings` on, and applies the connection's statement timeout.

`explain` with `analyze: true` passes the same guard. Full-access `query` calls without
`read_only`, `dry_run` or `isolation_level` are sent unread, as configured by the administrator.

## Development

```bash
uv run ruff check .
uv run mypy src
uv run pytest tests/unit -q
```

The SPA in `web/` is built into `src/pg_mcp/static/`:

```bash
cd web
uv run npm ci
uv run npm run build
```

Integration and browser tests use testcontainers (Docker, `postgres:16`) and headless Chromium:

```bash
uv run playwright install chromium
uv run pytest tests/integration -q < /dev/null
```

The integration suite takes about 20 minutes. Unit tests are the normal gate; integration tests
need Docker.

## Security notes

- Endpoint tokens are random, URL-safe strings; only their SHA-256 digest is stored.
- The admin session is signed with a key generated at process start, so a restart ends every
  session.
- Responses never include a connection password.
- Every MCP tool call writes an audit record, including refused calls.
- The server binds to `127.0.0.1` by default. Put it behind TLS and a trusted reverse proxy
  before exposing it beyond the local machine.

## License

MIT. See [LICENSE](LICENSE).