Skip to main content
Glama
postgres-mcp-lab

postgres-mcp-lab

by PlbKin190

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 up

The 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 mcp

Alternatively, 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

query

sql, params=[], limit?, timeout_ms?

One SELECT or read-only WITH, positional scalar parameters, rows, column types and truncation flag. No trailing semicolon.

schema

kind: schemas, tables, columns, indexes or foreign_keys; schema?, table?, limit?

Catalog inspection, one kind per call. Filter by schema to avoid catalog noise; omit table for schemas.

explain

sql, params=[], timeout_ms?

JSON plan of the original query without ANALYZE.

semantic

schema, table, vector_column, columns (1–20 names), text, k=5

pgvector cosine nearest neighbors; includes similarity and provider metadata.

cypher

graph, query, columns (names in RETURN order), limit?, timeout_ms?

Read-only AGE traversal with an explicit output signature.

health

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:

  1. “Check the connection and extensions. Inspect the demo schema, including columns, indexes and foreign keys.” health verifies the connection; schema runs once per kind. Expect read-only settings on, vector and AGE versions, two relational tables, a foreign key and an HNSW index.

  2. “How many available copies are on each shelf? Use a parameterized query to include shelves with at least three copies.” query joins demo.shelves and demo.books, groups by shelf and binds the threshold as $1. Totals are 10, 7 and 4.

  3. “Explain the plan for finding books on shelf 1 without executing the query. Is an index necessarily useful on ten rows?” explain plans a SELECT with params: [1]. A sequential scan is reasonable for this tiny dataset; an index is not guaranteed to be chosen.

  4. “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.” semantic hashes 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.

  5. “In the reading graph, which books point to the Forest topic through ABOUT? Return their titles using Cypher.” cypher traverses 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:

  1. Set EMBEDDING_PROVIDER=openai, OPENAI_API_KEY, OPENAI_EMBEDDING_MODEL, OPENAI_EMBEDDING_URL and EMBEDDING_DIMENSIONS in your private environment.

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

  3. Point semantic at 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

PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD

db, 5432, library, reader, empty password; separate fields, no database URL.

PGSSL, PGSSL_CA

disable for the isolated demo; verify-full for verified TLS, optional PEM CA content.

POOL_MAX, CONNECT_TIMEOUT_MS

4 connections, 5000 ms.

STATEMENT_TIMEOUT_MS

5000 ms; configurable range 100–30000.

MAX_ROWS, MAX_RESPONSE_BYTES

100 rows (configurable maximum 1000), 65536 bytes.

EMBEDDING_PROVIDER, EMBEDDING_DIMENSIONS

hash, 64; dimensions 8–2000.

EMBEDDING_TIMEOUT_MS

10000 ms.

OPENAI_API_KEY, OPENAI_EMBEDDING_URL, OPENAI_EMBEDDING_MODEL

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 ONLY and 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 test

Node.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.js

The 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 tools
cypherA
Read-onlyIdempotent

Run read-only Apache AGE Cypher. Supply graph and explicit output column names.

Inputs: graph (required), query (required), columns (required), limit, +1 more.

explainA
Read-onlyIdempotent

Return a JSON execution plan without ANALYZE; the query is not executed.

Inputs: sql (required), params, timeout_ms.

healthA
Read-onlyIdempotent

Check PostgreSQL connectivity, read-only settings and installed vector/age versions.

Inputs: no named parameters.

queryA
Read-onlyIdempotent

Run a parameterized read-only SELECT with server-enforced row and time limits.

Inputs: sql (required), limit, params, timeout_ms.

schemaC
Read-onlyIdempotent

Inspect schemas, tables, columns, indexes or foreign keys. Repeat for each kind.

Inputs: kind (required), limit, table, schema.

semanticA
Read-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

    A3.8/5.0

    Scored across 6 tools

    Disambiguation5/5

    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.

    Naming Consistency4/5

    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.

    Tool Count5/5

    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.

    Completeness5/5

    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

    ActivityStale
    ResponsivenessOutdated data

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      B
      maintenance
      Query 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.
      23
      1,124 npm
      10
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Enables 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.
      4
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Enables 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.
      2
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Enables 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.
      3
      MIT