dbbaskette/gp-mcp-server

Greenplum MCP Server

Production-ready Model Context Protocol server for Greenplum and PostgreSQL

[!WARNING] This repository is an unofficial project provided "as is."

It is not supported or endorsed by any organization, and no warranty or guarantee of functionality is provided. Use at your own discretion.

Overview

A Spring Boot MCP (Model Context Protocol) server that provides secure, policy-enforced database tools for AI agents and LLMs. Built on Spring AI's MCP framework with Streamable HTTP transport, it enables AI assistants to safely query Greenplum and PostgreSQL databases with fine-grained access control.

Key Features

• 🔐 Secure by Design — API key authentication with encrypted credential storage (AES-256-GCM)
• 🛡️ Policy Enforcement — Query validation, row/byte limits, schema/table allow-lists, and automatic redaction
• ⚡ Production Ready — HikariCP connection pooling, OpenTelemetry tracing, Prometheus metrics, health checks
• 🔌 MCP Protocol — Streamable HTTP transport with 5-second keep-alives for responsive tool execution
• 📊 Smart Caching — 15-minute Caffeine cache for schema metadata with hit rates tracked in metrics
• ☁️ Cloud Native — Ready for Cloud Foundry, Kubernetes, or containerized deployments
• 🎯 Method-Level Security — Tool discovery is public, but execution requires authentication

Table of Contents

• Architecture
• MCP Tools
• Quick Start
• Configuration
• Security Model
• Cloud Foundry Deployment
• API Key Management
• Observability
• Performance Optimizations
• Development

Architecture

┌─────────────────────────────────────────────────────────────┐
│  MCP Clients (AI Assistants, Nexus Gateway, etc.)          │
└─────────────────────────────────────────────────────────────┘
                            │
                            │ POST /mcp (Streamable HTTP)
                            │ X-API-Key: gpmcp_live_xxxxx.yyyyy
                            ▼
