MCP Redshift — Copilot + Entra ID + Propagated Identity

MCP server that integrates Microsoft Copilot, Entra ID, DataZone (catalog), DynamoDB (query templates) and Amazon Redshift, propagating the real user's identity end to end (OBO + native IdP federation).

Documentation

Reference (the "what")

Document	Content
Architecture	Overview, components, Entra ID, Redshift native IdP, AWS infra, risks
Tools Spec	Contracts for the 5 tools, error envelope, security invariants, acceptance criteria
Setup & Local Dev	Setup runbook (Entra/Redshift/Copilot) and dev guide with the real components

Execution (the "how" and "when")

Document	Content
Implementation Roadmap	Development phases, tasks, deliverables and acceptance criteria
ADRs	Architecture decisions (Python version, Docker-only, dependency emulation, stack, driver choice, stateless sessions, Terraform, templates as files, OBO credential, delivery pipeline)
CLAUDE.md	Code conventions: typing, docstrings, tests, coverage

Development principles

1. 100% containerized — no code runs directly on the local machine; build, execution and tests happen via Docker Compose (ADR-0002).
2. Local-first — no access to the real components (Entra, Redshift, DataZone, AWS DynamoDB) at this stage; every external dependency sits behind an interface (port) with a local emulator/fake (ADR-0003).
3. Typing and documentation are mandatory — every function with complete type hints (inputs and output) and a docstring; enforced by mypy --strict in CI.
4. Test coverage as a gate — pytest --cov with a coverage floor that fails the build.

Quick start

Requirements: Docker (with Compose) and git — nothing else.

docker compose up -d postgres dynamodb mock-idp     # start the local emulators
docker compose run --rm test python scripts/seed_dynamodb.py   # seed the templates table
docker compose run --rm test                        # full test suite with coverage gate
docker compose run --rm test sh scripts/ci.sh       # everything CI runs (format, lint, mypy, secrets, tests)

With make installed the same commands are make up, make seed, make test and make ci.

Running the MCP server locally

docker compose up -d --build mcp-server                        # emulators + server on :8080
docker compose run --rm test python scripts/seed_dynamodb.py   # seed templates (dynamodb-local is in-memory)

Every MCP route requires a Bearer JWT — mint one at the mock-idp:

curl -s -X POST http://localhost:8081/mint | jq -r .access_token

Query templates

Templates live in templates/ as YAML and are published to DynamoDB by the pipeline (ADR-0009). Git is the source of truth; the table is a projection of it.

docker compose run --rm test python -m mcp_redshift.template_source check

check needs no AWS and runs in CI: it parses every file, matches declared parameters against the placeholders the SQL binds, applies the catalog denylist and refuses duplicates — so a broken template is a red build rather than a conversation that fails in front of a user. See templates/README.md for the format, the optional-filter pattern and how publishing treats a template the validation job marked broken.

Validating templates against the schema

Stored templates drift as the schema evolves. The validation job parses every template, compares its tables and columns against information_schema and reports what no longer resolves — so the drift is found by an operator instead of by a user mid-conversation.

docker compose run --rm -e VALIDATION_DB_USER=admin -e VALIDATION_DB_PASSWORD=admin test python -m mcp_redshift.validation

It prints a JSON report and exits 1 when it finds drift (the signal a scheduled task turns into an alarm). Add --apply to mark the affected templates broken, which makes them refuse to execute and appear flagged in listings. Templates that start matching the schema again are reported but never reactivated automatically — a template may have been broken on purpose.

The job needs a database user that sees the whole schema (information_schema only exposes what the connected user has rights on) and runs outside the MCP runtime, whose task role has no write access to the template table.

Validating with MCP Inspector

1. npx @modelcontextprotocol/inspector
2. Transport: Streamable HTTP, URL: http://localhost:8080/mcp.
3. Add a custom header Authorization: Bearer <token from /mint>.
4. Connect and run the smoke sequence: search_catalog → search_query_templates → get_query_template → run_query_template (dates like 2026-01-01/2026-12-31). Every response is the standard envelope; executed_as must show aad:user-a@company.com.

Requests without a token (including initialize) are rejected with 401 by design.

Pointing at the real services (phase 7)

Everything above runs against the Docker emulators. Switching to real Entra, Redshift and AWS is a configuration change and nothing else — no code path, no build argument, no import differs (ADR-0003, asserted by tests/unit/test_wiring.py).

cp .env.real.example .env.real   # fill from docs/phase7-values.md
docker compose --profile real up mcp-server-real
docker compose --profile real run --rm test-real

Three variables are omitted from .env.real.example on purpose: MOCK_IDP_URL, CATALOG_FIXTURE_PATH and DYNAMODB_ENDPOINT_URL. Any one of them left set in a real deployment silently restores a local stand-in — including a password connection instead of the user's token, which is identity propagation gone with everything still green.

Current status

All locally-testable phases (0–6) are complete: foundation, domain core, authentication, data layer, execution with the identity-isolation CI gate, the MCP server with the 4 main tools, and the flag-gated ad-hoc query with guardrails.

Everything phases 7 and 8 allowed to be built without access has been built too: the versioned Redshift DDL, the values worksheet and checklist, the self-skipping tests/real/ suite, the Terraform stack with alarms and a dashboard, the deploy pipeline, the template validation job and the operations runbook.

What remains genuinely needs the real thing: applying any of it, confirming token_type on the driver, the real AADSTS surface, DataZone's actual forms shape, and Copilot's refresh behavior past ~75 minutes. See the roadmap and the security review.

To enable the optional 5th tool locally: set ADHOC_ENABLED: "true" in docker-compose.yml and restart mcp-server.