How do I copy or unload data from Amazon Redshift to an Amazon S3 bucket that's in another account?

Short description

To access Amazon S3 resources that are in a different account, complete the following steps:

1. Create an IAM role in the Amazon S3 account (RoleA).
2. Create an IAM role in the Amazon Redshift account (RoleB) with permissions to assume RoleA.
3. Test the cross-account access between RoleA and RoleB.

Note: The preceding steps apply to both Redshift Serverless and Redshift provisioned data warehouse and work for all data formats. However, you might need to modify the COPY and UNLOAD command syntax for certain data formats. For example, if you use the Parquet data format, then you must use the following syntax:

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 FORMAT AS PARQUET;

Resolution

Note: The following procedure assumes that the Amazon Redshift cluster and the S3 bucket are in the same AWS Region. If your cluster and bucket are in different Regions, then you must add the REGION parameter to the COPY or UNLOAD command.

Create an IAM role in the account that uses Amazon S3 (RoleA)

Complete the following steps:

1. Open the IAM console.
2. Choose Policies, and then choose Create policy.
3. Choose the JSON tab, and then enter the following IAM policy:
   Note: If the S3 bucket isn't encrypted with an AWS Key Management Service (AWS KMS) key, then remove the AWS KMS permissions.

   {  
      "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/*"  
         ]  
       }  
     ]  
   }

   Note: Replace awsexamplebucket with the name of the S3 bucket. If you use AWS KMS key encryption, then replace KMS_KEY_ARN_A_Used_for_S3_encryption with the ARN of the  AWS KMS key.
4. Choose Review policy.
5. Enter a name for the policy, and then choose Create policy.
6. In the navigation pane, choose Roles.
7. Choose Create role.
8. Choose Another AWS account as the trusted entity role.
9. Enter the account ID of the account that uses Amazon Redshift.
10. Choose Next: Permissions, and then select the policy.
11. (Optional) Choose Next: Tags, and then add tags.
12. Choose Next: Review.
13. Enter a role name.
14. Choose Create role.

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

Complete the following steps:

1. Open the IAM console.
2. Choose Policies, and then choose Create policy.
3. Choose the JSON tab, and then enter the following IAM policy:

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

   Note: 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, and then choose Create policy.
6. In 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.
12. (Optional) Choose Next: Tags, and then add tags.
13. Choose Next: Review.
14. Enter a role name.
15. Choose Create role.
16. Attach the RoleB to your Amazon Redshift cluster:
    For a provisioned Redshift cluster, see Associate the IAM role with your cluster.
    -or-
    For Redshift Serverless, see Granting permissions to Amazon Redshift Serverless.

Note: When you chain IAM roles in Amazon Redshift, the Amazon Redshift cluster assumes RoleB, and then RoleB assumes RoleA. 

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

Complete the following steps:

1. 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;

2. To verify that cross-account access is granted, run the UNLOAD command to unload the data from Amazon Redshift to your S3 bucket:

   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;

Note: In the preceding commands, replace the following values:

• table_name: Amazon Redshift table that you're copying the Amazon S3 data into
• s3://awsexamplebucket/crosscopy1.csv: S3 file path that you're copying the data from
• Amazon_Redshift_Account_ID: account ID for the Amazon Redshift account
• Amazon_S3_Account_ID: account ID for the Amazon S3 account
• (Optional) ARN_KMS_KEY_ID: ARN of the KMS key ID that you used to encrypt the S3 bucket

Related information

How do I provide cross-account access to objects that are in Amazon S3 buckets?

How do I copy S3 objects from another AWS account?

IAM permissions for COPY, UNLOAD, and CREATE LIBRARY