Redshift MCP Server

Give AI assistants secure, read-only access to your Amazon Redshift data warehouse.

This TypeScript-based Model Context Protocol (MCP) server enables LLMs to inspect schemas, execute queries, and understand your data warehouse structure.

🌟 Based on the original implementation by paschmaria, with production-ready enhancements.

✨ Features

• 🔒 Read-only queries with automatic transaction safety

• 🏗️ Schema introspection - tables, columns, relationships

• 📊 Smart sampling - optional PII redaction (emails, phones)

• 📈 Statistics - table sizes, row counts, distribution keys

• 🔍 Column search - find columns across all schemas

• 🚀 Dual modes - STDIO (IDEs) + HTTP (web/cloud)

• 🔐 Bearer auth - production-ready security

• ☸️ Cloud-native - stateless mode, health checks, K8s-ready

• 🐳 Docker - single-command deployment

Related MCP server: Redshift MCP Server

🚀 Quick Start

Local Setup (5 minutes)

# 1. Clone and install
git clone <repository-url>
cd redshift-mcp-server
npm install

# 2. Build
npm run build

# 3. Configure
export DATABASE_URL="redshift://user:pass@host:5439/db?ssl=true"

# 4. Run (STDIO mode for IDE)
npm start

# OR run HTTP mode for web/cloud
export TRANSPORT_MODE="http"
npm start
# Server: http://localhost:3000/mcp or http://localhost:3000/

Docker (1 minute)

# Build
docker build -t redshift-mcp:latest .

# Run STDIO (for IDEs)
docker run -e DATABASE_URL='redshift://...' -i --rm redshift-mcp:latest

# Run HTTP with auth (for production)
docker run \
  -e DATABASE_URL='redshift://...' \
  -e TRANSPORT_MODE=http \
  -e STATELESS_MODE=true \
  -e ENABLE_AUTH=true \
  -e API_TOKEN=your-secret-token \
  -e REDACT_PII=false \
  -p 3000:3000 \
  redshift-mcp:latest

📋 Table of Contents

• Configuration

• Transport Modes

• Authentication

• IDE Integration

• Testing

• A2A (Experimental)

• Dust.tt Integration

• Kubernetes Deployment

• Available Tools

• Troubleshooting

⚙️ Configuration

Environment Variables

Database URL Format

redshift://username:password@hostname:port/database?ssl=true&timeout=600

Example:

DATABASE_URL="redshift://admin:MyPass123@cluster.us-east-1.redshift.amazonaws.com:5439/analytics?ssl=true"

Configuration File (.env)

# Copy example
cp .env.example .env

# Edit with your values
DATABASE_URL="redshift://..."
TRANSPORT_MODE="http"
STATELESS_MODE="true"
ENABLE_AUTH="true"
API_TOKEN="your-secret-token-here"
REDACT_PII="false"

