Redshift Connection Configuration

summary

This is a help page for setting Redshift connection information.

In order to transfer data to Redshift at high speed, trocco saves the data in S3 and bulk loads it to Redshift with the COPY command.
Therefore, in the Redshift connection information, it is necessary to set permissions etc. for both S3 and Redshift.

Create IAM User

Data transfer to Redshift may temporarily store data in S3.
At that time, you will use IAM User credentials, so create an IAM User with the following permissions and enter your AWS credentials.

• s3:GetObject
• s3:PutObject
• s3:DeleteObject
• s3:ListBucket
• sts:GetFederationToken
• s3: GetBucketLocation (when using buckets in different regions)

To grant the least privileges, refer to the following policies:

{
     "Version": "2012-10-17",
     "Statement": [
         {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucketMultipartUploads",
                 "s3:AbortMultipartUpload",
                 "sts:GetFederationToken",
                 "s3:ListBucket",
                 "s3:DeleteObject",
                 "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::<YOUR_DESTINATION_BUCKET_NAME>",
                "arn:aws:s3:::<YOUR_DESTINATION_BUCKET_NAME>/*",
                 "arn:aws:sts::<YOUR_AWS_ACCOUNT_ID>:federated-user/<YOUR_IAM_USER_NAME>"
           ]
        }
    ]
}

Redshift user creation and permission setting

You must create a user with the following privileges:

• CREATE/DROP TABLE PERMISSION IN DESTINATION SCHEMA
• Permission to execute the COPY command in the destination schema

To grant the minimum privileges to the user, refer to the following SQL command.

ALTER DEFAULT PRIVILEGES FOR USER <your_db_user_name>　IN SCHEMA <your_destination_schema>
GRANT
　　　　SELECT,
　　　　INSERT,
　　　　DELETE
ON TABLES TO <your_db_user_name>;
GRANT USAGE ON SCHEMA <your_destination_schema> TO <your_db_user_name>