How to connect ai agent on xpander to AWS Redshift Using IAM and Secrets Manager

Overview

This guide walks you through setting up a secure connection from xpander to AWS Redshift Serverless using IAM credentials and Secrets Manager authentication. This approach provides a secure, scalable way to query your Redshift data warehouse without exposing database credentials directly.

By the end of this tutorial you will have an AI agent capable of query data from redshift

Architecture

The connection uses a layered security approach:

1. IAM User: Provides AWS-level permissions to access Secrets Manager and Redshift Data API

2. Secrets Manager: Securely stores the actual Redshift database credentials

3. Redshift Data API: Executes queries using credentials from Secrets Manager

This means your IAM user acts as a secure intermediary - it can retrieve credentials and execute queries, but the actual database permissions are controlled by the Redshift user stored in the secret.

Prerequisites

Before you begin, ensure you have:

• AWS account with permissions to create IAM users and policies

• Redshift Serverless workgroup or provisioned cluster

• Database credentials stored in AWS Secrets Manager

• xpander account with connector configuration access

Step 1: Create IAM User with Required Permissions

Create the IAM User

aws iam create-user \
  --user-name redshift-demo-readonly \
  --tags Key=Purpose,Value

Create Secrets Manager Policy

This policy allows the IAM user to read your Redshift credentials:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadRedshiftSecret",
      "Effect": "Allow",
      "Action": [
        "secretsmanager:GetSecretValue",
        "secretsmanager:DescribeSecret"
      ],
      "Resource": "arn:aws:secretsmanager:<region>:<account-number>:secret:<secret-name>"
    }
  ]
}

Save this as redshift-secret-policy.json and create the policy:

aws iam create-policy \
  --policy-name RedshiftSecretReadOnly \
  --policy-document file://redshift-secret-policy.json \
  --description "Read-only access to Redshift credentials in Secrets Manager"

Create Redshift Data API Policy

This policy allows the IAM user to execute queries via the Redshift Data API:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "RedshiftDataAPIAccess",
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift-data:ListStatements",
        "redshift-data:CancelStatement"
      ],
      "Resource": "*"
    },
    {
      "Sid": "RedshiftServerlessAccess",
      "Effect": "Allow",
      "Action": [
        "redshift-serverless:GetWorkgroup",
        "redshift-serverless:GetNamespace",
        "redshift-serverless:GetCredentials"
      ],
      "Resource": [
        "arn:aws:redshift-serverless:us-west-2:<account-number>:workgroup/*",
        "arn:aws:redshift-serverless:us-west-2:<account-number>:namespace/*"
      ]
    }
  ]
}

Create the policy

aws iam create-policy \
  --policy-name RedshiftDataAPIAccess \
  --policy-document file://redshift-data-api-policy.json \
  --description "Redshift Data API execution permissions"

Attach Policies to User

# Get your AWS account ID
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)

# Attach Secrets Manager policy
aws iam attach-user-policy \
  --user-name redshift-demo-readonly \
  --policy-arn "arn:aws:iam::${ACCOUNT_ID}:policy/RedshiftSecretReadOnly"

# Attach Redshift Data API policy
aws iam attach-user-policy \
  --user-name redshift-demo-readonly \
  --policy-arn "arn:aws:iam::${ACCOUNT_ID}:policy/RedshiftDataAPIAccess"

Create Access Keys

aws iam create-access-key --user-name

Save the output - you'll need the AccessKeyId and SecretAccessKey for Xpander configuration.

Step 2: Store Redshift Credentials in Secrets Manager

If you haven't already stored your Redshift database credentials in Secrets Manager:

aws secretsmanager create-secret \
  --name redshift-test-cluster-credentials \
  --description "Redshift database credentials for demo cluster" \
  --secret-string '{"username":"admin","password":"YourSecurePassword123"}' \
  --region

Note the Secret ARN from the output - you'll need it for xpander connector screen.

Step 3: Configure xpander Redshift Connector

Now that your AWS infrastructure is ready, configure the connector in xpander:

Navigate to Connectors : Go to Settings → Connectors. Click Add Connector. Select AWS Redshift

Fill in the following fields:

Service: AWS Redshift
Connection Name: xpander-wh (or your preferred name)

Authentication Settings

• Method Type: AWS IAM credentials

• Redshift Authentication Method: Secrets Manager

• Region: US West (Oregon) / us-west-2

IAM Credentials

• Access Key ID: <your-access-key> (from Step 1.5)

• Secret Access Key: <your-secret-key> (from Step 1.5)

• Session Token: Leave empty (not needed for standard IAM users)

Secrets Manager Configuration

Secret ARN: this-is-the-arn-secret-of-the-user-with-data-access

Example: