# 🚀 Redshift MCP Server

**A plug-and-play Model Context Protocol (MCP) server for Amazon Redshift. Instantly connect LLMs and AI agents (Claude, ChatGPT, etc.) to your Redshift data — securely, safely, and with zero hassle.**

---

## 🌟 Features
- **Production-ready**: Secure, robust, and easy to deploy
- **MCP Standard**: Exposes Redshift as standardized tools for LLMs/AI
- **Strict Access Modes**: `readonly`, `readwrite`, `admin` (enforced by env var)
- **Auto-connect**: Use environment variables or a `.env` file for seamless startup
- **No code changes needed**: Just configure and run
- **Extensible**: Add new tools or business logic easily
- **Works with Claude Desktop, ChatGPT, and any MCP client**

---

## ⚡ Quickstart

1. **Clone & Install**
   ```bash
   git clone <repo-url>
   cd my-redshift-mcp
   python3 -m venv venv
   source venv/bin/activate
   pip install -r requirements.txt
   ```
2. **Configure** (recommended: create a `.env` file):
   ```env
   REDSHIFT_HOST=your-cluster.region.redshift.amazonaws.com
   REDSHIFT_DATABASE=mydb
   REDSHIFT_USER=myuser
   REDSHIFT_PASSWORD=mypassword
   REDSHIFT_PORT=5439
   DB_MCP_MODE=readonly
   ```
3. **Run the server**
   ```bash
   ./scripts/run_server.sh
   ```
4. **Connect from your LLM/MCP client** (e.g., Claude Desktop)

---

## 🔗 What is Redshift MCP Server?

