Redshift

How to set up the Redshift connection and additional notes on using it

With Codatum, you can connect, query, and manage data of Redshift.

This feature is currently in preview. Functionality and availability may change without notice.

We'd love your feedback.

How to Set Up

Redshift integration targets RA3 node clusters and serverless configurations.

1. Necessary preparations in AWS Console

1. Grant the Necessary Privileges

Create a role for integration and grant privileges.

• Grant USAGE privilege on the schema you want to use to that role

• Grant SELECT privilege on the tables you want to use to that role

• Grant SELECT privilege on the system view svv_table_info to that role

2. Create Secret in Secrets Manager

• Specify the Data Warehouse you want to connect to and create a Secret

• Add a tag named RedshiftDataFullAccess to the created Secret (Value can be empty)

3. Create IAM User and Grant Policies

• Create an IAM user for integration and grant the following policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DataAPIPermissions",
            "Effect": "Allow",
            "Action": [
                "redshift-data:BatchExecuteStatement",
                "redshift-data:ExecuteStatement",
                "redshift-data:CancelStatement",
                "redshift-data:ListStatements",
                "redshift-data:GetStatementResult",
                "redshift-data:DescribeStatement",
                "redshift-data:ListDatabases",
                "redshift-data:ListSchemas",
                "redshift-data:ListTables",
                "redshift-data:DescribeTable"
            ],
            "Resource": "*"
        },
        {
            "Sid": "SecretsManagerPermissions",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "Specify the Secret ARN created in step 1-2"
            ],
            "Condition": {
                "StringLike": {
                    "secretsmanager:ResourceTag/RedshiftDataFullAccess": "*"
                }
            }
        },
        {
            "Sid": "ServiceLinkedRole",
            "Effect": "Allow",
            "Action": "iam:CreateServiceLinkedRole",
            "Resource": "arn:aws:iam::*:role/aws-service-role/redshift-data.amazonaws.com/AWSServiceRoleForRedshift",
            "Condition": {
                "StringLike": {
                    "iam:AWSServiceName": "redshift-data.amazonaws.com"
                }
            }
        }
    ]
}

4. Additional Steps Required Only for Serverless

• Add a tag named RedshiftDataFullAccess to the Workgroup you want to integrate with (Value can be empty)

• Create the following policy in IAM and grant the following policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ServerlessWorkgroupInfo",
            "Effect": "Allow",
            "Action": [
                "redshift-serverless:GetWorkgroup"
            ],
            "Resource": "*"
        }
    ]
}

2. Register Redshift connection in Codatum

Finally, create a Redshift connection in Codatum.

• Select Add Connection from Connections in Workspace Settings

• Enter Connection Name

• Select Access Level

• Enter Account ID

  • This is your AWS account ID.

• Enter Region

  • This is the region where your Redshift resources are located.

• Select Connection Type

  • If you select Cluster:

    • Enter Cluster Identifier

    • Enter Default Database

      • This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables.

  • If you select Serverless:

    • Enter Serverless Namespace

    • Enter Serverless Workgroup Name

    • Enter Default Database

      • This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables.

• Enter AWS Access Key

  • Use the access key of the IAM user prepared in Step 1.

• Enter AWS Secret Access Key

  • Use the secret access key of the IAM user prepared in Step 1.

• Enter Secret ARN

  • Use the Secret ARN of the Secret prepared in Step 1.

Once you have completed the input, perform a connection test and then save the connection.