dbbaskette/gp-mcp-server

🛡️ Greenplum MCP Server

🧭 Table of Contents

1. Why Teams Use It
2. Feature Spotlight
3. Front-End Control Center
4. Architecture Map
5. MCP Tools & APIs
6. Run It Now
7. Configuration Vault
8. Observability Radar
9. Security Stance
10. Development Flow
11. Further Reading
12. License & Credits

✨ Why Teams Use It

• 🔐 Multi-tenant guardrails — Every API key maps to a distinct Greenplum role with credentials encrypted via AES-256-GCM.
• 🧠 Smart SQL tooling — Policy-aware query execution, EXPLAIN plans, schema exploration, and cursor streaming through Spring AI MCP.
• ⚡ Ops friendly — HikariCP per-tenant pools, Prometheus and OpenTelemetry hooks, health endpoints, and structured audit logs out of the box.
• 🧱 Production ready — Built on Spring Boot 3.5, tested against Greenplum/Postgres targets, and configurable entirely via environment variables.
• 🗝️ Zero-leak posture — Secrets live in .env, the repo ships with templates only, and client guides reference placeholders instead of live values.

🚀 Feature Spotlight

🧰 MCP Toolbelt

🧮 Platform Superpowers

• Per-key pools — DatabaseConnectionManager spins up isolated HikariCP pools so noisy neighbors stay contained.
• Policy enforcement — policy.yml governs max rows, byte budgets, schema/table allow lists, and redaction rules.
• SQL inspection — SchemaTools + JSQLParser prevent mutations, multi-statements, and disallowed functions.
• Streaming transport — Implements the MCP Streamable HTTP spec for responsive LLM tool usage.

🎨 Front-End Control Center

The admin console at /admin/api-keys ships as a polished single-file experience (src/main/resources/templates/api-keys.html). It is intentionally framework-light but far from plain.

UI Stack at a Glance

Experience Touches

• Connection dry-runs — The "Test Connection" button posts to /admin/api-keys/test-connection before any credential is minted.
• Guarded secrets — Inline banners warn that generated keys display once; the layout encourages instant copy & storage.
• Responsive grid — CSS grid collapses to a single column below 600px for tablet administration on the go.
• Zero build step — Everything lives in one HTML file; changes deploy with your next Spring Boot restart.

🏗️ Architecture Map

flowchart LR
  subgraph Client["🧑‍💻 MCP Clients" ]
    Tooling1[API Key 1]
    Tooling2[API Key 2]
  end

  subgraph Gateway["☕ Spring Boot 3.5"]
    Security[Spring Security<br/>API Key Filter]
    MCPServer[MCP Tool Controller]
    PolicySvc[Policy Service]
    SQLGuard[SQL Validator]
    QueryOps[Query Tools]
    KeyVault[Credential Vault]
  end

  subgraph Data["🗄️ Greenplum / Postgres"]
    Pool1[HikariCP Pool ↔ User A]
    Pool2[HikariCP Pool ↔ User B]
  end

  subgraph Telemetry["📡 Observability"]
    OTEL[OpenTelemetry]
    Metrics[Prometheus Metrics]
    Logs[Structured Logging]
  end

  Client -->|Bearer Key| Security
  Security --> MCPServer
  MCPServer --> PolicySvc
  MCPServer --> SQLGuard
  MCPServer --> QueryOps
  QueryOps --> Pool1
  QueryOps --> Pool2
  Pool1 --> Data
  Pool2 --> Data
  KeyVault --> Security
  QueryOps --> Telemetry
  PolicySvc --> Telemetry

🔌 MCP Tools & APIs

• Streamable HTTP Endpoint: POST /mcp (configurable via application.yml).
• Admin Console Endpoints:
  • POST /admin/api-keys/generate
  • POST /admin/api-keys/test-connection
  • GET /admin/api-keys (serves the console)
• Actuator Surface: /actuator/health, /actuator/prometheus, /actuator/info for ops integrations.

Pair this server with any MCP-aware client (e.g., LaunchDarkly MCP Inspector, OpenAI assistants, or the sibling ).

⚙️ Run It Now

# 1) Clone
git clone https://github.com/dbbaskette/gp-mcp-server.git
cd gp-mcp-server

# 2) Generate an encryption key for API credential storage
openssl rand -base64 32

# 3) Create your .env (never commit this file)
cat > .env <<'ENV'
export DB_URL=jdbc:postgresql://localhost:15432/postgres
export DB_USER=gpadmin
export DB_PASSWORD=your_admin_password
export GP_MCP_ENCRYPTION_KEY=your_generated_encryption_key
export SERVER_PORT=8082
ENV

# 4) Launch
./run.sh            # loads .env, builds if needed, starts Spring Boot
# or
./mvnw clean spring-boot:run

Visit http://localhost:8082/admin/api-keys to mint keys and http://localhost:8082/mcp for MCP traffic.

🔧 Configuration Vault

Full matrix lives in src/main/resources/application.yml.

📡 Observability Radar

• Metrics: Scrape /actuator/prometheus for gp_mcp_query_executions_total, gp_mcp_query_duration_seconds, and per-pool Hikari stats (hikaricp_connections_active).
• Tracing: OpenTelemetry spans track authentication, policy evaluation, SQL execution, and cursor lifecycle events.
• Logging: Structured patterns emitted to console and logs/gp-mcp-server.log; include trace/span IDs when tracing is enabled.
• Health: /actuator/health (overall) and /actuator/health/db (database connectivity) integrate cleanly with uptime checks.

🔐 Security Stance

1. API Key Auth — Spring Security filter verifies every call, binding requests to encrypted credential bundles.
2. Credential Vault — AES-256-GCM via EncryptionService stores usernames/passwords; keys never rest in plaintext.
3. Greenplum RBAC — Authorization delegated to database roles; application-level allow lists remain optional.
4. SQL Guardrails — JSQLParser validation, parameter binding, read-only enforcement, redaction rules, and cancellation hooks.
5. Operational Hygiene — .env git-ignored, secrets templated in documentation, and SECURITY_COMPLIANCE_REPORT.md captures review evidence.

Rotate any placeholder secrets you copied from older docs; historic values should be considered compromised.

🧰 Development Flow

# Install dependencies & build
./mvnw clean compile

# Run unit tests
./mvnw test

# Launch with live reload (DevTools)
./mvnw spring-boot:run

# Format / lint hooks (if configured later)
./mvnw spotless:apply

Recommended next steps:

• Add a GitHub Actions workflow running the build + tests on pull requests.
• Wire the MCP endpoint into gp-assistant or another client for end-to-end smoke tests.
• Extend the admin console with audit history for key issuance.

📚 Further Reading

• — Client configuration patterns and inspector examples.
• — Credential rotation, expiry, and policy guardrails.
• — Current security posture snapshot.
• Spring AI MCP Overview
• Greenplum Documentation

🪪 License & Credits

Licensed under the .

Huge thanks to:

• Spring Boot & Spring AI for the MCP server foundation.
• Greenplum Database for the massively parallel SQL core.
• OpenTelemetry and Micrometer for observability tooling.

Crafted with 💙 to give every LLM a safe window into Greenplum.