# postgres-mcp

A read-only PostgreSQL [MCP](https://modelcontextprotocol.io) server with a
SQL security policy. It exposes a small set of read-only tools and validates
every `query` against an allowlist of commands, a deny-list of keywords, an
automatic row limit, and a per-statement timeout.

Read-only is enforced twice: by the SQL validator and by the connection itself,
which is opened with `default_transaction_read_only=on` and a
`statement_timeout`. A statement that slips past the validator still cannot
write and cannot run forever.

## Install

```bash
pip install -e ".[dev]"        # editable, with test deps
# or from a published index:
pip install postgres-mcp
```

## Configure

Copy `config.example.yaml` to `config.yaml` (gitignored) and fill it in. Any
value may reference an environment variable as `${VAR}` — keep secrets in the
environment, never in a committed file.

```yaml
postgres:
  host: "localhost"
  port: 5432
  dbname: "postgres"
  user: "${POSTGRES_MCP_USER}"
  password: "${POSTGRES_MCP_PASSWORD}"

security:
  query_timeout_ms: 30000
  max_rows_limit: 1000
  allowlist_commands: ["SELECT", "EXPLAIN", "SHOW", "WITH", "ANALYZE"]
  deny_keywords: ["DROP", "DELETE", "INSERT", "UPDATE", "ALTER",
                  "CREATE", "TRUNCATE", "GRANT", "REVOKE"]
  log_queries: true
```

Resolution order: `$POSTGRES_MCP_CONFIG` (explicit path), then `./config.yaml`,
then `./config.example.yaml` in the working directory.

## Cooperation with masstrade-agent

postgres-mcp can share its connection source with `masstrade-agent`: point it at
the same `~/.wren/profiles.yml` that `masstrade-agent` maintains, and both
servers read the readonly credentials from `MASSTRADE_RO_USER` /
`MASSTRADE_RO_PASSWORD` (process environment or `~/.wren/.env`).

```yaml
wren:
  profiles: true          # read ~/.wren/profiles.yml
  profile: null           # null = follow `active`; or a tenant SID (e.g. "APR")
```

With `wren.profiles: true` the `postgres:` block is ignored, and the
`select_profile` tool is registered. The typical flow is:

1. `masstrade-agent` → `select_database APR` (upserts the `APR` profile).
2. `postgres-mcp` → `select_profile APR` (re-points at the same database).
3. `postgres-mcp` → `query "SELECT ..."` (read-only against `APR`).

When no config file is found at all and `~/.wren/profiles.yml` exists,
wren-profiles mode is enabled automatically.

## Run

```bash
postgres-mcp            # stdio, via the installed entry point
# or
python -m postgres_mcp
```

## Tools

| Tool | Description |
| --- | --- |
| `query` | Run a validated read-only SQL statement |
| `list_tables` | List base tables in the `public` schema |
| `describe_table` | Describe the columns of a table or view |
| `relationships` | List foreign-key relationships |
| `select_profile` | Switch to a profile in `~/.wren/profiles.yml` (wren mode only) |

## Security model

1. **Allowlist** — the first command must be on `allowlist_commands`.
2. **Deny-list** — any whole-word `deny_keywords` entry anywhere in the SQL
   rejects the statement.
3. **Row limit** — a `LIMIT` is appended to `SELECT` when the query has none
   (`SHOW`/`EXPLAIN` are left untouched, since they reject `LIMIT`).
4. **Timeout** — `statement_timeout` is set per connection from
   `query_timeout_ms`.
5. **Query log** — when `log_queries` is true, each query is logged (truncated)
   to stderr.

The validator is deliberately lightweight (string + regex, no SQL parser), so
a denied keyword inside a string literal or quoted identifier can cause a false
rejection — safe by design, but noted. The connection-level guards remain the
authoritative boundary.

## MCP client wiring

Point any MCP client at the stdio server. With Cursor or Hermes the entry is a
server whose command is `postgres-mcp` (or `python -m postgres_mcp`); the
`user`/`password` come from the environment of the process that launches it.

## License

MIT — see [LICENSE](LICENSE).