AWS Redshift Connection Configuration

Amazon Redshift is a cloud-based data warehouse service that requires specialized connection configuration. mcp_ohmy_sql supports both traditional username/password authentication and modern AWS IAM-based authentication for Redshift clusters.

See also

AWSRedshiftConnection - The configuration class source code for AWS Redshift connection

AWS Redshift Connection Type

AWS Redshift databases use the "aws_redshift" connection type:

{
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        # ... authentication configuration
    }
}

Authentication Methods

AWS Redshift supports two main authentication approaches:

1. Direct Credentials: Traditional host/port/username/password

2. AWS IAM Authentication: Uses AWS credentials and temporary tokens

Both methods ultimately create a SQLAlchemy connection, but handle authentication differently.

Method 1: Direct Credentials

Use this method when you have direct database credentials:

{
    "identifier": "redshift_direct",
    "description": "Redshift with direct credentials",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "host": "my-cluster.abc123.us-east-1.redshift.amazonaws.com",
        "port": 5439,
        "database": "warehouse",
        "username": "analyst",
        "password": "secure_password"
    }
}

Required Fields for Direct Credentials:

• host: Redshift cluster endpoint

• port: Database port (typically 5439)

• database: Database name within the cluster

• username: Database username

• password: Database password

Method 2: AWS IAM Authentication

Use this method for secure, credential-free authentication using AWS IAM:

For Redshift Clusters:

{
    "identifier": "redshift_iam_cluster",
    "description": "Redshift cluster with IAM auth",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "my-analytics-cluster",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            "profile_name": "analytics"
        }
    }
}

For Redshift Serverless:

{
    "identifier": "redshift_serverless",
    "description": "Redshift Serverless with IAM auth",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "namespace_name": "analytics-namespace",
        "workgroup_name": "analytics-workgroup",
        "boto_session_kwargs": {
            "region_name": "us-west-2",
            "profile_name": "default"
        }
    }
}

AWS Credentials Configuration

The boto_session_kwargs object configures how to authenticate with AWS:

{
    "boto_session_kwargs": {
        "region_name": "us-east-1",
        "profile_name": "analytics",
        "aws_access_key_id": "AKIA...",
        "aws_secret_access_key": "...",
        "aws_session_token": "...",
        "role_arn": "arn:aws:iam::123456789012:role/RedshiftAnalyst",
        "duration_seconds": 3600,
        "auto_refresh": false
    }
}

AWS Credential Options:

AWS Credential Parameters

Parameter	Required	Description
region_name	No	AWS region where Redshift cluster is located
profile_name	No	AWS CLI profile name from ~/.aws/credentials
aws_access_key_id	No	AWS access key (use environment variables instead)
aws_secret_access_key	No	AWS secret key (use environment variables instead)
aws_session_token	No	Temporary session token for STS credentials
role_arn	No	IAM role to assume for Redshift access
duration_seconds	No	Session duration when assuming roles (default: 3600)
auto_refresh	No	Automatically refresh temporary credentials (default: false)

Complete Configuration Examples

Development with Direct Credentials:

{
    "identifier": "redshift_dev",
    "description": "Development Redshift cluster",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "host": "dev-cluster.abc123.us-east-1.redshift.amazonaws.com",
        "port": 5439,
        "database": "dev_warehouse",
        "username": "dev_user",
        "password": "dev_password"
    },
    "schemas": [
        {
            "name": "public",
            "table_filter": {
                "exclude": ["temp_*", "staging_*"]
            }
        }
    ]
}

Production with IAM Authentication:

{
    "identifier": "redshift_prod",
    "description": "Production analytics warehouse",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "prod-analytics-cluster",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            "profile_name": "production"
        }
    },
    "schemas": [
        {
            "name": "public",
            "table_filter": {
                "exclude": ["staging_*", "temp_*", "admin_*"]
            }
        },
        {
            "name": "marts",
            "table_filter": {
                "include": ["fact_*", "dim_*", "*_summary"]
            }
        }
    ]
}

Cross-Account Role Assumption:

{
    "identifier": "redshift_cross_account",
    "description": "Cross-account Redshift access",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "shared-analytics",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            "role_arn": "arn:aws:iam::987654321098:role/CrossAccountRedshiftAccess",
            "duration_seconds": 7200,
            "auto_refresh": true
        }
    }
}

Redshift Serverless Configuration:

{
    "identifier": "redshift_serverless_analytics",
    "description": "Serverless analytics workgroup",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "namespace_name": "analytics-ns",
        "workgroup_name": "analytics-wg",
        "boto_session_kwargs": {
            "region_name": "us-west-2",
            "profile_name": "serverless"
        }
    },
    "schemas": [
        {
            "name": "analytics",
            "table_filter": {
                "include": ["daily_*", "monthly_*", "summary_*"]
            }
        }
    ]
}

AWS IAM Permissions

For IAM authentication to work, your AWS credentials need appropriate permissions:

Required IAM Permissions for Redshift Clusters:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:DescribeClusters",
                "redshift:GetClusterCredentialsWithIAM"
            ],
            "Resource": [
                "arn:aws:redshift:region:account:cluster/cluster-name",
                "arn:aws:redshift:region:account:dbuser:cluster-name/username"
            ]
        }
    ]
}

Required IAM Permissions for Redshift Serverless:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift-serverless:GetNamespace",
                "redshift-serverless:GetWorkgroup",
                "redshift-serverless:GetCredentials"
            ],
            "Resource": [
                "arn:aws:redshift-serverless:region:account:namespace/namespace-id"
            ]
        }
    ]
}

Security Best Practices

Use IAM Authentication:

Prefer IAM authentication over hardcoded credentials:

# ✅ Recommended: IAM authentication
{
    "boto_session_kwargs": {
        "region_name": "us-east-1",
        "profile_name": "analytics"
    }
}

# ❌ Avoid: Hardcoded credentials
{
    "username": "user",
    "password": "hardcoded_password"
}

Environment Variables:

Use environment variables for sensitive data:

export AWS_PROFILE=analytics
export AWS_REGION=us-east-1

Credential Hierarchy:

AWS credentials are resolved in this order:

1. Explicit credentials in boto_session_kwargs

2. Environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY)

3. AWS CLI profiles (~/.aws/credentials)

4. IAM instance profiles (for EC2)

5. IAM container credentials (for ECS)

Troubleshooting

Common Issues:

1. Authentication failures: Check AWS credentials and IAM permissions

2. Network connectivity: Verify security groups and VPC settings

3. Cluster not found: Check cluster identifier and region

4. SSL errors: Redshift requires SSL connections

5. Token expiration: Use auto_refresh for long-running sessions

Testing AWS Credentials:

Test your AWS credentials independently:

aws redshift describe-clusters --region us-east-1
aws sts get-caller-identity

AWS Redshift Documentation

For comprehensive information about AWS Redshift configuration and best practices:

• AWS Redshift Getting Started

• Redshift Database User Authentication

• Redshift IAM Authentication

• Redshift Serverless

• AWS CLI Configuration

The mcp_ohmy_sql server leverages AWS SDKs and follows AWS best practices for authentication and security.

Next Steps

• Schema Configuration - Configure schemas and table filtering for your Redshift warehouse

• Relational Database Connection Configuration - Learn about SQLAlchemy-based connections

• Connection Configuration - Return to connection configuration overview