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

Short description

To access Amazon S3 resources that are in a different account from where Amazon Redshift is in use, perform the following steps. These steps apply to both Redshift Serverless and Redshift provisioned data warehouse:

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.

Note: These steps work regardless of your data format. However, there might be some changes in the COPY and UNLOAD command syntax while performing these operations. For example, if you're using the Parquet data format, your syntax looks like this:

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 steps assume that the Amazon Redshift cluster and the S3 bucket are in the same Region. If they're in different Regions, then 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 AWS 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 (RoleB).

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.    Attach the IAM role (RoleB) to your Amazon Redshift cluster:

        For a provisioned Redshift cluster, see Associate the IAM role with your cluster.

        -or-

        For Redshift Serverless, see Assigning IAM roles to a namespace in Security and connections in Amazon Redshift Serverless.

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

---

Related information

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

How can I copy S3 objects from another AWS account?

IAM permissions for COPY, UNLOAD, and CREATE LIBRARY