Skip to main content
Version: 9.1

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. Select Next.
  6. Enter a secret name (for example, RedshiftClusterSecret).
  7. Select Next.
  8. Optionally configure automatic rotation, if desired.
  9. Select 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. Select 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. Select 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. Select 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. Select 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. Select 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}"
            ]
        }
    ]
}
  1. Select Next: Tags, then Next: Review.
  2. Name the policy (for example, RedshiftDataAccessPolicy) and select Create policy.

5. Create a New Role​

  1. In the Redshift AWS account, open IAM.
  2. Select Roles and then Create Role.
  3. Select service Redshift and then Redshift - Customizable.
  4. Select Next: Permissions.
  5. Find and select the RedshiftDataAccessPolicy created earlier.
  6. Select Next: Tags, then Next: Review.
  7. Enter a role name (for example, RedshiftDataAccessRole) and select 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. Select 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. Select 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. Select Manage IAM roles.
  5. Select Enter ARN and input the ARN of the newly created role.
  6. Select 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.