AWS Redshift - Cross-Account Unload & Copy with S3
Use Case Requirements
You have an occasional need to copy and / or unload data from an S3 bucket from one account to a Redshift cluster in another account.
This can be achieved through Redshift’s
UNLOAD commands in combination with IAM Roles and Role chaining.
IAM Role chaining also works in Redshift for cross-account
UNLOAD to S3 commands.
As shared in my previous blog, IAM Role chaining allows you to set up a chain of trusted entities between AWS accounts, enabling you to delegate access from one account to another.
This feature is also useful when you have a scenario where a Redshift cluster in one account needs to perform
UNLOAD operations on S3 buckets owned by a different account.
High-Level Solution Visual
To set up permissions and IAM Role chaining for cross-account
UNLOAD commands in Redshift, you can take the following steps:
- Set up an IAM role in the source AWS account (the account where the Redshift cluster resides) that will be allowed to assume a role in the destination AWS account (the account that owns the S3 buckets).
- In the source account, create an IAM role with the necessary permissions to perform
UNLOADoperations in Redshift.
- Add a trust policy to the IAM role, specifying the destination account as the trusted entity. Below is an example of a trust policy for assuming a role in the destination account:
2. In the destination account, create an IAM role that grants the necessary permissions to access the S3 buckets. This role will be assumed by the IAM role in the source account during
- Create an IAM role with the required S3 permissions for the S3 buckets involved in the
UNLOADcommands. For example, the role should have permissions to read from the source bucket during
COPYor write to the target bucket during
3. Attach an IAM policy to the role in the destination account, allowing the role from the source account to assume it. Below is an example of an IAM policy that grants permission for the source role to assume the destination role:
4. When executing the
UNLOAD command in Redshift, use the IAM role in the source account that has the trust relationship configured with the destination account. Specify the ARN of the role in the source account using the
COPY table_name FROM 's3://<source-bucket>/<source-prefix>'
UNLOAD ('SELECT * FROM table_name') TO 's3://<destination-bucket>/<destination-prefix>'
In these examples,
<source-account-id> refers to the ID of the source AWS account,
<source-role-name> refers to the name of the IAM role in the source account,
<destination-account-id> refers to the ID of the destination AWS account, and
<destination-bucket> are the names of the S3 buckets involved.
By setting up IAM Role chaining, you establish a trust relationship between the two accounts, allowing Redshift in the source account to access the S3 buckets owned by the destination account using the specified IAM roles.
Remember to adjust the permissions and trust policies according to your specific requirements and naming conventions for the AWS accounts, IAM roles, and S3 buckets involved.