# postgres-mcp-server

[![CI](https://github.com/arieffian/postgres-mcp-server/actions/workflows/ci.yml/badge.svg)](https://github.com/arieffian/postgres-mcp-server/actions/workflows/ci.yml)
[![npm](https://img.shields.io/npm/v/@arieffian/postgres-mcp-server.svg)](https://www.npmjs.com/package/@arieffian/postgres-mcp-server)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)

An MCP server for safely querying PostgreSQL from an LLM. Read-only by default, multi-connection with named aliases, Postgres-session-enforced safety.

## Quickstart

Create `~/.config/postgres-mcp/config.json`:

```json
{
  "connections": {
    "local": {
      "url_env": "LOCAL_DATABASE_URL",
      "mode": "read"
    }
  }
}
```

Add to your MCP client config (Claude Desktop, Cursor, Windsurf, Zed):

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@arieffian/postgres-mcp-server"],
      "env": { "LOCAL_DATABASE_URL": "postgres://user:pw@localhost/db" }
    }
  }
}
```

Restart the client and ask: "Ping the local Postgres and list its tables."

## Modes

Every connection declares its mode statically in config. Escalation requires editing config and restarting.

| Mode  | SELECT | INSERT/UPDATE/DELETE | DDL | Notes |
|-------|:------:|:--------------------:|:---:|-------|
| read  |   ✓    |          ✗           |  ✗  | Default. `SET default_transaction_read_only = on` at the session level. |
| write |   ✓    |          ✓           |  ✗  | Writes must go through `begin_transaction` → `execute` → `commit`. |
| admin |   ✓    |          ✓           |  ✓  | Same tx flow as write; DDL also permitted. |

## Tools

**Meta**
- `ping`, `list_connections`

**SQL**
- `query` — read-only SELECT via server-side cursor
- `begin_transaction`, `commit`, `rollback` — tx lifecycle
- `execute` — INSERT/UPDATE/DELETE/DDL inside an open tx

**Schema introspection (Phase 2, new in 0.2.0)**
- `list_databases`, `list_schemas`
- `list_tables` — includes regular, partitioned, and foreign tables (via `kind` field); row count is clamped to 0 for never-analyzed tables
- `list_indexes`, `list_constraints` — per-table catalog listings
- `list_functions` — excludes functions installed by extensions
- `describe_table` — composite: columns, PK, FKs, indexes, constraints in one call

**Observability** — ships in Phase 3.

## Safety

Five layers — see [docs/safety.md](docs/safety.md). Highlights:

- Read-only enforced by the Postgres session, not by parsing SQL — we do not trust our own parser.
- Statement timeout per connection (default 30s).
- Every SELECT wrapped in a server-side cursor; results capped by row count and byte size.
- Writes require an explicit transaction; no autocommit.
- Bound parameter values are never logged. Credentials in URLs are redacted.

## Configuration

Discovery order (first hit wins):

1. `--config <path>` CLI flag
2. `$POSTGRES_MCP_CONFIG` env var
3. `$XDG_CONFIG_HOME/postgres-mcp/config.json` (fallback `~/.config/postgres-mcp/config.json`)
4. `./postgres-mcp.config.json`

## Contributing

Requires Node ≥ 20. Local dev: `npm install`, `npm test`. Integration tests use `testcontainers` and need a working Docker daemon.

## Publishing

Set `NPM_TOKEN` in the repo's GitHub Actions secrets. Changesets automatically opens a release PR on push to `main`; merging it publishes to npm with provenance.

## License

MIT