postgres-mcp
# postgres-mcp
A small PostgreSQL [MCP](https://modelcontextprotocol.io) server for Claude Code and other MCP clients,
built to be safe to point at a shared database.
- **Read-only by default.** Write tools exist only when `PG_MCP_MODE=readwrite`.
- **One statement per call.** Every statement goes through the extended query protocol, which rejects
`select 1; drop table t`. Transaction control (`COMMIT`, `BEGIN`, `SET TRANSACTION`, ...) is refused.
- **Every call is its own transaction.** Reads run in `REPEATABLE READ READ ONLY` with a snapshot taken
first, then always roll back; the session also defaults to `default_transaction_read_only=on`.
- **Bounded.** `statement_timeout` on every call; reads go through a cursor and fetch at most
`PG_MCP_MAX_ROWS + 1` rows; long cells and `bytea` are summarised.
- **No password on the command line.** Arguments are refused; the connection string comes from the
environment, so it does not show up in `ps` or Task Manager.
- **Pinned.** Exact dependency versions, a committed `package-lock.json`, and `ignore-scripts=true`.
Written after reviewing `@sarmadparvez/postgresql-mcp@1.0.8`, whose `query` tool could be escaped with
`COMMIT; <write>` because it sent the SQL with the simple query protocol. No code is shared.
## Tools
| Tool | Mode | What it does |
|---|---|---|
| `query` | always | One read-only statement (`SELECT`, `WITH`, `VALUES`, `TABLE`, `SHOW`, `EXPLAIN`) |
| `list_tables` | always | Tables, views, materialized views with estimated rows and size; all user schemas by default |
| `describe_table` | always | Columns, constraints and indexes of one relation |
| `connection_info` | always | Role, mode, limits, and whether the role itself could write |
| `execute` | readwrite | One write statement, committed; `dry_run: true` reports the effect and rolls back |
| `transaction` | readwrite | Up to 100 statements, all or nothing; supports `dry_run` |
## Install
```bash
git clone https://github.com/cleondev/postgres-mcp.git
cd postgres-mcp
npm ci
```
Requires Node.js 20 or later.
## Register with Claude Code
```bash
claude mcp add postgres-mydb --scope user \
-e PG_MCP_URL=postgres://reader@db.example.com:5432/mydb \
-- node /path/to/postgres-mcp/src/index.js
```
Keep the password out of `PG_MCP_URL` where you can: `pg` also reads `PGPASSWORD` and the standard
pgpass file (`~/.pgpass`, or `%APPDATA%\postgresql\pgpass.conf` on Windows).
## Configuration
| Variable | Default | Meaning |
|---|---|---|
| `PG_MCP_URL` (or `DATABASE_URL`) | required | `postgres://user[:password]@host:port/db` |
| `PG_MCP_MODE` | `readonly` | `readonly` or `readwrite` |
| `PG_MCP_STATEMENT_TIMEOUT_MS` | `30000` | Per-call statement timeout |
| `PG_MCP_MAX_ROWS` | `500` | Rows returned by `query` before truncation |
| `PG_MCP_MAX_CELL_CHARS` | `2000` | Characters kept per cell |
## Threat model
This server stops a model from writing by accident or by being talked into a multi-statement escape.
It cannot make a role that is allowed to write truly read-only: a function the role may call (for
example `dblink_exec`, which opens a second connection) runs outside this server's transaction.
`connection_info` and the startup log warn when that is the case.
For a hard boundary, connect `readonly` mode as a role that has only `SELECT`:
```sql
create role mydb_reader login password '...';
grant connect on database mydb to mydb_reader;
grant usage on schema app to mydb_reader;
grant select on all tables in schema app to mydb_reader;
alter default privileges for role mydb_owner in schema app grant select on tables to mydb_reader;
```
In `readwrite` mode, review each `execute`/`transaction` call before approving it; do not add those
tools to an auto-approve list.
## Tests
```bash
npm test # unit tests only
PG_MCP_TEST_URL=postgres://... npm test # plus end-to-end tests over stdio
```
The end-to-end tests touch only temporary tables, so they are safe against a shared database.
## License
MIT
TDQS
Scored across 4 tools
Each tool targets a clearly different need: arbitrary reads (query), object inventory (list_tables), single-object structure (describe_table), and server/session context (connection_info). The only overlap is that query can reach pg_catalog and duplicate the metadata tools, but the descriptions make the intended split clear enough to avoid misselection.
Three of four names follow a recognizable verb/verb_noun style (query, list_tables, describe_table) with snake_case throughout. connection_info is the one break in pattern as a noun phrase, but it still reads naturally as a distinct informational tool.
Four tools is lean and each one earns its place in a deliberately read-only server; nothing feels redundant. It sits at the low end of the comfortable range, so a slightly broader surface (e.g. schema/extension listing) could have been added without bloat.
For a read-only inspection domain, the set covers the main path: discover objects, inspect structure, run ad-hoc queries, and confirm what the role can do. Gaps are minor — no schema/database listing tool, no column-name search, and no dedicated index or function introspection — all workable via query on pg_catalog.