┌─────────────────────────────────────────────────────────────┐
│  Spring Security Filter Chain                                │
│  • ApiKeyAuthenticationFilter validates API keys            │
│  • Sets SecurityContext with authenticated principal        │
│  • Public: /mcp (discovery), /actuator/*, /admin/*         │
│  • Protected: Tool execution (method-level @PreAuthorize)   │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│  Spring AI MCP Server                                        │
│  • @McpTool annotation scanning (12 tools)                  │
│  • Streamable HTTP protocol handler                          │
│  • Keep-alive pings (5s interval)                           │
│  • Session management (5min timeout)                         │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│  Business Logic Layer                                        │
│  ┌─────────────────┬──────────────────┬─────────────────┐  │
│  │  SchemaTools    │   QueryTools     │  PolicyService  │  │
│  │  (4 tools)      │   (8 tools)      │  (validation)   │  │
│  │  @PreAuthorize  │   @PreAuthorize  │                 │  │
│  └─────────────────┴──────────────────┴─────────────────┘  │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  DatabaseConnectionManager                           │   │
│  │  • Per-API-key HikariCP connection pools            │   │
│  │  • Credential decryption via CredentialService      │   │
│  │  • Session-level settings (search_path, timeouts)   │   │
│  └─────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│  Greenplum / PostgreSQL Cluster                             │
│  • Separate connection pools per API key                    │
│  • Database-level RBAC and row-level security              │
│  • Query optimization with pg_hint_plan support            │
└─────────────────────────────────────────────────────────────┘

Component Highlights

MCP Tools

The server exposes 12 tools via the MCP protocol. All tools require authentication for execution, but discovery via tools/list is public.

Schema Discovery Tools (4)

Query Execution Tools (8)

Tool Output Format

All tools return data in JSON Table format optimized for AI consumption:

{
  "columns": [
    {"name": "schema_name", "type": "text"},
    {"name": "table_count", "type": "integer"}
  ],
  "rows": [
    ["public", 42],
    ["analytics", 108]
  ]
}

Quick Start

Prerequisites

• Java 21+
• Maven 3.8+
• Greenplum or PostgreSQL 13+ (accessible via network)
• OpenSSL (for encryption key generation)

Local Development

# 1. Clone the repository
git clone https://github.com/your-org/gp-mcp-server.git
cd gp-mcp-server

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

# 3. Create environment configuration
cat > .env <<'EOF'
export DB_URL=jdbc:postgresql://localhost:15432/postgres
export DB_USER=gpadmin
export DB_PASSWORD=VMware1!
export GP_MCP_ENCRYPTION_KEY=<your-generated-key>
export SERVER_PORT=8082
EOF

# 4. Start the server (automatically builds if needed)
./run.sh

# Or use Maven directly:
source .env
./mvnw spring-boot:run

Verify Installation

# Check health
curl http://localhost:8082/actuator/health

# Generate an API key
curl -X POST http://localhost:8082/admin/api-keys/generate \
  -H "Content-Type: application/json" \
  -d '{
    "username": "demo_user",
    "password": "demo_password",
    "databaseName": "postgres",
    "description": "Demo API key"
  }'

# Test MCP endpoint (with generated API key)
curl http://localhost:8082/mcp \
  -H "X-API-Key: gpmcp_live_xxxxx.yyyyy" \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/list",
    "id": 1
  }'

Configuration

Environment Variables

Application Profiles

Connection Pool Tuning

Optimized for single-user local development (configurable via application.yml or environment variables):

spring:
  datasource:
    hikari:
      maximum-pool-size: 10     # ${DB_MAX_POOL_SIZE}
      minimum-idle: 2           # ${DB_MIN_IDLE}
      connection-timeout: 30000 # ${DB_CONNECTION_TIMEOUT}
      idle-timeout: 300000      # ${DB_IDLE_TIMEOUT}
      max-lifetime: 1800000     # ${DB_MAX_LIFETIME}

For production multi-tenant workloads, increase pool sizes and tune based on load testing.

Security Model

Authentication Flow

1. HTTP Filter Layer — ApiKeyAuthenticationFilter validates X-API-Key or Authorization: Bearer headers
2. Security Context — Valid API keys populate Spring Security context with ROLE_API_USER
3. Method Security — All @McpTool methods require @PreAuthorize("isAuthenticated()")
4. Public Endpoints — Tool discovery (/mcp with no auth), health checks, and admin console

API Key Format

gpmcp_live_{id}.{secret}

• ID: 16-character alphanumeric identifier (visible in logs)
• Secret: 32-character cryptographic secret (stored as bcrypt hash)
• Storage: Credentials encrypted with AES-256-GCM in api_keys table

Credential Encryption

// Encryption (during API key generation)
AES-256-GCM(plaintext_password, encryption_key) → encrypted_bytes

// Decryption (during connection pool creation)
AES-256-GCM-Decrypt(encrypted_bytes, encryption_key) → plaintext_password

The GP_MCP_ENCRYPTION_KEY must be:

• Base64-encoded 32-byte random value
• Stored securely (environment variable, Kubernetes secret, Cloud Foundry service)
• Never committed to version control

SQL Injection Prevention

// ✅ Safe: Parameterized queries with named parameters
SELECT * FROM users WHERE id = :userId
// Parameters: {userId: 123}

// ❌ Blocked: String concatenation
String sql = "SELECT * FROM " + tableName; // Validation fails

// ❌ Blocked: Multiple statements
SELECT * FROM users; DROP TABLE users; -- Parsed as multiple statements, rejected

// ✅ Allowed: Whitelisted functions
SELECT COUNT(*), SUM(amount) FROM transactions WHERE date > :startDate

Policy Enforcement

Configured in application.yml:

gp:
  mcp:
    policy:
      max-rows: 10000                    # Row limit per query
      max-bytes-mb: 100                  # Result size limit
      statement-timeout-ms: 5000         # Query timeout
      allowed-schemas: public,analytics  # Schema whitelist
      allowed-tables: public.*           # Table pattern matching
      redaction-rules:                   # Column-level data masking
        "public.users.email":
          type: MASK
          replacement: "***@***"

Cloud Foundry Deployment

Prerequisites

# Install CF CLI
brew install cloudfoundry/tap/cf-cli

# Login to Cloud Foundry
cf login -a https://api.sys.your-cf-domain.com

# Target org and space
cf target -o your-org -s your-space

Deployment

# 1. Create variables file (copy from vars.yml and customize)
cp vars.yml prod-vars.yml
# Edit prod-vars.yml with production database credentials

# 2. Deploy using the automated script
./deploy.sh -cf

# Or manually:
./mvnw clean package -DskipTests
cf push --vars-file prod-vars.yml

Cloud Foundry Manifest

---
applications:
  - name: gp-mcp-server
    memory: 1G
    instances: 1
    path: target/gp-mcp-server-0.0.1-SNAPSHOT.jar
    buildpacks:
      - java_buildpack_offline
    env:
      SPRING_PROFILES_ACTIVE: cloud
      JBP_CONFIG_OPEN_JDK_JRE: '{ jre: { version: 21.+ } }'
      DB_HOST: ((DB_HOST))
      DB_PORT: ((DB_PORT))
      DB_NAME: ((DB_NAME))
      DB_USERNAME: ((DB_USERNAME))
      DB_PASSWORD: ((DB_PASSWORD))
      GP_MCP_ENCRYPTION_KEY: ((GP_MCP_ENCRYPTION_KEY))
    health-check-type: http
    health-check-http-endpoint: /actuator/health

Variables are injected from vars.yml using cf push --vars-file.

Post-Deployment

# Check application status
cf app gp-mcp-server

# View logs
cf logs gp-mcp-server --recent

# Test health endpoint
curl https://gp-mcp-server.apps.your-cf-domain.com/actuator/health

# Generate API key
curl -X POST https://gp-mcp-server.apps.your-cf-domain.com/admin/api-keys/generate \
  -H "Content-Type: application/json" \
  -d '{"username":"app_user","password":"secure_password","databaseName":"production_db"}'

API Key Management

Admin Console

Access the web UI at /admin/api-keys for:

• Connection testing (dry-run before key generation)
• API key generation with custom credentials
• One-time display with copy-to-clipboard
• Inline validation and error handling

Programmatic API

Test Connection

POST /admin/api-keys/test-connection
Content-Type: application/json

{
  "username": "test_user",
  "password": "test_password",
  "databaseName": "postgres"
}

Response:

{
  "success": true,
  "message": "Connection successful"
}

Generate API Key

POST /admin/api-keys/generate
Content-Type: application/json

{
  "username": "my_app_user",
  "password": "my_db_password",
  "databaseName": "my_database",
  "description": "Production API key for app X"
}

Response:

{
  "apiKey": "gpmcp_live_a1b2c3d4e5f6g7h8.x1y2z3w4v5u6t7s8r9q0p1o2n3m4l5k6",
  "message": "API key generated successfully. Save this key securely - it will not be shown again."
}

⚠️ Important: API keys are displayed only once. Store them securely (environment variables, secrets manager, etc.).

Key Rotation

-- Revoke an API key (connect to gp_assistant database)
UPDATE api_keys SET enabled = false WHERE id = 'a1b2c3d4e5f6g7h8';

-- Or delete entirely
DELETE FROM api_keys WHERE id = 'a1b2c3d4e5f6g7h8';

After revocation, requests with that key receive HTTP 401 Unauthorized.

Observability

Metrics (Prometheus)

Scrape endpoint: /actuator/prometheus

Custom Metrics:

HikariCP Metrics:

Cache Metrics:

Health Checks

# Overall health
curl http://localhost:8082/actuator/health

# Database connectivity
curl http://localhost:8082/actuator/health/db

# Disk space
curl http://localhost:8082/actuator/health/diskSpace

Response:

{
  "status": "UP",
  "components": {
    "db": {"status": "UP"},
    "diskSpace": {"status": "UP"},
    "ping": {"status": "UP"}
  }
}

OpenTelemetry Tracing

Configure OTLP endpoint via environment:

export OTEL_EXPORTER_OTLP_ENDPOINT=http://localhost:4317
export OTEL_TRACES_EXPORTER=otlp
export OTEL_METRICS_EXPORTER=none

Traced Operations:

• API key authentication
• MCP tool invocations
• Query execution (with SQL statement in span attributes)
• Connection pool acquisition
• Policy validation

Trace/span IDs appear in logs when tracing is enabled.

Logging

Log Levels (configure in application.yml):

logging:
  level:
    com.baskettecase.gpmcp: DEBUG
    org.springframework: INFO
    org.springaicommunity.mcp: INFO
    com.zaxxer.hikari: WARN

Structured Log Format:

2025-12-03 19:45:43 [http-nio-8080-exec-2] INFO  c.b.g.t.QueryTools - 🔍 Executing query: SELECT * FROM users WHERE id = ?
2025-12-03 19:45:43 [http-nio-8080-exec-2] INFO  c.b.g.t.QueryTools - ✅ Query executed successfully: 1 rows, 245 bytes

Performance Optimizations

1. Schema Metadata Caching

Impact: 450ms → 1ms on cache hit (450x faster)

@Cacheable(value = "schemaList", key = "#databaseName + '_' + #limit + '_' + #offset")
public String listSchemas(String databaseName, int limit, int offset)

Configuration:

• Cache provider: Caffeine (in-memory)
• TTL: 15 minutes
• Eviction: LRU (least recently used)

Cache Keys:

• schemaList: Schema listings
• tableList: Table listings per schema
• columnDetails: Column details per table

2. Optimized pg_catalog Queries

listSchemas uses direct pg_namespace query:

SELECT n.nspname AS schema_name,
       COUNT(c.oid) AS table_count
FROM pg_namespace n
LEFT JOIN pg_class c ON n.oid = c.relnamespace AND c.relkind IN ('r', 'v')
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema'
GROUP BY n.nspname
ORDER BY n.nspname
LIMIT ? OFFSET ?

listTables combines pg_class and pg_stat_user_tables:

SELECT c.relname AS table_name,
       c.relkind AS table_type,
       pg_total_relation_size(c.oid) AS total_size_bytes,
       s.n_live_tup AS row_count
FROM pg_class c
LEFT JOIN pg_stat_user_tables s ON c.oid = s.relid
WHERE c.relnamespace = ?::regnamespace
ORDER BY c.relname

3. Connection Pool Per API Key

Isolates user workloads and prevents connection exhaustion:

API Key A → HikariCP Pool A (max 10 connections) → Greenplum as user_a
API Key B → HikariCP Pool B (max 10 connections) → Greenplum as user_b

4. Streamable HTTP Keep-Alive

5-second keep-alive pings prevent client timeouts during long operations:

spring:
  ai:
    mcp:
      server:
        streamable-http:
          keep-alive-interval: 5s

5. SQL Validation Pre-Flight

Catches errors before execution:

• JSQLParser validates syntax
• PolicyService checks schema/table permissions
• SqlValidator enforces SELECT-only, no multi-statements

This prevents invalid queries from consuming database resources.

Development

Build

# Full build with tests
./mvnw clean package

# Skip tests
./mvnw clean package -DskipTests

# Run tests only
./mvnw test

Run Locally

# With environment variables
source .env
./mvnw spring-boot:run

# Or use the convenience script
./run.sh

Project Structure

gp-mcp-server/
├── src/main/java/com/baskettecase/gpmcp/
│   ├── api/                  # REST controllers
│   │   ├── ApiKeyWebController.java    # Admin console endpoints
│   │   └── PerformanceController.java  # Performance/cache endpoints
│   ├── config/               # Spring configuration
│   │   └── CacheConfig.java           # Caffeine cache setup
│   ├── db/                   # Database layer
│   │   ├── DatabaseConnectionManager.java   # Per-key connection pools
│   │   └── GpAssistantDataSourceConfig.java # gp_assistant DB config
│   ├── policy/               # Policy enforcement
│   │   └── PolicyService.java         # Query validation rules
│   ├── security/             # Security layer
│   │   ├── SecurityConfig.java        # Spring Security config
│   │   ├── ApiKeyAuthenticationFilter.java
│   │   ├── ApiKeyService.java
│   │   └── CredentialEncryptionService.java
│   ├── sql/                  # SQL validation
│   │   └── SqlValidator.java          # JSQLParser integration
│   ├── tools/                # MCP tool implementations
│   │   ├── SchemaTools.java  # 4 schema discovery tools
│   │   └── QueryTools.java   # 8 query execution tools
│   └── util/                 # Utilities
│       ├── FuzzyMatcher.java          # Fuzzy table name matching
│       └── JsonResponseFormatter.java  # JSON table format
├── src/main/resources/
│   ├── application.yml        # Main configuration
│   ├── application-cloud.yml  # Cloud Foundry profile
│   ├── policy.yml            # Policy rules (optional)
│   └── templates/
│       └── api-keys.html     # Admin console UI
├── deploy.sh                  # Cloud Foundry deployment script
├── run.sh                     # Local development startup
├── manifest.yml              # Cloud Foundry manifest
└── vars.yml                  # CF deployment variables

Key Classes

Testing

Cache Verification:

# Run cache performance test
./test-cache.sh

# Or manually:
curl http://localhost:8082/admin/performance/cache/stats

Connection Testing:

curl -X POST http://localhost:8082/admin/api-keys/test-connection \
  -H "Content-Type: application/json" \
  -d '{"username":"test","password":"test","databaseName":"postgres"}'

Documentation

• — Detailed Cloud Foundry deployment guide
• — Performance tuning and optimization
• Spring AI MCP Documentation
• Model Context Protocol Specification

License

Licensed under the .

Credits

Built with:

• Spring Boot — Application framework
• Spring AI — MCP server implementation
• Greenplum Database — Massively parallel PostgreSQL
• HikariCP — High-performance JDBC connection pool
• JSQLParser — SQL syntax validation
• Caffeine — High-performance caching library
• OpenTelemetry — Observability and tracing