Connection Configuration for Amazon Redshift

summary

This is the help page for setting up Connection Configuration for Amazon Redshift.

Authority required for Connection Configuration used for Data Source

It is necessary to create a user with the following permissions

• SELECT TABLE authority in Data Source schema

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

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

Authority required for Connection Configuration used for Data Destination

To transfer data to Amazon Redshift at high speed, TROCCO first temporarily stores data in Amazon S3 and then bulk loads the data into Amazon Redshift using the COPY command.
In order to go through the above transfer process, Connection Configuration used for Data Destination Amazon Redshift must have permission settings for both Amazon S3 and Amazon Redshift.

Setting up permissions for Amazon S3 (creating an IAM user)

Data may be temporarily stored in Amazon S3 for data transfer to Amazon Redshift.
The IAM User credentials are used for this purpose.
Create an IAM User with the following permissions and enter AWS Credentials

• s3:GetObject
• s3:PutObject
• s3:DeleteObject
• s3:ListBucket * Grant permission to list some or all of the objects in an S3 bucket.
• s3:ListAllMyBuckets * Grant permission to list all buckets owned by the authenticated sender of the request
• s3:GetBucketLocation (for buckets in different regions)
  • Amazon S3 Actions, Resources, and Condition Keys - Service Authorization Reference
• sts:GetFederationToken * Grant the right to retrieve the federated user's temporary security credentials (consisting of an access key ID, secret access key and security token)
  • AWS Security Token Service Actions, Resources, and Condition Keys - Service Authorization Reference

To grant minimum privileges, please refer to the following policy.

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

Setting up permissions (creating users) for Amazon Redshift

It is necessary to create a user with the following permissions

• CREATE/DROP TABLE permission in Data Destination schema
• Authority to execute COPY command in Data Destination schema

To grant minimum privileges to users, please 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>