📦

Gp MCP Server

No description available

0 installs

Trust: 30 — Low

Devtools

Ask AI about Gp MCP Server

I know everything about Gp MCP Server. Ask me about installation, configuration, usage, or troubleshooting.

0/500

Loading tools...

Reviews

Documentation

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

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

Component	Purpose	Key Features
ApiKeyAuthenticationFilter	HTTP-level authentication	Validates X-API-Key or Authorization: Bearer headers, sets Spring Security context
@PreAuthorize Annotations	Method-level authorization	All 12 @McpTool methods require `isAuthenticated()`
DatabaseConnectionManager	Connection lifecycle	HikariCP pools per API key, automatic cleanup, optimized for single-user development
PolicyService	Query governance	Row limits, byte budgets, schema/table allow-lists, SQL validation
CredentialEncryptionService	Secret management	AES-256-GCM encryption for database credentials stored in `api_keys` table
Caffeine Cache	Metadata caching	15-minute TTL for schema/table/column metadata (450ms → 1ms on cache hit)

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)

Tool	Description	Caching
`gp.listSchemas`	List all schemas with table counts and pagination	✅ 15min
`gp.listTables`	List tables in a schema with row counts, sizes, and types	✅ 15min
`gp.getTableSchema`	Get columns, types, constraints, and indexes for a table	✅ 15min
`gp.getTableDistribution`	Get Greenplum-specific distribution and partitioning info	⚡ Real-time

Query Execution Tools (8)

Tool	Description	Features
`gp.previewQuery`	Validate SQL without execution	JSQLParser validation, policy checks
`gp.runQuery`	Execute parameterized SELECT query	Streaming JSON table format, policy enforcement
`gp.explain`	Get EXPLAIN plan for a query	Supports ANALYZE option
`gp.getSampleData`	Get sample rows from a table	Limit parameter, useful for AI data understanding
`gp.openCursor`	Open server-side cursor for large result sets	Named cursors, parameterized queries
`gp.fetchCursor`	Fetch batch of rows from cursor	Configurable batch size
`gp.closeCursor`	Close cursor and free resources	Automatic cleanup on session end
`gp.cancel`	Cancel a running query by operation ID	Connection-level cancellation

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

Variable	Required	Default	Description
`DB_URL`	Yes	`jdbc:postgresql://localhost:15432/postgres`	JDBC URL for Greenplum/PostgreSQL
`DB_USER`	Yes	`gpadmin`	Bootstrap database username
`DB_PASSWORD`	Yes	`secret`	Bootstrap database password
`GP_MCP_ENCRYPTION_KEY`	Yes	(none)	Base64-encoded 32-byte AES key for encrypting API key credentials
`SERVER_PORT`	No	`8082`	HTTP server port
`DB_SEARCH_PATH`	No	`public`	Default search path for database sessions
`DB_STATEMENT_TIMEOUT_MS`	No	`5000`	Query timeout in milliseconds
`API_KEY_ENABLED`	No	`true`	Enable/disable API key authentication
`ENVIRONMENT`	No	`development`	Environment tag for metrics

Application Profiles

Profile	Activation	Use Case
`default`	Local	Development with local Greenplum/PostgreSQL
`cloud`	`SPRING_PROFILES_ACTIVE=cloud`	Cloud Foundry deployment with environment variable injection

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

HTTP Filter Layer — ApiKeyAuthenticationFilter validates X-API-Key or Authorization: Bearer headers
Security Context — Valid API keys populate Spring Security context with ROLE_API_USER
Method Security — All @McpTool methods require @PreAuthorize("isAuthenticated()")
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:

Metric	Type	Description
`gp_mcp_query_executions_total`	Counter	Total query executions
`gp_mcp_query_duration_seconds`	Timer	Query execution time (histogram)
`gp_mcp_schema_queries_total`	Counter	Schema discovery operations
`gp_mcp_schema_query_duration_seconds`	Timer	Schema query time

HikariCP Metrics:

Metric	Description
`hikaricp_connections_active`	Active database connections
`hikaricp_connections_idle`	Idle connections in pool
`hikaricp_connections_pending`	Threads waiting for connection
`hikaricp_connections_timeout_total`	Connection acquisition timeouts

Cache Metrics:

Metric	Description
`cache_gets{result="hit"}`	Cache hit count
`cache_gets{result="miss"}`	Cache miss count
`cache_evictions`	Cache eviction count
`cache_size`	Current cache entry count

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

Class	Responsibility
`SchemaTools`	4 MCP tools for schema/table discovery
`QueryTools`	8 MCP tools for query execution
`ApiKeyAuthenticationFilter`	HTTP request authentication
`DatabaseConnectionManager`	Per-API-key HikariCP pools
`PolicyService`	Query policy validation
`CredentialEncryptionService`	AES-256-GCM credential encryption
`SqlValidator`	SQL safety validation via JSQLParser

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

CLOUD_FOUNDRY.md — Detailed Cloud Foundry deployment guide
PERFORMANCE_GUIDE.md — Performance tuning and optimization
Spring AI MCP Documentation
Model Context Protocol Specification

License

Licensed under the MIT License.

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

_{Built with ❤️ for AI-powered database access}

Gp MCP Server

Reviews

Documentation

Greenplum MCP Server

Overview

Key Features

Table of Contents

Architecture

Component Highlights

MCP Tools

Schema Discovery Tools (4)

Query Execution Tools (8)

Tool Output Format

Quick Start

Prerequisites

Local Development

Verify Installation

Configuration

Environment Variables

Application Profiles

Connection Pool Tuning

Security Model

Authentication Flow

API Key Format

Credential Encryption

SQL Injection Prevention

Policy Enforcement

Cloud Foundry Deployment

Prerequisites

Deployment

Cloud Foundry Manifest

Post-Deployment

API Key Management

Admin Console

Programmatic API

Test Connection

Generate API Key

Key Rotation

Observability

Metrics (Prometheus)

Health Checks

OpenTelemetry Tracing

Logging

Performance Optimizations

1. Schema Metadata Caching

2. Optimized pg_catalog Queries

3. Connection Pool Per API Key

4. Streamable HTTP Keep-Alive

5. SQL Validation Pre-Flight

Development

Build

Run Locally

Project Structure

Key Classes

Testing

Documentation

License

Credits