Redshift MCP Server is an open-source bridge between Amazon Redshift and modern AI assistants. It lets LLMs securely query, explore, and (optionally) modify your Redshift data using the [Model Context Protocol (MCP)](https://github.com/anthropic-ai/model-context-protocol). Perfect for:
- Data teams enabling AI-powered analytics
- Building AI workflows and automations
- Secure, auditable LLM access to production data

---

## About This Project

This repository provides a **minimal, production-ready MCP server for Amazon Redshift** using the FastMCP framework. It allows LLMs and AI agents to:
- Query Redshift databases (with strict access controls)
- List schemas and tables
- Describe table structures
- (Optionally) insert/update data, depending on configured mode

**Key features:**
- Three access modes: `readonly`, `readwrite`, `admin` (see below)
- Environment variable and `.env` support for easy configuration
- Secure by default: starts in `readonly` mode
- Compatible with Claude Desktop, ChatGPT, and any MCP client
- Easy to extend with new tools or business logic

---

## Features

This MCP server provides the following tools for Redshift database operations:

- **connect_db**: Establish connection to a Redshift cluster (supports environment variables)
- **query**: Execute SELECT queries with optional parameter binding
- **execute**: Run INSERT, UPDATE, DELETE, or DDL statements
- **list_schemas**: List all schemas in the database
- **list_tables**: List tables in a specific schema
- **describe_table**: Get detailed structure of a table
- **disconnect**: Close the database connection

### Auto-Connection Support

The server supports automatic connection on startup using environment variables. If all required environment variables are set, the server will connect automatically when it starts.

---

## Access Modes and Query Safety

The server supports three access modes, controlled by the `DB_MCP_MODE` environment variable:

### 1. **readonly** (default)
- **Allows:** `SELECT`, `SHOW`, `DESCRIBE`, etc.
- **Blocks:** `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `ALTER`, `CREATE`, `GRANT`, `REVOKE`, `COMMENT`, `SET`, `COPY`, `UNLOAD`, `VACUUM`, `ANALYZE`, `MERGE`

### 2. **readwrite**
- **Allows:** `SELECT`, `INSERT`, `UPDATE`, `CREATE`, etc.
- **Blocks:** `DELETE`, `DROP`, `TRUNCATE`, `ALTER`, `GRANT`, `REVOKE`, `COMMENT`, `SET`, `COPY`, `UNLOAD`, `VACUUM`, `ANALYZE`, `MERGE`

### 3. **admin**
- **Allows everything** (no restrictions)

**If a forbidden statement is detected, the server will return a clear error message.**

#### How to Set the Mode
- **Environment variable:** `DB_MCP_MODE=readonly` (or `readwrite`, `admin`)
- **Claude Desktop config:**
  ```json
  "env": {
    "DB_MCP_MODE": "readwrite",
    ...
  }
  ```
- **Shell/local:**
  ```bash
  export DB_MCP_MODE=readonly
  ./run_server.sh
  ```

---

## Installation

1. Clone or download this repository
2. Install dependencies:

```bash
pip install -r requirements.txt
```

## Usage

### Running the Server

Run the server directly:

```bash
python src/redshift_mcp_server.py
```

Or use the provided scripts:

```bash
./scripts/run_server.sh
# or on Windows:
scripts\run_server.bat
```

By default, the server runs using STDIO transport, which is suitable for integration with MCP clients like Claude Desktop.

### Configuration for Claude Desktop

To use this server with Claude Desktop, add the following configuration to your Claude Desktop config file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%/Claude/claude_desktop_config.json`

#### Option 1: With Environment Variables (Recommended)

```json
{
  "mcpServers": {
    "redshift": {
      "command": "/Users/anshulpatre/Desktop/DB-mcp/my-redshift-mcp/venv/bin/python",
      "args": ["/Users/anshulpatre/Desktop/DB-mcp/my-redshift-mcp/src/redshift_mcp_server.py"],
      "env": {
        "REDSHIFT_HOST": "your-cluster.region.redshift.amazonaws.com",
        "REDSHIFT_DATABASE": "mydb",
        "REDSHIFT_USER": "myuser",
        "REDSHIFT_PASSWORD": "mypassword",
        "REDSHIFT_PORT": "5439",
        "DB_MCP_MODE": "readonly"
      }
    }
  }
}
```

With this configuration, the server will automatically connect to your Redshift database on startup. You won't need to use the `connect_db` tool manually.

#### Option 2: Without Environment Variables

```json
{
  "mcpServers": {
    "redshift": {
      "command": "/Users/anshulpatre/Desktop/DB-mcp/my-redshift-mcp/venv/bin/python",
      "args": ["/Users/anshulpatre/Desktop/DB-mcp/my-redshift-mcp/src/redshift_mcp_server.py"]
    }
  }
}
```

With this configuration, you'll need to use the `connect_db` tool to establish a connection.

### Environment Variables

The server supports the following environment variables:

- `REDSHIFT_HOST`: Redshift cluster endpoint
- `REDSHIFT_DATABASE`: Database name
- `REDSHIFT_USER`: Username
- `REDSHIFT_PASSWORD`: Password
- `REDSHIFT_PORT`: Port number (default: 5439)
- `DB_MCP_MODE`: Access mode (`readonly`, `readwrite`, `admin`)

When these environment variables are set, the server will:
1. Automatically connect on startup
2. Use them as defaults for the `connect_db` tool if no parameters are provided
3. Enforce query/statement restrictions based on the selected mode

---

### Available Tools

#### 1. connect_db
Connect to a Redshift database cluster. If environment variables are set, parameters are optional.

```python
# With explicit parameters
{
  "tool": "connect_db",
  "arguments": {
    "host": "your-cluster.region.redshift.amazonaws.com",
    "database": "mydb",
    "user": "myuser",
    "password": "mypassword",
    "port": 5439
  }
}

# With environment variables set
{
  "tool": "connect_db",
  "arguments": {}
}
```

#### 2. query
Execute SELECT queries to retrieve data.

```python
{
  "tool": "query",
  "arguments": {
    "sql": "SELECT * FROM users WHERE created_at > %s",
    "params": ["2024-01-01"]
  }
}
```

#### 3. execute
Execute data modification or DDL statements.

```python
{
  "tool": "execute",
  "arguments": {
    "sql": "INSERT INTO users (name, email) VALUES (%s, %s)",
    "params": ["John Doe", "john@example.com"]
  }
}
```

#### 4. list_schemas
List all user-created schemas.

```python
{
  "tool": "list_schemas",
  "arguments": {}
}
```

#### 5. list_tables
List tables in a specific schema.

```python
{
  "tool": "list_tables",
  "arguments": {
    "schema": "public"
  }
}
```

#### 6. describe_table
Get detailed information about a table's structure.

```python
{
  "tool": "describe_table",
  "arguments": {
    "table": "users",
    "schema": "public"
  }
}
```

#### 7. disconnect
Close the database connection.

```python
{
  "tool": "disconnect",
  "arguments": {}
}
```

---

## Security Considerations

- **Credentials**: The recommended approach is to use environment variables in the MCP configuration file. This keeps credentials out of your code and tool calls.
- **Permissions**: Use database users with minimal required permissions.
- **Query Safety**: The server uses parameterized queries to prevent SQL injection. Query/statement type is checked and blocked according to the selected mode.
- **Network Security**: Ensure your Redshift cluster is properly secured with VPC and security groups.

## Development

To extend this server:

1. Add new tools using the `@mcp.tool()` decorator
2. Follow the FastMCP documentation for advanced features
3. Test thoroughly with your Redshift cluster

## Troubleshooting

### Connection Issues
- Verify your Redshift cluster endpoint and credentials
- Check network connectivity and security group settings
- Ensure the cluster is active and accepting connections

### Import Errors
- If `redshift-connector` is not available, the server falls back to `psycopg2`
- Install the appropriate connector based on your needs

## License

MIT

## Contributing

Feel free to submit issues or pull requests to improve this MCP server.

---

## 👤 Maintainer

This Redshift MCP Server is maintained and deployed by **Anshul**.