Epic: Security improvement roadmap (MCP + general hardening)

[jrosskopf]

Context

A vendor pitching an external "runtime security gateway for MCP agents" (inspecting tool definitions, tool-call arguments, RBAC, and responses) prompted a review of flAPI's own posture. Exposing data warehouses to AI agents over MCP is genuinely high-risk, and a code audit confirms several gaps worth closing in-product rather than punting to an external sidecar.

This epic tracks a prioritized roadmap covering both general and MCP-specific security, MCP first, under a guiding constraint: simple things must stay simple. Defaults remain safe; hardening is opt-in for production but never required for flapii project init demos to work.

Current state (audit summary)

Area	Status
MCP authentication (JWT/Basic/OIDC, method-level)	Implemented
MCP input validation (reuses REST RequestValidator)	Implemented
MCP rate limiting (inherited from middleware stack)	Implemented
MCP tool exposure (opt-in via mcp-tool:)	Implemented
MCP per-tool RBAC	Gap
MCP per-call audit log	Gap
MCP response shaping / column redaction	Gap
MCP dry-run / shadow mode	Gap
Tool-description sanitization	Gap
Basic-auth password hashing (MD5 or plaintext)	Broken
CORS (wildcard, not config-driven)	Gap
SQL-injection defense (regex validator + Mustache; misleading docs)	Weak
Generic REST audit log	Gap (audit infra exists only for DuckLake)
TLS in embedded server (schema present, not wired)	Partial
Per-user rate limit (per-IP only today)	Gap
Secrets in logs (JWT secrets, DB passwords leak at DEBUG)	Gap

Guiding principles

1. Safe defaults, simple opt-in for production hardening. Fresh flapii project init must still work without auth config.
2. No breaking config changes without a deprecation warning at startup.
3. MCP-first ordering — blast radius of an unrestricted agent > misconfigured REST endpoint.
4. Build the inspection points the vendor sells, into flAPI itself — per-tool RBAC + per-call audit + dry-run need access to the tool registry and result shape, which a sidecar cannot see without re-parsing config.

Roadmap (waves tracked as child issues)

• Wave 0 — Honest defaults & honest docs (low effort, ship first) — Security Wave 0: Honest defaults & honest docs #22
• Wave 1 — Quick production wins (bcrypt, CORS allowlist, audit log, per-user rate limit) — Security Wave 1: Quick production wins (bcrypt, CORS allowlist, audit log, per-user rate limit) #23
• Wave 2 — MCP hardening (per-tool RBAC, dry-run, response shaping, description hygiene) — directly answers the vendor pitch — Security Wave 2: MCP hardening (per-tool RBAC, dry-run, response shaping, description hygiene) #24
• Wave 3 — Architectural (prepared statements, TLS, statement-shape validator) — schedule deliberately — Security Wave 3: Architectural (prepared statements, TLS, statement-shape validator) #25

Mapping to the vendor pitch

The pitch — "shadow-mode audit and query inspection before production" — maps almost 1:1 onto:

• Tool-definition inspection → W2.3 (description hygiene at load time)
• Tool-call argument inspection → already implemented via RequestValidator; extended by W2.1 (RBAC) and W1.3 (audit)
• RBAC → W2.1 (per-tool roles)
• Response inspection → W2.4 (response shaping)
• Shadow mode → W2.2 (dry-run)
• Pre-production audit → W1.3 (JSONL audit log) + W2.2 dry-run for batch validation

Out of scope

• Building our own MCP gateway/sidecar (defeats single-binary value prop)
• OAuth2/OIDC provider (already supported as a client; not adding flAPI-as-IdP)
• Per-row authorization (covered today by SQL templates with auth.roles in context — documentation improvement instead)
• AWS KMS envelope encryption for config secrets (low ROI vs. recommending IAM roles)