How can I create cross-account access between Amazon Redshift and Amazon S3?

Last updated: 2020-06-25

I want to UNLOAD or COPY data between Amazon Redshift and an Amazon Simple Storage Service (Amazon S3) bucket that's in a different AWS account.

Short description

Create an AWS Identity and Access Management (IAM) role in the Amazon S3 account (RoleA). Then, create an IAM role in the Amazon Redshift account (RoleB) that has permissions to assume RoleA. The Amazon Redshift cluster assumes RoleB, which then assumes RoleA, giving Amazon Redshift access to S3. This process is called chaining roles.

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.

In your S3 account, perform the following steps:

1.    Open the IAM console.

2.    Choose Policies, and then choose Create policy.

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

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:Get*",       
        "s3:List*",
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::awsbucket",
        "arn:aws:s3:::awsbucket/*"
      ]
    }
  ]
}

Note: Replace awsbucket with the name of the S3 bucket that you want to access.

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 and then choose Create role.

7.    For Select type of trusted entity, choose Another AWS account.

8.    For Account ID, enter the ID of the Amazon Redshift account. For more information, see Finding Your AWS Account ID.

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

10.    Choose Next: Tags and then choose Next: Review. You do not need to add any tags.

11.    Enter a Role name, and then choose Create role. In the following examples, we refer to the newly created role as RoleA.

In your Amazon Redshift account, perform the following steps:

1.    Open the IAM console.

2.    Choose Policies, and then choose Create policy.

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

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

Note: Replace ARN_for_RoleA with the Amazon Resource Name (ARN) of RoleA, which you created in the S3 account.

4.    Choose Review policy.

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

6.    On the IAM console, choose Roles from the navigation pane, and then choose Create role.

7.    For Select type of trusted entity, choose AWS service.

8.    Choose Redshift, and then choose Redshift - Customizable.

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

10.    Choose Next: Tags, and then Next: Review. You do not need to add policies or tags.

11.    Enter a Role name, and then choose Create role. In the following examples, we refer to the newly created role as RoleB.

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

Test the setup

Run a command similar to the following to test the COPY command. Replace these values in the example:

table_name: The Amazon Redshift table that you want to copy the S3 data into
s3://awsbucket/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 S3 account
RoleA: The first role that you created

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

Run a command similar to the following to test the UNLOAD command. Replace these values in the example:

table_name: The Amazon Redshift table that you want to unload to the S3 bucket
s3://awsbucket/folder/test.dat: The S3 path where you want to unload the Amazon Redshift data
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 S3 account
RoleA: The first role that you created

unload ('select * from table_name') to 's3://awsbucket/folder/test.dat' iam_role 'arn:aws:iam::Amazon_Redshift_Account_ID:role/RoleB,arn:aws:iam::Amazon_S3_Account_ID:role/RoleA' ;