How do I COPY or UNLOAD data from Amazon Redshift to an Amazon S3 bucket in another account?

Last updated: 2020-11-03

I'm trying to COPY or UNLOAD data between Amazon Redshift and an Amazon Simple Storage Service (Amazon S3) bucket in another account. However, I can't assume the AWS Identity and Access Management (IAM) role in the other account. How do I set up cross-account access?

Short description

To access Amazon S3 resources that are in a different account from where Amazon Redshift is in use, perform the following steps:

1.    Create RoleA, an IAM role in the Amazon S3 account.

2.    Create RoleB, an IAM role in the Amazon Redshift account with permissions to assume RoleA.

3.    Test the cross-account access between RoleA and RoleB.

Resolution

Note: The following steps assume that the Amazon Redshift cluster and the S3 bucket are in the same Region. If they're in different Regions, you must add the REGION parameter to the COPY or UNLOAD command.

Create an IAM role in the account that's using Amazon S3 (RoleA)

1.    Open the IAM console.

2.    Choose Policies, and then choose Create policy.

3.    Choose the JSON tab, and then enter an IAM policy like the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "kms:Decrypt",
                "kms:Encrypt",
                "kms:GenerateDataKey"
            ],
            "Resource": [
                "<KMS_KEY_ARN_A_Used_for_S3_encryption>"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": [
                "arn:aws:s3:::awsexamplebucket",
                "arn:aws:s3:::awsexamplebucket/*"
            ]
        }
    ]
}

Replace awsexamplebucket with the name of the S3 bucket that you want to access. Also, replace KMS_KEY_ARN_A_Used_for_S3_encryption with the Amazon Resource Name (ARN) of the AWS Key Management Service (AWS KMS) used to encrypt the S3 bucket.

Note: The KMS permissions aren't required if the S3 bucket isn't encrypted with an AWS KMS key.

4.    Choose Review policy.

5.    Enter a name for the policy (such as policy_for_roleA), and then choose Create policy.

6.    From the navigation pane, choose Roles.

7.    Choose Create role.

8.    Choose Another AWS account for the trusted entity role.

9.    Enter the AWS account ID of the account that's using Amazon Redshift.

10.    Choose Next: Permissions, and then select the policy that you just created (policy_for_roleA).

11.    Choose Next: Tags, and then choose Next: Review. Note that tags aren't required.

12.    Enter a role name (such as RoleA).

13.    Choose Create role.

Create an IAM role (RoleB) in the Amazon Redshift account with permissions to assume RoleA

1.    Open the IAM console.

2.    Choose Policies, and then choose Create policy.

3.    Choose the JSON tab, and then enter an IAM policy like the following:

{
    "Version": "2012-10-17",
    "Statement": [
            {
            "Sid": "CrossAccountPolicy",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "AmazonS3AccountRoleARN"
        }
    ]
}

Replace AmazonS3AccountRoleARN with the ARN for RoleA (arn:aws:iam::Amazon_S3_Account_ID:role/RoleA).

4.    Choose Review policy.

5.    Enter a name for the policy (such as policy_for_roleB), and then choose Create policy.

6.    From the navigation pane, choose Roles.

7.    Choose Create role.

8.    Choose AWS service as your trusted entity type.

9.    Choose Redshift.

10.    Choose Redshift - Customizable.

11.    Choose Next: Permissions, and then select the policy that you just created (policy_for_roleB).

12.    Choose Next: Tags, and then Next: Review. Note that tags aren't required.

13.    Enter a role name (such as RoleB).

14.    Choose Create role.

15.    Associate the IAM role (RoleB) with your Amazon Redshift cluster.

Note: By chaining IAM roles in Amazon Redshift, the Amazon Redshift cluster assumes RoleB, which then assumes RoleA. This role chaining gives Amazon Redshift access to Amazon S3.

Test the cross-account access between your S3 bucket and Amazon Redshift

Run the COPY command to import data from your S3 bucket to Amazon Redshift:

copy table_name from 's3://awsexamplebucket/crosscopy1.csv' iam_role 'arn:aws:iam::Amazon_Redshift_Account_ID:role/RoleB,arn:aws:iam::Amazon_S3_Account_ID:role/RoleA' delimiter ',' removequotes;

Replace these values in the example:

table_name: the Amazon Redshift table that you want to copy the Amazon S3 data into
s3://awsexamplebucket/crosscopy1.csv: the S3 bucket that you want to copy the data from
Amazon_Redshift_Account_ID: the AWS account ID for the Amazon Redshift account
RoleB: the second role that you created
Amazon_S3_Account_ID: the AWS account ID for the Amazon S3 account
RoleA: the first role that you created

Then, run the UNLOAD command to unload the data from Amazon Redshift to your S3 bucket, verifying cross-account access:

unload ('select * from table_name') to 's3://awsexamplebucket/folder/table_name_' iam_role 'arn:aws:iam::Amazon_Redshift_Account_ID:role/RoleB,arn:aws:iam::Amazon_S3_Account_ID:role/RoleA' KMS_KEY_ID 'ARN_KMS_KEY_ID' ENCRYPTED;

Replace these values in the example:

table_name: the Amazon Redshift table that you want to unload to the S3 bucket
s3://awsexamplebucket/folder/test.dat: the S3 path where the Amazon Redshift data is being unloaded to
Amazon_Redshift_Account_ID: the AWS account ID for the Amazon Redshift account
RoleB: the second role that you created
Amazon_S3_Account_ID: the AWS account ID for the Amazon S3 account
RoleA: the first role that you created
ARN_KMS_KEY_ID: ARN of the KMS key ID used to encrypt the S3 bucket