Read-only MCP server for PostgreSQL. It gives any Model Context Protocol client (Claude Desktop, Cursor, or another stdio client) seven tools to inspect schemas, run read-only queries, explain plans, count rows, and discover foreign keys. It works against a local database or through Cloud SQL Proxy. Use it for inspection and debugging, not for migrations or writes.
git clone https://github.com/damrek/postgres-mcp-server.git
cd postgres-mcp-server
npm ci
npm run build- Set the database environment variables (see Configuration).
- Add the server to your MCP client (see Connect a client).
- Verify it with the MCP Inspector or
npm test.
- Read-only by design - Every query runs inside
BEGIN TRANSACTION READ ONLYand passes a statement validator; writes, DDL, locking clauses,SELECT INTO, and stacked statements are rejected. See Security. - Seven focused tools - List databases and tables, describe structures, query data, explain plans, count rows, and discover foreign keys.
- Parameterized queries - Pass values through
$1,$2instead of interpolating them into SQL. - Structured output - Every tool publishes an
outputSchemathat matches thestructuredContentit returns, so clients can validate results programmatically. - Composite foreign keys - Relationship discovery maps each source column to its exact target column, with schema-qualified output.
- Bounded query responses -
postgres_query_dataapplies row limits and 25K character trimming, and every statement is bounded byDB_STATEMENT_TIMEOUT.
- Node.js 20.19+, 22.13+, or 24+ (see
enginesinpackage.json). - A PostgreSQL instance accessible from this process (tested against PostgreSQL 16).
- Network access from this process to
DB_HOST/DB_PORT.
| Variable | Default | Description |
|---|---|---|
DB_HOST |
localhost |
PostgreSQL host |
DB_PORT |
5432 |
PostgreSQL port |
DB_USER |
postgres |
Database user |
DB_PASSWORD |
- | Password (optional) |
DB_NAME |
- | Default database. When omitted, connections use the user's default database. |
DB_STATEMENT_TIMEOUT |
15000 |
Per-statement timeout in integer milliseconds |
DB_CONNECT_TIMEOUT |
10000 |
Connection timeout in integer milliseconds |
The server only opens TCP connections to DB_HOST/DB_PORT. For Cloud SQL, start the proxy locally and point the server at it:
cloud-sql-proxy --port 5432 project:region:instanceThen use DB_HOST=127.0.0.1 and DB_PORT=5432. The proxy owns authentication and TLS; the MCP server does not manage the proxy process.
Add this to claude_desktop_config.json:
{
"mcpServers": {
"postgres": {
"command": "node",
"args": ["/path/to/postgres-mcp-server/dist/index.js"],
"env": {
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_USER": "postgres",
"DB_PASSWORD": "your_password",
"DB_NAME": "your_database"
}
}
}
}Use the same mcpServers block in .cursor/mcp.json at the project root, or in ~/.cursor/mcp.json for a global configuration. Restart Cursor; a green dot next to the server name in Settings > MCP means the connection succeeded.
The server speaks MCP over stdio. Build it first, then run:
DB_HOST=localhost DB_USER=postgres node dist/index.js| Tool | Use it for | Main inputs |
|---|---|---|
postgres_list_databases |
Discover databases | - |
postgres_list_tables |
List tables and views in a schema | schema, database |
postgres_describe_table |
Columns, constraints, and indexes | table_name, schema, database |
postgres_query_data |
Run one read-only statement | sql, params, limit, database |
postgres_explain_query |
Inspect a JSON execution plan | sql, analyze, database |
postgres_count_rows |
Exact or estimated row counts | table_name, where_clause, exact, database |
postgres_list_relationships |
Foreign keys and JOIN hints | table_name, database |
All tools except postgres_list_databases accept an optional database; when omitted it uses DB_NAME, or the user's default database if DB_NAME is unset. Every tool publishes an outputSchema for its structuredContent.
Lists non-template databases on the instance.
Lists tables and views in the requested schema. schema defaults to public. Unquoted schema names fold to lowercase; double-quote mixed-case names ("MySchema").
Returns columns, constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY with PostgreSQL definitions), and indexes. A missing table returns a non-error message with empty arrays instead of failing the request.
Runs one read-only statement: SELECT, WITH, TABLE, VALUES, SHOW, or EXPLAIN. Use params for $1, $2 values.
Rows are read with a server-side cursor, so an existing LIMIT in the SQL is preserved and the SQL text is never rewritten. limit defaults to 100 and caps at 1000. The response reports truncated: true when rows were dropped by the row limit or by the 25K character trim.
Returns the execution plan as pretty-printed JSON in the text content and in structuredContent.plan. With analyze: true the statement actually runs, includes actual runtimes and buffer usage, stays inside a read-only transaction, and is bounded by DB_STATEMENT_TIMEOUT. Writes, DDL, SELECT INTO, locking clauses, and stacked statements are rejected by the same validator used by postgres_query_data.
Counts rows with an optional where_clause (without the WHERE keyword). exact defaults to true and runs COUNT(*). With exact: false it returns PostgreSQL's reltuples estimate from pg_class, which is cheap on large tables but requires ANALYZE for accuracy; estimates apply to tables and partitioned tables, not views. exact: false does not support where_clause, and reports when no estimate is available.
Discovers foreign keys across non-system schemas and suggests JOIN clauses. Composite foreign keys return one row per column pair, and every row includes source and target schemas. Filter with table_name (source or target) or schema.table.
| Topic | Behavior |
|---|---|
postgres_query_data rows |
Default 100, maximum 1000; an existing SQL LIMIT still applies |
| Response size | Rows are dropped when the JSON payload exceeds 25K characters and truncated is set |
| Timeouts | DB_STATEMENT_TIMEOUT per statement (15s default) and DB_CONNECT_TIMEOUT (10s default) |
| Schema names | Default public; unquoted names fold to lowercase, mixed-case names need double quotes |
| Count estimates | Require ANALYZE; negative estimates are reported as unavailable |
| Relationships | All non-system schemas; one row per foreign-key column pair |
npm testUnit tests always run. Integration tests need a disposable PostgreSQL database and skip without it:
TEST_DB_HOST=127.0.0.1 \
TEST_DB_PORT=5432 \
TEST_DB_USER=postgres \
TEST_DB_PASSWORD=postgres \
TEST_DB_NAME=postgres \
npm testnpx @modelcontextprotocol/inspector \
-e DB_HOST=localhost \
-e DB_PORT=5432 \
-e DB_USER=postgres \
-e DB_PASSWORD=your_password \
node /path/to/postgres-mcp-server/dist/index.jsThis opens a web interface where you can call every tool interactively.
npm run dev # Start with hot reload
npm run build # Compile TypeScript
npm run clean # Remove dist
npm run lint # Run ESLint
npm test # Run unit and integration tests (integration skips without TEST_DB_*)
npm run typecheck # Type-check source and testsRead-only access is enforced in layers, not only by inspecting SQL text:
- Server-side read-only transactions - Every query runs inside
BEGIN TRANSACTION READ ONLY, so PostgreSQL rejects DML and DDL. One PostgreSQL quirk requires the validator:EXPLAIN ANALYZEof a DDL statement can still execute it, soEXPLAINis only accepted when its target is a read-only query. - Statement validation -
postgres_query_dataandpostgres_explain_queryaccept a single read-only statement and reject data-modifying CTEs,EXPLAIN ANALYZEwrites,SELECT INTO, locking clauses, and stacked statements before execution. - Query boundary check - The database wrapper validates every statement again before it reaches a connection, so a tool that forgets to validate cannot bypass the statement rules.
- Identifier validation -
postgres_count_rowsvalidates table names and rejects statement separators inWHEREclauses. - Timeouts -
statement_timeoutand connection timeouts keep a slow query from blocking the server. - No network listener - The server speaks MCP over stdio only; it does not open an HTTP port.
- Parameterized queries - Pass values through
$1,$2instead of interpolating them into SQL.
For a hard guarantee, connect with a dedicated role that only has read privileges:
CREATE ROLE mcp_reader LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE your_database TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;Repeat the USAGE/SELECT grants for every schema you want to expose.
| Symptom | Likely cause | Fix |
|---|---|---|
ECONNREFUSED |
Database or proxy not listening | Start PostgreSQL/Cloud SQL Proxy and check DB_HOST/DB_PORT |
password authentication failed |
Wrong credentials | Check DB_USER/DB_PASSWORD |
permission denied for schema or table |
Role lacks grants | Grant USAGE on the schema and SELECT on the tables |
Schema '<name>' does not exist |
Spelling or case mismatch | Use public, or double-quote a mixed-case schema |
canceling statement due to statement timeout |
Query exceeds 15s | Optimize the query or raise DB_STATEMENT_TIMEOUT |
No columns found for table ... |
Wrong table or schema | Check the name with postgres_list_tables |
Row count estimate is not available |
Table was never analyzed | Run ANALYZE <table> and retry |
MIT. See LICENSE.