postgres-mcp

# @contextflo/postgres-mcp

The analytics MCP server for Postgres. Read-only by construction, with schema context that makes answers correct.

A drop-in replacement for the archived `@modelcontextprotocol/server-postgres`, which shipped with a
[SQL injection vulnerability](https://securitylabs.datadoghq.com/articles/mcp-vulnerability-case-study-SQL-injection-in-the-postgresql-mcp-server/)
that let `COMMIT; DROP SCHEMA public CASCADE` walk straight out of its read-only transaction.

```bash
npx @contextflo/postgres-mcp postgresql://localhost/mydb
```

## Why this one

**Read-only that holds up.** The archived server enforced read-only as a property of the SQL *string*. Here it is a
property of the connection, the role, and the wire protocol: four independent layers, each of which stops that
payload on its own. The exploit is a test case in this repo.

**Answers that make sense.** A model that does not know `fct_orders_v2` is the table your team actually uses, or that
`revenue` is gross rather than net, writes confident, wrong SQL. `.contextflo/context.md` is a markdown file you edit
and this server hands to the model. No database, no index, no service.

## Setup

**1. Create a read-only role.** Run this as the database owner, in `psql` or your provider's SQL editor. Use your
database name and a real password, and repeat the schema lines for each schema the agent should see:

```sql
CREATE ROLE mcp_readonly LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;
```

This makes read-only a property of the database, not just of this server's code.

**2. Put that role's connection string in `.env`** in your project folder:

```bash
DATABASE_URL='postgresql://mcp_readonly:change-me@db.example.com:5432/mydb'
```

Keep the quotes: hosted providers add `?sslmode=require&...`, and the `&` needs them. The server reads `DATABASE_URL`
from `.env` in the directory it starts in, so the connection string never appears on a command line.

**3. Generate the context file:**

```bash
npx @contextflo/postgres-mcp init
```

That writes `.contextflo/context.md`, seeded from your `COMMENT ON` values. Edit it: the business definitions section
is where the value is.

**4. Add it to your client.** Claude Code starts servers in your project folder, so it finds `.env` and the context
file on its own:

```bash
claude mcp add postgres --scope project -- npx -y @contextflo/postgres-mcp
```

`--scope project` writes `.mcp.json` into the folder instead of your global config.

<details>
<summary>Cursor, Claude Desktop, VS Code</summary>

Other clients may start servers outside your project folder (Claude Desktop starts them in `/`), so give them the
connection string and the context file explicitly:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@contextflo/postgres-mcp", "--context-file", "/path/to/project/.contextflo/context.md"],
      "env": { "DATABASE_URL": "postgresql://mcp_readonly:change-me@db.example.com:5432/mydb" }
    }
  }
}
```

**Cursor** uses `.cursor/mcp.json`. **Claude Desktop** uses `claude_desktop_config.json` (macOS:
`~/Library/Application Support/Claude/`, Windows: `%APPDATA%\Claude\`). **VS Code** uses `.vscode/mcp.json`, with
`servers` in place of `mcpServers`.

</details>

## Tools

| Tool | What it does |
| --- | --- |
| `query` | Runs one read-only statement: `SELECT`, `WITH ... SELECT`, `EXPLAIN`, or `SHOW`. |
| `list_tables` | Lists readable tables with descriptions. `pattern` matches anywhere in the name or description. |
| `get_table_context` | Describes tables: columns, types, keys, foreign key targets, enum values, curated descriptions. |
| `add_table_context` | Lets the agent write down a gotcha it found (`amount` is in cents, `status` has an undocumented value) in the context file. |

There is no separate search tool, and that is deliberate. `information_schema` and `pg_catalog` are ordinary tables,
so anything more specific, like finding every column named like `%revenue%` or listing tables with no primary key,
is a query the model can write itself:

```sql
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name ILIKE '%revenue%';
```

Table schemas are also exposed as `postgres://<host>/<table>/schema` resources, matching the archived server, for
anything pinned to those URIs. Most clients never fetch resources on their own, which is why discovery lives in the
tools.

## How read-only is enforced

Four layers. Each one stops the archived server's exploit by itself.

**1. Extended query protocol.** User SQL goes through `pg-cursor`, which always issues Parse/Bind/Execute, so
Postgres itself rejects multi-statement input. The archived server called `client.query(sql)` with a bare string;
node-postgres only prepares a statement when there are bind values, so that took the *simple* protocol path, where
`;` separates statements. That is the whole bug.