⚠️ Special characters in passwords: If your DATABASE_URL password contains shell metacharacters ($, `, \, etc.), use **single quotes** in .env, not double quotes. When you source (or set -a && source .env && set +a) a .env file, bash performs variable expansion inside double-quoted values. For example, a password containing $9 will be silently expanded as a positional parameter (usually to an empty string), truncating/corrupting your password.

# ❌ Wrong - $9 gets expanded by bash on `source`
DATABASE_URL="redshift://user:kjash$90sbb@host:5439/db?ssl=true"

# ✅ Correct - single quotes prevent all expansion
DATABASE_URL='redshift://user:kjash$90sbb@host:5439/db?ssl=true'

🔄 Transport Modes

Choose the right transport mode for your use case:

STDIO Mode (Default)

Best for: IDEs, CLI tools, local development

# Default mode - no configuration needed
export DATABASE_URL="redshift://..."
npm start

Clients:

• Cursor IDE

• Windsurf

• Claude Desktop

• Custom CLI tools

How it works: Communicates via standard input/output streams

HTTP Mode

Best for: Web apps, Dust.tt, Kubernetes, remote integrations

# Enable HTTP transport
export DATABASE_URL="redshift://..."
export TRANSPORT_MODE="http"
npm start

Endpoints:

• POST/GET/DELETE /mcp - MCP protocol endpoint

• POST/GET/DELETE / - Root path (alias for /mcp)

• GET /health - Health check with metrics

• GET /ready - Readiness probe

Stateful vs Stateless:

Production recommendation: Use STATELESS_MODE=true for cloud deployments

🔐 Authentication

Bearer Token Auth (Production)

Enable authentication for production deployments (required for Dust.tt, recommended for K8s):

export TRANSPORT_MODE="http"
export ENABLE_AUTH="true"
export API_TOKEN="your-super-secret-token-here"
npm start

How it works:

1. Clients send requests with Authorization: Bearer <token> header

2. Server validates token against API_TOKEN

3. Invalid/missing tokens receive 401 Unauthorized

Security features:

• OPTIONS requests (CORS preflight) don't require auth

• Health/ready endpoints don't require auth

• OAuth discovery endpoints return 404 (tells clients OAuth is not available)

Testing authentication:

# Without token - should fail
curl -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":1}'
# Returns: 401 Unauthorized

# With token - should work
curl -X POST http://localhost:3000/mcp \
  -H "Authorization: Bearer your-super-secret-token-here" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}},"id":1}'
# Returns: 200 OK with server capabilities

Best practices:

• Generate strong tokens: openssl rand -hex 32

• Store tokens in secrets (K8s Secrets, env vars, vault)

• Rotate tokens regularly (every 90 days)

• Use HTTPS in production (ngrok, load balancer, ingress)

💻 IDE Integration

Cursor / Windsurf / Claude Desktop

Add to your MCP config file:

• Cursor: .cursor/mcp.json

• Windsurf: mcp_config.json

• Claude Desktop: claude_desktop_config.json

Option 1: Node.js (Recommended)

{
  "mcpServers": {
    "redshift": {
      "command": "node",
      "args": ["/absolute/path/to/redshift-mcp-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "redshift://user:pass@host:5439/db?ssl=true",
        "REDACT_PII": "false"
      }
    }
  }
}

⚠️ Important: Use absolute paths, not relative paths!

Option 2: Docker

{
  "mcpServers": {
    "redshift": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "DATABASE_URL",
        "-e", "REDACT_PII",
        "redshift-mcp:latest"
      ],
      "env": {
        "DATABASE_URL": "redshift://user:pass@host:5439/db?ssl=true",
        "REDACT_PII": "false"
      }
    }
  }
}

After configuration:

1. Restart your IDE

2. Tools appear automatically in MCP settings

3. Ask AI: "What tables are in my database?"

🌐 MCP Inspector (Testing Tool)

Anthropic's MCP Inspector is a web-based tool for testing MCP servers.

Setup:

# 1. Start server with auth (optional)
export DATABASE_URL="redshift://..."
export TRANSPORT_MODE="http"
export STATELESS_MODE="true"
export ENABLE_AUTH="true"
export API_TOKEN="test-token-123"
npm start

2. Open MCP Inspector and connect:

• Transport: Streamable HTTP

• Connection: Direct

• URL: http://localhost:3000/mcp or http://localhost:3000/

• Authentication: Custom Header (if enabled)

  • Header: Authorization

  • Value: Bearer test-token-123

3. Test tools:

• List tools

• Execute query tool

• Check resources

🧪 Testing

There's no local emulator for Redshift itself, so tests run against plain Postgres (postgres:16-alpine via Docker) with a compatibility shim for the handful of Redshift-only system views this project relies on (SVV_COLUMNS, SVV_TABLES, SVV_TABLE_INFO, and pg_attribute.attisdistkey/attsortkeyord). It's a real emulator for this project's query surface, not a general Redshift emulator — Redshift-only SQL like COPY/UNLOAD, or distribution/sort-aware query plans, still isn't covered, and is_distkey/is_sortkey always come back false.

Integration tests (npm test)

npm test

This spins up the emulator (Docker required), seeds it with a small clients / orders / order_items dataset (see test/docker/init/) plus a second marketing schema, runs the full RedshiftTools suite against it via vitest, and tears the container down again — no manual setup, and no state left behind. It's safe to run in CI (e.g. the existing publish-mcp.yml workflow already calls npm run test --if-present, and GitHub's ubuntu-latest runners ship Docker).

Useful variants:

npm run test:watch        # vitest in watch mode
npm run test:db:up        # start the emulator and leave it running
npm run test:db:logs      # tail Postgres logs
npm run test:db:down      # stop it
KEEP_TEST_DB=true npm test  # run the suite but leave the container up afterward, for poking around

Connect to the running emulator directly with psql "redshift://redshift:redshift@localhost:5439/analytics" (or any Postgres client) once it's up.

End-to-end smoke test (npm run test:e2e)

npm run test:e2e

This drives the real claude CLI against a real build of this server, connected to the same local emulator, and asks it a natural-language question ("how many orders have status 'completed'?") to confirm the whole stack — MCP protocol, tool schema, SQL generation, DB round-trip — works end to end, not just the internal RedshiftTools methods.

The MCP server is registered for that single invocation only, via:

claude -p "..." \
  --mcp-config <temp-file>.json \
  --strict-mcp-config \
  --restricted \
  --allowedTools "mcp__redshift-e2e__query"

--mcp-config loads the server from a throwaway JSON file instead of any persistent config; --strict-mcp-config ignores every other MCP server configured on the machine; --restricted drops code-execution tools and ignores project/user settings files. Nothing is ever written to project, user, or local Claude Code settings, so there's no "disconnect" step afterward — the server was never added anywhere durable, and other sessions on the machine are unaffected regardless of how the script exits. This is preferable to claude mcp add + claude mcp remove, which mutate shared config and would need explicit, failure-prone cleanup.

Requires: the claude CLI installed and authenticated, and Docker. Not run in CI or as part of npm test — it makes a real LLM call and costs API usage (capped at $0.50 via --max-budget-usd).

🤝 A2A (Experimental)

Two extra, unpublished sibling packages — a2a-agent/ and a2a-bridge/ — expose this server to other agents over the A2A protocol, not just to the Claude Code session sitting directly on top of it. Not part of this npm package; doesn't affect anything above.

See A2A.md for the architecture, configuration, how to run it, and how to test it (unit tests + a real end-to-end script driving the actual claude CLI).

☁️ Dust.tt Integration

Dust.tt supports remote MCP servers. Here's how to connect:

Step 1: Expose Your Server

Option A: ngrok (Quick testing)

# Start server with auth
export DATABASE_URL="redshift://..."
export TRANSPORT_MODE="http"
export STATELESS_MODE="true"
export ENABLE_AUTH="true"
export API_TOKEN="your-secret-token"
npm start

# In another terminal, expose
ngrok http 3000
# You'll get: https://abc123.ngrok.io

Option B: Kubernetes (Production)

See Kubernetes Deployment section below.

Step 2: Configure in Dust.tt

1. Go to Dust.tt → Connections → Add MCP Server

2. Fill in:

   • Server Name: Redshift Data Warehouse

   • URL: https://your-ngrok-url.ngrok.io/mcp or https://your-domain.com/mcp

   • Authentication: Bearer Token

   • Token: your-secret-token (same as API_TOKEN)

3. Click Save

✅ Success! Dust.tt agents can now query your Redshift data.

Troubleshooting:

• ❌ "404 Not Found" → Use /mcp suffix or root / path

• ❌ "401 Unauthorized" → Check token matches API_TOKEN exactly

• ❌ "OAuth error" → Select "Bearer Token" auth (not "Automatic")

Step 3: Test in Dust.tt

Ask your Dust.tt agent:

• "What tables are in my Redshift database?"

• "Show me the schema of the users table"

• "How many rows are in the orders table?"

Learn more: Dust.tt MCP Guide

☸️ Kubernetes Deployment

Production-ready K8s deployment with horizontal scaling:

Complete manifest:

apiVersion: v1
kind: Secret
metadata:
  name: redshift-mcp-secrets
type: Opaque
stringData:
  database-url: "redshift://user:pass@host:5439/db?ssl=true"
  api-token: "your-super-secret-token"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: redshift-mcp-server
spec:
  replicas: 3  # Horizontal scaling with stateless mode
  selector:
    matchLabels:
      app: redshift-mcp-server
  template:
    metadata:
      labels:
        app: redshift-mcp-server
    spec:
      containers:
      - name: server
        image: your-registry/redshift-mcp:latest
        ports:
        - containerPort: 3000
        env:
        - name: TRANSPORT_MODE
          value: "http"
        - name: STATELESS_MODE
          value: "true"  # Enable for horizontal scaling
        - name: ENABLE_AUTH
          value: "true"
        - name: API_TOKEN
          valueFrom:
            secretKeyRef:
              name: redshift-mcp-secrets
              key: api-token
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: redshift-mcp-secrets
              key: database-url
        - name: ALLOWED_ORIGINS
          value: "https://dust.tt"
        - name: REDACT_PII
          value: "false"
        livenessProbe:
          httpGet:
            path: /health
            port: 3000
          initialDelaySeconds: 10
          periodSeconds: 30
        readinessProbe:
          httpGet:
            path: /ready
            port: 3000
          initialDelaySeconds: 5
          periodSeconds: 10
        resources:
          requests:
            memory: "256Mi"
            cpu: "100m"
          limits:
            memory: "512Mi"
            cpu: "500m"
---
apiVersion: v1
kind: Service
metadata:
  name: redshift-mcp-service
spec:
  selector:
    app: redshift-mcp-server
  ports:
  - protocol: TCP
    port: 80
    targetPort: 3000
  type: ClusterIP
---
apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: redshift-mcp-ingress
  annotations:
    cert-manager.io/cluster-issuer: "letsencrypt-prod"
    nginx.ingress.kubernetes.io/ssl-redirect: "true"
spec:
  tls:
  - hosts:
    - mcp.your-company.com
    secretName: mcp-tls
  rules:
  - host: mcp.your-company.com
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: redshift-mcp-service
            port:
              number: 80

Key configuration points:

🛠️ Available Tools

The MCP server exposes these tools to AI assistants:

1. query - Execute SQL

Execute read-only SQL queries with automatic transaction safety.

// Input
{
  "sql": "SELECT table_name FROM information_schema.tables LIMIT 10"
}

// Output
[
  {"table_name": "users"},
  {"table_name": "orders"},
  ...
]

Features:

• Automatic BEGIN TRANSACTION READ ONLY

• Safe for production use

• Returns results as JSON array

Example prompts:

• "Show me all tables in the public schema"

• "What are the top 10 customers by revenue?"

• "Count rows in the orders table"

2. describe_table - Table Schema

Get comprehensive table information including columns, data types, and Redshift-specific attributes.

// Input
{
  "schema": "public",
  "table": "users"
}

// Output
{
  "schema": "public",
  "table": "users",
  "columns": [
    {
      "column_name": "id",
      "data_type": "integer",
      "is_nullable": "NO",
      "is_distkey": true,
      "is_sortkey": true
    },
    ...
  ]
}

Includes:

• Column names and data types

• Nullability

• Distribution keys (DISTKEY)

• Sort keys (SORTKEY)

• Defaults and constraints

Example prompts:

• "Describe the structure of the users table"

• "What columns are in the orders table?"

• "Show me the schema for public.payments"

3. find_column - Search Columns

Find tables containing columns matching a search pattern.

// Input
{
  "pattern": "email"
}

// Output
[
  {
    "table_schema": "public",
    "table_name": "users",
    "column_name": "email",
    "data_type": "varchar"
  },
  {
    "table_schema": "public",
    "table_name": "contacts",
    "column_name": "contact_email",
    "data_type": "varchar"
  }
]

Use cases:

• Find all tables with customer IDs

• Locate PII fields across schemas

• Discover relationships between tables

Example prompts:

• "Find all columns containing 'customer'"

• "Which tables have an 'updated_at' column?"

• "Search for columns with 'amount' in the name"

Resources (Contextual Information)

These are auto-discovered and provided to AI assistants:

PII Redaction: Email and phone fields can be redacted in sample data by setting REDACT_PII=true (disabled by default).

🔧 Troubleshooting

Common Issues

❌ Connection Fails

Symptoms: ENOTFOUND, ECONNREFUSED, or timeout errors

Solutions:

1. Check DATABASE_URL format:

   redshift://username:password@cluster.region.redshift.amazonaws.com:5439/database?ssl=true

2. Verify network access: Security groups, VPC settings, public access

3. Test with psql: psql "$DATABASE_URL"

❌ Authentication 401 Unauthorized

Solutions:

1. Verify token matches: API_TOKEN="abc123" → Authorization: Bearer abc123

2. Select "Bearer Token" in Dust.tt (not "Automatic")

3. Check request headers in logs

❌ MCP Inspector Won't Connect

Solutions:

1. Enable stateless mode: STATELESS_MODE="true"

2. Use correct URL: http://localhost:3000/mcp or http://localhost:3000/

3. Add auth header if enabled: Authorization: Bearer your-token

❌ Dust.tt 404 Not Found

Solutions:

1. Use full path: https://your-ngrok-url.ngrok.io/mcp

2. Check ngrok logs for actual requests

3. Verify auth token is correct

❌ IDE Tools Not Showing

Solutions:

1. Use absolute paths in config

2. Verify build: npm run build && ls -la dist/index.js

3. Restart IDE after config changes

Debug Commands

# Health check
curl http://localhost:3000/health

# Test with auth
curl -H "Authorization: Bearer token" http://localhost:3000/mcp

# Test DB connection
psql "$DATABASE_URL" -c "SELECT 1;"

🏗️ Architecture

src/
├── core/
│   └── redshift-tools.ts     # Pure DB logic (transport-agnostic)
├── mcp/
│   └── server.ts             # MCP protocol handler
├── transports/
│   ├── stdio.ts              # STDIO transport
│   └── streamable-http.ts    # HTTP/SSE transport
├── middleware/
│   └── auth.ts               # Bearer token authentication
└── index.ts                  # Application entry point

Key principles:

• 🧩 Core logic is transport-agnostic (reusable)

• 🔌 Transports are pluggable (STDIO, HTTP, WebSocket)

• 🔒 Middleware is modular (auth, CORS, logging)

• ⚙️ Config is environment-driven (12-factor)

See ARCHITECTURE.md for details.

📚 Resources

• MCP Specification

• MCP TypeScript SDK

• Dust.tt MCP Guide

• Original Implementation

🔐 Security

Built-in protections:

• 🔒 Read-only transactions - All queries in BEGIN TRANSACTION READ ONLY

• 😷 PII redaction - Optional email/phone redaction in samples

• 🔐 Bearer auth - Token-based access control

• 🔒 SSL/TLS - Encrypted database connections

Best practices:

1. Use dedicated read-only Redshift user

2. Limit permissions to necessary schemas/tables

3. Enable auth for production: ENABLE_AUTH=true

4. Use strong tokens: openssl rand -hex 32

5. Rotate credentials every 90 days

6. Deploy in private network when possible

7. Monitor query logs for suspicious activity

📝 License & Credits

Based on: paschmaria/redshift-mcp-server

Enhancements:

• ✅ Streamable HTTP + stateless mode

• ✅ Bearer token authentication

• ✅ Kubernetes-ready deployment

• ✅ Root path (/) + OAuth discovery

• ✅ Clean architecture with separation of concerns

HTTP Transport Inspiration: The HTTP/SSE transport implementation took inspiration from:

• mcp-streamable-http

• mcp-streamable-http-typescript-server

• http-oauth-mcp-server

Stack: TypeScript 5.3+ | Node.js 16+ | MCP SDK 1.8.0 | Express.js

🚀 Questions? Issues? PRs welcome!