io.github.kalehdoo/warehouse-mcp
Production MCP server for Postgres, Oracle, Snowflake, BigQuery, Redshift, DuckDB, MotherDuck.
Open source Open in the app JSON README (API)
About
Production MCP server for Postgres, Oracle, Snowflake, BigQuery, Redshift, DuckDB, MotherDuck.
Details
- Kind
- MCP servers
- Topic
- Databases
- Publisher
- kalehdoo
- Origin
- official
- Category
- ferramentas
- Transport
- local
- Version
- 0.4.2
- Last push
- 2026-06-18T21:31:38Z
- Repository state
- ativo
- Language
- JavaScript
- License
- Apache-2.0
- Added
- 2026-08-29 04:00:18
- Updated
- 2026-08-29 04:00:18
- Origin id
io.github.kalehdoo/warehouse-mcp
README
# warehouse-mcp
[](https://github.com/kalehdoo/warehouse-mcp/actions/workflows/ci.yml)
[](https://github.com/kalehdoo/warehouse-mcp/blob/main/LICENSE)
Production MCP (Model Context Protocol) server for your data warehouse. Read-only enforcement, five-tier role-based access, optional warehouse-role impersonation (for native RLS / CLS), JSONL audit log, optional output PII masking, optional semantic-metadata layer (glossary + table docs) with per-session toggle. Self-host the Docker image, install via npx, or wait for the upcoming managed cloud variant.
> **Status:** v0.4.x — production-ready for the v1 warehouse list. See [CHANGELOG](https://github.com/kalehdoo/warehouse-mcp/blob/main/CHANGELOG.md) for what shipped in each release.
## Demo Videos
[Warehouse MCP in action using Cursor](https://youtu.be/DGgI7cczEtk)
## Supported warehouses
| Warehouse | Adapter docs |
|---|---|
| Postgres 12+ | [docs/adapters/postgres.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/postgres.md) |
| Oracle 12c+ (Thin mode, no Instant Client) | [docs/adapters/oracle.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/oracle.md) |
| Amazon Redshift (cluster + Serverless) | [docs/adapters/redshift.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/redshift.md) |
| Snowflake (key-pair auth) | [docs/adapters/snowflake.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/snowflake.md) |
| Google BigQuery | [docs/adapters/bigquery.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/bigquery.md) |
| DuckDB (local file or `:memory:`) | [docs/adapters/duckdb.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/duckdb.md) |
| MotherDuck (cloud-hosted DuckDB, `md:` paths) | [docs/adapters/duckdb.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/adapters/duckdb.md#motherduck-cloud-hosted-duckdb) |
Databricks SQL is a fast-follow.
## Tools exposed (16, all read-only)
| Tool | Purpose |
|---|---|
| `query` | Execute a SELECT (validator-enforced read-only, dialect-aware) |
| `list_schemas`, `list_tables`, `describe_table` | Browse the catalog |
| `find_columns` | Search column names across the warehouse with a LIKE pattern |
| `get_foreign_keys` | Discover declared FK relationships for safe joins |
| `get_view_definition` | Read the SQL body of a view (where business logic lives) |
| `sample_table`, `count_rows` | Peek at data, check size before scanning |
| `column_stats`, `top_values` | Profile a single column |
| `time_series` | Bucket by hour/day/week/month/quarter/year — dialect-correct everywhere |
| `search_value` | Find a literal across a table's text columns |
| `glossary_lookup` | Read business-glossary terms from the semantic layer (in-memory, no warehouse I/O) |
| `schema_lookup` | Read schema-level docs — purpose, owner, refresh, table list |
| `table_lookup` | Read full semantic doc for one table — description + column metadata |
## Roles (five tiers)
| Role | Tools allowed |
|---|---|
| `semantic_only` | Only the three semantic-lookup tools — zero warehouse access; pair with `semantic=on` for a docs-viewer persona |
| `metadata_only` | Catalog discovery + semantic lookups — never reads row data |
| `reader_restricted` | Aggregates / samples / time series — no arbitrary SELECT |
| `reader` | Adds `query` and `search_value` (the general analyst tier) |
| `admin` | Everything; future write tools when `ENABLE_WRITE_TOOLS` ships |
Per-key role assigned via `MCP_API_KEYS=key:role[:set_role=warehouse_role][:semantic=on|off]`. The optional `set_role=` directive issues `SET ROLE` on Postgres/Redshift so the warehouse's own RLS / CLS / masking policies enforce per-key access — no policy duplication in MCP. The optional `semantic=on|off` directive overrides `SEMANTIC_DEFAULT` for that one key, controlling whether the session sees the `warehouse://semantic/*` resources (see [docs/semantic-metadata.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/semantic-metadata.md#toggling-semantic-per-session)).
For deployments with multiple existing DB roles (finance, hr, payroll, etc.) and many human users, see [docs/multi-role-deployment.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/multi-role-deployment.md) — walks through mapping ~10 DB roles to MCP keys, the recommended `<area>` / `<area>_restricted` pattern, and when to graduate from static keys to OIDC.
## Sample mcp connection json(change based on your role):
{
"mcpServers": {
"warehouse-admin-semanticon": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer admin-key-change-me"
]
},
"warehouse-admin-semanticoff": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer admin-key-nosemantic"
]
},
"warehouse-reader-semanticon": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer reader-key-change-me"
]
},
"warehouse-reader-semanticoff": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer reader-key-nosemantic"
]
},
"warehouse-reader-restricted-semanticon": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer restricted-key-change-me"
]
},
"warehouse-reader-restricted-semanticoff": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer restricted-key-nosemantic"
]
},
"warehouse-metadata-only-semanticon": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer metadata-only-key-change-me"
]
},
"warehouse-metadata-only-semanticoff": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer metadata-only-key-nosemantic"
]
},
"warehouse-docs-viewer-only": {
"command": "npx",
"args": [
"-y",
"mcp-remote@latest",
"http://localhost:3001/mcp",
"--header",
"Authorization: Bearer docs_viewer_key_change_me"
]
}
}
}
## Quick start
### Option A — Docker compose (5-minute demo with seeded Postgres)
```bash
git clone https://github.com/kalehdoo/warehouse-mcp.git
cd warehouse-mcp
docker compose up
# server on http://localhost:3001, seeded ecommerce data in Postgres
```
### Option B — Docker against your own warehouse
The same image bundles every adapter; pick one with `WAREHOUSE_TYPE` plus the matching credentials. For credentials, prefer `--env-file` (or your secrets manager) over inline `-e` flags so passwords don't end up in shell history.
```bash
# Postgres (REDSHIFT_* env vars for Redshift; same driver under the hood)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=postgres \
-e PG_HOST=db -e PG_DATABASE=analytics -e PG_USER=mcp_reader -e PG_PASSWORD=... \
-e MCP_API_KEYS="$(openssl rand -hex 24):reader" \
ghcr.io/kalehdoo/warehouse-mcp:latest
# Oracle (Thin mode, no Instant Client)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=oracle \
-e ORACLE_USER=MCP_READER -e ORACLE_PASSWORD=... \
-e ORACLE_CONNECT_STRING="db.host:1521/SERVICE" \
ghcr.io/kalehdoo/warehouse-mcp:latest
# Snowflake (key-pair, mount the .p8)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=snowflake \
-e SNOWFLAKE_ACCOUNT=xy12345.us-east-1 -e SNOWFLAKE_USER=MCP_READER \
-e SNOWFLAKE_PRIVATE_KEY_PATH=/keys/snowflake.p8 \
-e SNOWFLAKE_WAREHOUSE=COMPUTE_WH -e SNOWFLAKE_DATABASE=ANALYTICS \
-v /opt/keys:/keys:ro \
ghcr.io/kalehdoo/warehouse-mcp:latest
# BigQuery (mount the service-account JSON)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=bigquery \
-e GOOGLE_APPLICATION_CREDENTIALS=/keys/bq-sa.json \
-e BIGQUERY_PROJECT=my-gcp-project \
-v /opt/keys:/keys:ro \
ghcr.io/kalehdoo/warehouse-mcp:latest
# DuckDB (file or in-memory)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=duckdb -e DUCKDB_PATH=:memory: \
ghcr.io/kalehdoo/warehouse-mcp:latest
# MotherDuck (hosted DuckDB)
docker run -d -p 3001:3001 \
-e WAREHOUSE_TYPE=duckdb -e DUCKDB_PATH=md:my_database \
-e MOTHERDUCK_TOKEN=... \
ghcr.io/kalehdoo/warehouse-mcp:latest
```
### Option C — npx (no container)
```bash
npx warehouse-mcp@latest init # interactive setup; writes .env, prints Claude Desktop snippet
npx warehouse-mcp doctor # verify the connection without booting the server
npx warehouse-mcp start # bind the MCP server to MCP_SERVER_PORT (default 3001)
```
Then point an AI client at it. Drop-in configs:
- [Claude Desktop](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/install-claude-desktop.md)
- [Cursor](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/install-cursor.md)
- [Docker (production)](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/deploy-docker.md)
- [Kubernetes](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/deploy-kubernetes.md)
For a step-by-step walkthrough from "I have a warehouse" to "Claude is querying it", see the [onboarding guide](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/onboarding.md). For common errors, see [troubleshooting](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/troubleshooting.md).
## Local development
```bash
nvm use # Node 20
npm install
npm test # unit tests, sub-second
npm run lint
cp .env.example .env
```
For testcontainers integration tests against real Postgres: `npm run test:integration` (Docker required).
## env setup
1. MCP_API_KEYS
2. WAREHOUSE_TYPE
3. SEMANTIC_DIR
## Optional: Semantic metadata (v0.4+)
Teach the AI agent **what your warehouse means**, not just what's in it. Drop YAML files describing your business glossary and table semantics into a directory, point `SEMANTIC_DIR` at it, and the agent gets two access channels into the same in-memory index:
- **MCP resources** at `warehouse://semantic/*` — what tool-aware clients (Cursor, MCP Inspector) read proactively before issuing queries.
- **MCP tools** `glossary_lookup`, `schema_lookup`, `table_lookup` — same data via the tool channel, for clients (Claude Desktop) whose UI is tool-centric and may suppress resource-only servers. Pure in-memory `Map.get`s — no warehouse I/O, no audit cost beyond a normal tool log line.
Format follows dbt's `schema.yml` v2 with one extension (`meta.schema:` per model). Customers using dbt can point `SEMANTIC_DIR` at their existing `models/` directory and reuse most of what they have. See [docs/semantic-metadata.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/semantic-metadata.md) and the starter [docs/semantic-templates/](https://github.com/kalehdoo/warehouse-mcp/tree/main/docs/semantic-templates).
### Per-session toggle
Whether a session sees the semantic layer is independent of whether the YAMLs are loaded — `SEMANTIC_DIR` controls loading; `SEMANTIC_DEFAULT=on|off` and the per-key `semantic=on|off` option (or `include_semantic` JWT claim) control per-session visibility. The YAMLs are always loaded and validated at boot so you can flip a kill-switch without restarting. Three precedence layers, highest first:
1. **Per-JWT claim** — `include_semantic: true|false` (OIDC).
2. **Per-API-key option** — `semantic=on|off` in `MCP_API_KEYS`.
3. **Server default** — `SEMANTIC_DEFAULT=on|off` (default `on`).
Each tool-call audit row records the resolved `include_semantic` value so you can correlate query quality with semantic exposure after the fact.
## Optional: OpenTelemetry tracing
Off by default. Set `OTEL_EXPORTER_OTLP_ENDPOINT=http://otel-collector:4318/v1/traces` in your env and the server will emit per-tool-call spans. Resource attributes: `service.name=warehouse-mcp`, `service.version=<package.json version>`. Span attributes: `warehouse.tenant`, `warehouse.role`. Works with any OTLP/HTTP backend (Grafana Tempo, Honeycomb, Datadog APM, New Relic, SigNoz).
## Optional: Output PII masking
Off by default. Set `GUARDRAIL_PII_MASK=on` and the server masks emails, SSNs, formatted phones, IPv4 addresses, and Luhn-validated credit cards in result rows. Mask level depends on the caller's role: `admin` sees raw, `reader` sees partial (`a***@example.com`), `reader_restricted` sees full redaction tags.
## How it works
Read [the architecture doc](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/architecture.md) for the request flow — what files execute when an MCP client makes a call, how the guardrail pipeline composes around the tool handler, and the recipes for adding a new tool / guardrail / adapter. Single-page orientation for operators, security reviewers, and contributors.
## Security
Read [the threat model](https://github.com/kalehdoo/warehouse-mcp/blob/main/docs/threat-model.md) before deploying. It covers the OWASP Top 10 mapping, what the codebase mitigates, and what is left to your deployment (TLS, secrets management, network isolation, cost guardrails). Report vulnerabilities per [SECURITY.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/SECURITY.md).
## Contributing
Issues and PRs welcome. Start with [CONTRIBUTING.md](https://github.com/kalehdoo/warehouse-mcp/blob/main/CONTRIBUTING.md) — it covers the dev workflow, the adapter contract, and how to add a new warehouse. By participating you agree to the [Code of Conduct](https://github.com/kalehdoo/warehouse-mcp/blob/main/CODE_OF_CONDUCT.md).
## License
Apache-2.0