**2. Connection-level read-only.** `default_transaction_read_only=on` is set in the startup packet, and every
statement runs inside an explicit `BEGIN READ ONLY` that always ends in `ROLLBACK`, never `COMMIT`. The rollback
also undoes any `SET` made inside the transaction, so a statement cannot leave a pooled connection weakened for
whoever gets it next.

**3. A statement allowlist on the real Postgres parser.** [`libpg-query`](https://github.com/launchql/libpg-query-node)
is the actual Postgres C parser compiled to WASM, not a JavaScript approximation of SQL. The whole parse tree is
walked rather than just the top-level node, which is what catches a data-modifying CTE:

```sql
WITH x AS (INSERT INTO users VALUES (1) RETURNING *) SELECT * FROM x
```

That parses as a `SelectStmt`. A validator checking only the statement type runs it. Unknown node types fail closed.

Functions are checked against the database's own catalog. Postgres labels every function immutable, stable, or
volatile, and only volatile ones can have side effects, so a volatile function is refused unless it is on a short list
of harmless ones analysis needs (`random()`, `clock_timestamp()`, the table size functions). That covers `dblink`,
`pg_logical_emit_message` (which writes to the WAL even in a read-only transaction), advisory locks, statistics resets,
and whatever a future Postgres adds, without anyone having to name them. `SECURITY DEFINER` functions, which run with
their owner's privileges, are refused whatever their label. A fixed list of known escapes is checked as well.

**4. A read-only database role.** The layers above are code, and code has bugs. A role that cannot write is enforced
by Postgres regardless, which is why creating one is the first step of [Setup](#setup). The server warns on startup
if you connect as a superuser, and `init` prints the role snippet if the role it connects as can write.

**What this does not protect against.** A function's label is only as honest as whoever created it: a user-defined
function declared `STABLE` that writes anyway is allowed. Layer 4 is what stops that, which is why the read-only role
is the recommended setup rather than an optional extra. Read-only is also not
confidentiality: anything the connected role can read, a model can read, so grant it only what you want an agent
to see.

## Migrating from `@modelcontextprotocol/server-postgres`

Swap the package name. The tool is still called `query`, still takes `sql`, still returns JSON rows, and the
connection string is still the first argument.

Four deliberate differences:

1. **Multi-statement SQL and `SET`/`RESET` are rejected** with a clear error. On the archived server these "worked",
   and that was the vulnerability.
2. **Results are capped** at 1000 rows and 50,000 characters by default, and single values over 2,000 characters are
   shortened. Truncation is stated in the output, never silent. Rows come back one per line rather than
   pretty-printed, which roughly halves their token cost; it is still a JSON array. Dates and timestamps are exactly
   what Postgres sent, not re-rendered in the server's timezone.
3. **Schema discovery is a tool, not just a resource.** Most clients do not auto-attach resources, which is why
   models using the old server so often did not know the schema.
4. **All non-system schemas are visible**, not only `public`, and column descriptions come through from
   `COMMENT ON`.

## Options

```
--max-rows <n>            Maximum rows returned per query (default: 1000)
--max-output-chars <n>    Character budget for one query result (default: 50000)
--statement-timeout <ms>  Server-side statement timeout (default: 30000)
--context-file <path>     Curated schema context (default: .contextflo/context.md)
--no-context-writes       Do not offer add_table_context; the context file is only read
--log-file <path>         Query audit log (default: .contextflo/log.md once that directory exists)
--no-log                  Never write a query log
--http                    Serve over streamable HTTP instead of stdio
--port <n>                HTTP port (default: 8080)
--host <addr>             HTTP bind address (default: 127.0.0.1)
```

`DATABASE_URL` supplies the connection string if you do not pass one, from the environment or from `.env` in the
current directory. The server connects on first use: without a connection string it still starts and lists its tools,
and each tool call says what is missing. `AUTH_TOKEN`, with `--http`, requires that
value as a bearer token.

**Connection poolers.** PgBouncer (and so the pooled connection strings from Supabase, Neon, and others) refuses
the startup parameters this server normally sends. When that happens it reconnects without them and says so on
stderr. Nothing is weakened: every statement still runs in `BEGIN READ ONLY` with its own statement