# 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:

```sh
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.

## Connect Claude Code

In a second terminal, register the server from the repository root:

```sh
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](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:

```json
{
  "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

```sh
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:

```sh
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.