Postgres (read-only)

@contextflo/postgres-mcp

The analytics MCP server for Postgres. Read-only by construction, with a context file you and the agent both write to, so the model answers the way your team would.

Built and maintained by Contextflo.

A drop-in replacement for the archived @modelcontextprotocol/server-postgres, which shipped with a SQL injection vulnerability that let COMMIT; DROP SCHEMA public CASCADE walk straight out of its read-only transaction.

Watch the 100-second demo: setup, the agent working out that an events table is not what it looks like and saving that as a note, and a DROP the server refuses.

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 wire protocol, the connection, the Postgres parser, and the database role: 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, with nothing behind it but the file. The agent adds to it too: when it learns something the schema does not say, add_table_context appends a note, which you review in a diff like any other change.

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:

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:

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.

Point it at a read replica or a branch rather than your primary if you can. Read-only stops writes, not load: an agent exploring your data can run a full-table scan or a heavy join, and on the primary that competes with your application. Neon and Supabase can branch or replicate a database in a few clicks, and RDS, Cloud SQL, and most other hosts offer read replicas. The statement timeout (30 seconds by default, --statement-timeout) and the row cap limit how long one query runs and how much it returns; they do not make it cheap.

3. Generate the context file:

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:

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

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

Cursor, Claude Desktop, VS Code

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:

{

"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.

Tools

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:

SELECT table_schema, table_name, column_name

FROM information_schema.columns WHERE column_name ILIKE '%revenue%';

Table schemas are also exposed as postgres:// / /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 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:

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. 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. The function check sees the functions a query calls directly, and it trusts how each is labelled. A user-defined function declared STABLE that writes anyway is allowed, and so is a function reached indirectly: inside a view, behind an operator, or through a type cast. The read-only transaction still refuses anything that changes table data that way; what can slip through is the rarer kind of side effect, such as a message written to the WAL. Layer 4 is what stops those, 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 Character budget for one query result (default: 50000) --statement-timeout Server-side statement timeout (default: 30000) --context-file Curated schema context (default: .contextflo/context.md) --no-context-writes Do not offer add_table_context; the context file is only read --log-file 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 HTTP port (default: 8080) --host 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 timeout, and CI runs the read-only suite through PgBouncer in transaction mode.

The context file

# Database context

Revenue means gross, before refunds. "Active customer" means an order in the last 90 days.

Tables

public.orders

One row per customer order. Source of truth for revenue, not orders_legacy.

• revenue_usd: Gross revenue, before refunds. Net lives in order_refunds.
• status: One of pending, paid, refunded.

Everything above ## Tables is handed to the model: as server instructions, and again at the top of list_tables, becaus