Redshift Configuration (AWS)

The steps below detail how to configure Qrvey to access Redshift clusters for loading data into Qrvey for data analysis. These changes allow Qrvey to access the cluster and enable the cluster to export data to S3 for access by Qrvey.

1. Create a New Secret for the Redshift Cluster

For Standard Redshift Clusters:

1. Open the AWS console.
2. Use the Redshift AWS account.
3. Open AWS Secrets Manager.
4. Create a new secret:
   • Select Credentials for a Redshift cluster.
   • Specify the User name and Password for the Redshift cluster.
   • Select the DB Cluster.
5. Click Next.
6. Enter a secret name (e.g., RedshiftClusterSecret).
7. Click Next.
8. Optionally configure automatic rotation, if desired.
9. Click Next and then Store.

Note the secret's ARN string for later use.

For Redshift Serverless Mode:

1. Open the AWS console.

2. Use the Redshift AWS account.

3. Open AWS Secrets Manager.

4. Create a new secret:

   • Select Other type of secret.
   • Specify key/value pairs using the Plaintext feature. Example:

   {
     "username": "MY_USERNAME",
     "password": "MY_PASSWORD",
     "engine": "redshift",
     "host": "default.MY_ACCOUNT.us-east-1.redshift-serverless.amazonaws.com",
     "port": 5439,
     "workgroupName": "default"
   }

5. Click Next and then Store.

Note the secret's ARN string for later use.

2. Set Permissions for the Kubernetes Cluster

1. Use the Qrvey AWS account.

2. Open Amazon Elastic Kubernetes Service.

3. Click Clusters and select the Qrvey Kubernetes cluster name.

4. Go to the Access tab and locate the Pod Identity associations section.

5. Open the associated IAM role.

6. Click Add inline policy.

7. Switch to the JSON tab and paste the following policy, replacing {AWS_ACCOUNT_REDSHIFT} with the AWS account number for the Redshift cluster:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "VisualEditor2",
               "Effect": "Allow",
               "Action": [
                   "sts:AssumeRole",
                   "sts:TagSession"
               ],
               "Resource": [
                   "arn:aws:iam::{AWS_ACCOUNT_REDSHIFT}:role/RedshiftDataAccessRole"
               ]
           }
       ]
   }

3. Add a Trust Relationship for the Role DbDatasourceExportToS3Role

1. In the Qrvey AWS account, open IAM.

2. Search for the export role containing DbDatasourceExportToS3Role.

3. Note the ARN of the role for later use.

4. Click the Trust relationships tab and then Edit trust relationship.

5. Paste the following trust relationship, replacing the appropriate ARN:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Principal": {
                   "AWS": [
                       "arn:aws:iam::{AWS_ACCOUNT_REDSHIFT}:role/RedshiftDataAccessRole"
                   ]
               },
               "Action": "sts:AssumeRole"
           }
       ]
   }

6. Save the trust relationship.

4. Create a New Policy

1. Switch to the Redshift AWS account.
2. Open AWS Identity and Access Management (IAM).
3. Click Policies and then Create policy.
4. Switch to the JSON tab and paste the following policy, replacing the appropriate ARNs:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "redshift-data:GetStatementResult",
                "redshift-data:CancelStatement",
                "redshift-data:ExecuteStatement",
                "redshift-data:ListTables",
                "redshift:GetClusterCredentials",
                "redshift:GetClusterCredentialsWithIAM"
            ],
            "Resource": "arn:aws:redshift:<awsRegion>:<accountId>:cluster:<clusterName>"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "redshift-data:DescribeStatement"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "{SECRET_ARN}"
            ]
        },
        {
            "Sid": "VisualEditor3",
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole",
                "sts:TagSession"
            ],
            "Resource": [
                "{EXPORT_TO_S3_ARN}"
            ]
        }
    ]
}

5. Click Next: Tags, then Next: Review.
6. Name the policy (e.g., RedshiftDataAccessPolicy) and click Create policy.

5. Create a New Role

1. In the Redshift AWS account, open IAM.
2. Click Roles and then Create Role.
3. Select service Redshift and then Redshift - Customizable.
4. Click Next: Permissions.
5. Find and select the RedshiftDataAccessPolicy created earlier.
6. Click Next: Tags, then Next: Review.
7. Enter a role name (e.g., RedshiftDataAccessRole) and click Create Role.
8. Note the role's ARN string for later use.

6. Add a Trust Relationship to the New Role

1. Open the Trust relationships tab for the new role.

2. Click Edit trust relationship.

3. Paste the following trust relationship:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Principal": {
                   "AWS": [
                       "arn:aws:iam::{QRVEY_AWS_ACCOUNT}:root"
                   ]
               },
               "Action": [
                   "sts:AssumeRole",
                   "sts:TagSession"
               ]
           },
           {
               "Effect": "Allow",
               "Principal": {
                   "Service": "pods.eks.amazonaws.com"
               },
               "Action": [
                   "sts:AssumeRole",
                   "sts:TagSession"
               ]
           },
           {
               "Effect": "Allow",
               "Principal": {
                   "Service": "redshift.amazonaws.com"
               },
               "Action": "sts:AssumeRole"
           }
       ]
   }

4. Click Update Trust Policy.

7. Associate the Redshift Cluster with the New Role

1. Open AWS Redshift.
2. Select the desired cluster.
3. Go to the Properties tab.
4. Click Manage IAM roles.
5. Select Enter ARN and input the ARN of the newly created role.
6. Click Associate IAM Role and then Save changes.

8. Configure the Qrvey Application

1. Open the Qrvey application.
2. Navigate to Datasets > Connections.
3. Create a new connection for Redshift.
4. Enter the secret ARN and role ARN created earlier.
5. Test the new connection.

Debugging

Connection Test Timeout Error

This error occurs when the Postgres driver cannot connect to Redshift during the connection test. If Qrvey is in a different account, ensure a VPC is configured for DBDatasourcePump.

Not Authorized to Assume IAM Role

Error in Redshift SQL UNLOAD command: ERROR: User
arn:aws:redshift:{region}:{AccountId}:{user}:{redshift_account}/{user} is not authorized to assume IAM
Role arn:aws:iam::{AccountId}:role/RedshiftDataAccessRole,arn:aws:iam::{AccountId}:role/
{env}_dataload_DbDatasourceExportToS3Role.

This error occurs when the trust relationship for the role DbDatasourceExportToS3Role is not set. Refer to Step 3 above.