postgres-mcp-lab
Provides read-only access to a PostgreSQL database, enabling relational queries, schema inspection, execution plan analysis, vector similarity search, and graph traversal via Apache AGE.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@postgres-mcp-lab Show me the books table schema and its indexes"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
postgres-mcp-lab
A stdio MCP server that gives Claude Code read-only access to a real PostgreSQL database: relational queries, catalog inspection, execution plans, vector similarity and graph traversal. A database enforces relationships, uses indexes and explains its work; a flat file cannot provide the same execution model.
The repository contains six generic tools and an invented public-library dataset. No embedding account or API key is needed for the default demo. Claude Code requires its usual authentication and connectivity.
Quickstart
Requirements: Docker with Compose v2, Git, and enough disk space to compile a PostgreSQL extension. From the repository root:
cp .env.example .env
docker compose upThe first build downloads dependencies and compiles Apache AGE; allow several minutes. Leave the terminal open. Initialization runs automatically on an empty volume. The MCP container has no HTTP endpoint: it waits for a client on standard input/output.
Database image
db/Dockerfile starts from pgvector/pgvector:pg16 and compiles AGE from its release/PG16/1.5.0 release branch. This avoids attempting package installation from an init SQL script. AGE is preloaded in every backend, so the restricted role does not need permission to execute LOAD. The build argument AGE_REF can select an audited compatible revision.
The three SQL files are copied into the inherited docker-entrypoint-initdb.d directory at image build time. The PostgreSQL entrypoint executes them in filename order. This baked-in alternative to a bind mount needs no host-specific path. Rebuild the image after editing init files.
Initialization is not a migration system: it only runs on an empty database volume. The base image provides an anonymous data volume. docker compose down --volumes destroys the demo database; the next start initializes it again. Build tools remain in the database image for simplicity. The health check uses TCP and the reader role so it waits for the final database server, not the temporary initialization server.
Related MCP server: Argos-MCP
Connect Claude Code
In a second terminal, register the server from the repository root:
claude mcp add --transport stdio --scope project postgres-lab -- docker compose --project-directory . run --rm -T --no-deps mcpAlternatively, merge examples/claude_code_mcp.json into the project MCP configuration. Start Claude Code from this repository so that the relative Compose directory resolves correctly. Keep the database running before connecting.
docker compose run creates a separate stdio process for each MCP client; it does not attach to the idle MCP container started by up. Never use docker compose up as the stdio command: its service logs would corrupt the protocol. Only MCP messages go to stdout; generic diagnostics go to stderr.
Architecture
See docs/architecture.md for the component diagram, the five-layer read-only chain, and the entity diagram of the demo dataset.
Tools
All tools return JSON text. Failures return MCP isError: true. Vector and graph identifiers must be simple ASCII names of at most 63 characters.
Tool | Parameters | Behavior |
|
| One SELECT or read-only WITH, positional scalar parameters, rows, column types and truncation flag. No trailing semicolon. |
|
| Catalog inspection, one kind per call. Filter by schema to avoid catalog noise; omit table for schemas. |
|
| JSON plan of the original query without ANALYZE. |
|
| pgvector cosine nearest neighbors; includes similarity and provider metadata. |
|
| Read-only AGE traversal with an explicit output signature. |
| none | Connection status, PostgreSQL version, read-only settings, timeout and vector/AGE versions. |
limit and k cannot exceed MAX_ROWS; timeout_ms can only lower the configured timeout. SQL parameter values are strings, finite numbers, booleans or null. Cast JSON strings inside SQL when needed. AGE values retain their textual representation rather than undergoing guessed JSON conversion.
Two-minute demonstration
Ask these five questions in Claude Code and permit the read-only calls:
“Check the connection and extensions. Inspect the demo schema, including columns, indexes and foreign keys.”
healthverifies the connection;schemaruns once per kind. Expect read-only settingson, vector and AGE versions, two relational tables, a foreign key and an HNSW index.“How many available copies are on each shelf? Use a parameterized query to include shelves with at least three copies.”
queryjoinsdemo.shelvesanddemo.books, groups by shelf and binds the threshold as$1. Totals are 10, 7 and 4.“Explain the plan for finding books on shelf 1 without executing the query. Is an index necessarily useful on ten rows?”
explainplans a SELECT withparams: [1]. A sequential scan is reasonable for this tiny dataset; an index is not guaranteed to be chosen.“Use vector search on demo.books for the three closest descriptions to ‘forest trees nature’. Return id, title and description, using embedding as the vector column.”
semantichashes the query into 64 dimensions and orders by pgvector cosine distance. Forest-related books should rank near the top. This is token overlap, not language understanding.“In the reading graph, which books point to the Forest topic through ABOUT? Return their titles using Cypher.”
cyphertraverses the graph. Expect The Moss Compass, Small Wings at Dusk and Seeds Under Glass; order is unspecified.
A concrete graph call:
{
"graph": "reading",
"query": "MATCH (b:Book)-[:ABOUT]->(t:Topic) WHERE t.name = 'Forest' RETURN b.title",
"columns": ["title"],
"limit": 10
}The seed contains ten invented books, three shelves, three topics and ABOUT/RELATED_TO edges. Graph book IDs match relational IDs. No real people or records are represented.
Embeddings
EMBEDDING_PROVIDER=hash lowercases ASCII tokens, computes a polynomial hash modulo 2147483647, projects token counts into N buckets and L2-normalizes the vector. src/embedding.ts and demo.hash_embedding implement the same specification. Seed vectors encode description only, in 64 dimensions. Floating-point storage differences are expected.
This is a deterministic demonstration encoder, not a semantic model. It has collisions, no synonym understanding and no multilingual representations. Non-ASCII characters act as token separators; empty token input is rejected. After dependencies and images are downloaded, the hash encoder and database require no network access or API key.
For a real provider:
Set
EMBEDDING_PROVIDER=openai,OPENAI_API_KEY,OPENAI_EMBEDDING_MODEL,OPENAI_EMBEDDING_URLandEMBEDDING_DIMENSIONSin your private environment.Use a separate privileged ingestion process to regenerate all stored vectors with the same provider, model, input convention and dimensions. Change the column dimension and rebuild its index if necessary.
Point
semanticat that table. This server intentionally provides no ingestion or write tool.
Never compare OpenAI query vectors to the seed hash vectors, even when dimensions match. The endpoint must support the OpenAI embeddings request shape, including dimensions. HTTPS is required and redirects are refused. Query text leaves the machine with this provider; table vectors and rows are not sent by the embedding module.
Configuration
All runtime configuration is environmental; see .env.example. Compose loads .env for the MCP service. Native Node processes require exported variables or --env-file.
Variables | Default / purpose |
| db, 5432, library, reader, empty password; separate fields, no database URL. |
| disable for the isolated demo; verify-full for verified TLS, optional PEM CA content. |
| 4 connections, 5000 ms. |
| 5000 ms; configurable range 100–30000. |
| 100 rows (configurable maximum 1000), 65536 bytes. |
| hash, 64; dimensions 8–2000. |
| 10000 ms. |
| Empty key, public embeddings endpoint, text-embedding-3-small. |
For another database, configure a separately provisioned least-privileged role. query, schema, explain and health work without extensions; absent extension versions are null. Vector search currently expects pgvector in public. AGE must already be loaded in each connection, for example by administrator-configured preloading.
Security
The safeguards are visible in src/guard.ts, src/db.ts and the initialization SQL:
Every pool connection starts with
default_transaction_read_only=on, statement and idle-transaction timeouts, a fixed search path and standard-conforming strings.Every operation uses
BEGIN TRANSACTION READ ONLYand always rolls back. Failed rollback destroys the connection.Conservative lexical guards reject write keywords, comments, semicolons, multiple statements, backslashes, dollar quoting and selected privileged functions. SQL must start with SELECT/WITH; Cypher must use read-oriented clauses. Bind SQL values rather than embedding literals.
Parameter values are bound; identifiers are validated and quoted. AGE requires literal query text: it is validated and single quotes are escaped. Explicit output columns avoid fragile RETURN parsing.
An outer row limit cannot be bypassed by an inner LIMIT or CTE. One additional row detects truncation. Oversized serialized tool payloads are rejected, reserving space for the MCP envelope. Explain returns one plan document subject to the response-size budget.
Raw database/network/provider errors and environment values are never forwarded. There is no database-backed telemetry or connection-string output.
The reader is not a superuser or owner, has no role memberships, TEMP or CREATE rights, and only SELECT on demo and graph tables. AGE graph DDL helpers are not executable by the reader; traversal still requires extension function execution privileges.
These are defense-in-depth controls, not a sandbox for arbitrary hostile SQL. PostgreSQL read-only mode does not prevent every effect of user-defined functions, extensions, network calls, advisory locks or resource consumption. Audit executable routines, SECURITY DEFINER functions, extension permissions and role memberships before connecting any non-demo database. Never use superuser credentials. MCP read-only annotations are descriptive, not enforcement.
The demo uses passwordless trust authentication to avoid shipping credentials. Its database network is internal, with no published database port. Anyone controlling Docker or another container on that network can impersonate the bootstrap role. This is for a disposable local demo, not a shared host or production deployment. Use SCRAM, network policy, verified TLS and an independently provisioned reader elsewhere.
Read-only access can disclose everything the role can read. Limit grants and review results before sharing them. Treat database text as untrusted data, never as instructions to Claude.
Development and verification
npm install
npm run build
npm testNode.js 22 is required. npm run dev uses tsx and exported variables. For native execution with an environment file:
node --env-file=.env dist/index.jsThe hostname db resolves inside Compose, not from the host shell. Configure a reachable database for native development.
test/guards.test.ts exercises lexical rejection, escaping and the hash specification. test/database.test.ts is an opt-in integration suite: export TEST_DATABASE=1 and connection variables for a freshly seeded demo database, then run npm test. It checks all six tools, encoder parity, row limits, response limits and timeout recovery. It assumes hash/64 and the default response budget. Without the opt-in, this suite is skipped.
Before publishing, run a clean Docker build, the integration suite and all five Claude prompts. Independently inspect privileges and confirm that direct INSERT as reader fails, not merely the MCP guard. Test write CTEs, multiple statements, Cypher CREATE and attempts to change transaction settings. Record versions and results.
No build or integration run is claimed here. Generate and commit a real npm lockfile after verification, then use npm ci in Docker. Pin image digests and the AGE commit for reproducible releases; current branches, tags and npm ranges are not immutable.
Limits
Stdio only: no HTTP service, remote authentication layer or migrations.
Lexical guards are not complete SQL/Cypher parsers and may reject harmless words in literals or identifiers. Unusual quoted identifiers are unsupported in vector/graph arguments.
Row and response limits bound delivery, not peak memory or database work. Large fields and aggregates may be materialized before rejection; apply database resource controls to untrusted workloads.
Timeouts apply per statement, not as a total tool deadline. Embedding requests have a separate deadline.
PostgreSQL catalog visibility can exceed table-data visibility. AGE compatibility and permissions need testing against the selected versions and CPU architecture.
HNSW is approximate; ten rows do not demonstrate index performance. Ingestion and reindexing remain administrative operations.
License
Apache License 2.0. Copyright 2026 Christian Verbrugge. See LICENSE and NOTICE. Dependencies and database images retain their own licenses.
Available Tools
6 toolscypherARead-onlyIdempotent
Run read-only Apache AGE Cypher. Supply graph and explicit output column names.
Inputs: graph (required), query (required), columns (required), limit, +1 more.
explainARead-onlyIdempotent
Return a JSON execution plan without ANALYZE; the query is not executed.
Inputs: sql (required), params, timeout_ms.
healthARead-onlyIdempotent
Check PostgreSQL connectivity, read-only settings and installed vector/age versions.
Inputs: no named parameters.
queryARead-onlyIdempotent
Run a parameterized read-only SELECT with server-enforced row and time limits.
Inputs: sql (required), limit, params, timeout_ms.
schemaCRead-onlyIdempotent
Inspect schemas, tables, columns, indexes or foreign keys. Repeat for each kind.
Inputs: kind (required), limit, table, schema.
semanticARead-onlyIdempotent
Rank rows using pgvector cosine distance. Default hash encoding measures token overlap, not meaning.
Inputs: text (required), table (required), schema (required), columns (required), +2 more.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 6 tools
Each tool targets a clearly distinct operation: generic SQL, schema inspection, query planning, vector similarity, Cypher graph queries, and health checks. There is little risk of selecting the wrong tool for a given task.
All tool names are single lowercase words, giving a clean and consistent style. However, they mix verbs and nouns (query/explain vs schema/health) rather than following a strict verb_noun pattern.
Six tools is well-scoped for a PostgreSQL lab server covering SQL, schema, plans, vector search, graph queries, and health. Each tool earns its place without redundancy.
The read-only domain is fully covered: arbitrary SQL, schema inspection, execution plans, vector ranking, graph querying, and environment health. No obvious dead ends or missing core operations for the stated purpose.
Maintenance
Related MCP Connectors
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query your Postgres from ChatGPT or Claude without exposing the database or handing over credentials. Run npx boltschema connect next to your database and it dials out over HTTPS — no inbound firewall rule, no open port, works with localhost and VPC-private databases. Read-only is enforced by a SQL guard, a Postgres READ ONLY transaction, and a scoped role generated for you.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Related MCP Servers
- AlicenseAqualityBmaintenanceQuery and manage PostgreSQL databases from Claude Code, Cursor, and any MCP client, with read-only by default and built-in schema introspection, EXPLAIN, and performance diagnostics.231,124 npm10MIT
- AlicenseNot gradedqualityBmaintenanceEnables Claude Code to securely interact with PostgreSQL, MySQL, SQLite, and SQL Server databases, featuring read-only mode, query validation, SSH tunneling, and field redaction for production-safe data access.4MIT
- AlicenseAqualityCmaintenanceEnables Claude Desktop and Claude Code to inspect a Postgres schema and run read-only SQL queries against a demo database through the tools describe_schema and query_readonly. Every proposed query passes the same deterministic guardrails — SELECT-only, allow-listed tables, enforced row limits, statement timeouts and a read-only role — before execution.2MIT
- AlicenseAqualityCmaintenanceEnables Claude to answer questions from PostgreSQL databases using read-only SQL queries, with row caps, column masking, and an audit log while preventing data changes.3MIT