Amazon Redshift connection information

summary

This is the help page for setting up Amazon Redshift connection information.

Authorization required for connection information to be used for the transfer source

It is necessary to create a user with the following permissions

• SELECT TABLE authority in the 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>

Authorization required for the connection information used for the forwarding destination

To speed up data transfer to Amazon Redshift, 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, the connection information used for the destination Amazon Redshift must have permission settings for both Amazon S3 and Amazon Redshift.

Setting up permissions related to 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 * Grants 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) related to Amazon Redshift

It is necessary to create a user with the following permissions

• CREATE/DROP TABLE authority in the destination schema
  - Authority to execute COPY command in the 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>