Copy to Redshift from another accounts S3 bucket

Question

Is it possible to copy from one AWS accounts S3 bucket into another AWS accounts Redshift cluster? The way I tried to do it was to log in using SQL Workbench to my AWS Account (Account1) and used a IAM User of (Account2) to copy the file over like this:

copy my_table (town,name,number) from 's3://other-s3-account-bucket/fileToCopy.tsv' credentials 'aws_access_key_id=<other_accounts_aws_access_key_id>;aws_secret_access_key=<other_accounts_aws_secret_access_key>' delimiter '\t';

I know the other account's user has s3 permissions after double checking. Do I have share IAM users or setup different permissions in order to do this?

Matt Houser · Accepted Answer · 2016-04-19 23:38:50Z

You will need to "pull" the data from the other account's S3 bucket.

AWS Account A has an S3 bucket called source-bucket-account-a.
AWS Account B has a Redshift cluser called TargetCluster.
On bucket source-bucket-account-a, add a bucket policy allowing AWS Account B to read files.

A sample policy:

{ "Version": "2012-10-17", "Statement": [ { "Sid": "DelegateS3Access", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::<account-b-number>:root" }, "Action": [ "s3:Get*", "s3:List*" ], "Resource": [ "arn:aws:s3:::source-bucket-account-a", "arn:aws:s3:::source-bucket-account-a/*" ] } ] }

It's very similar to the following: http://docs.aws.amazon.com/AmazonS3/latest/dev/example-walkthroughs-managing-access-example2.html

or the following: http://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_policy-examples.html

Once the bucket policy is in place, you use the credentials for AWS Account B to run the copy command because it owns the Redshift cluster. In the copy command, you specify the bucket by it's name source-bucket-account-a.

The bucket policy has granted read access to AWS Account B so it can "pull" the data into Redshift.

What if I want to use iam_role instead of root access_key and access_id?How does Account A give access to an iam_role for S3 bucket source-bucket-account-a?
What if AWS Account A has bucket called "TEST" and AWS Account B has bucket called "TEST"? How does it know to which you refer?
@ChiragAgrawal the "root" notation in the Principal property does not indicate root credentials. It simply is the notation to indicate the AWS account as a whole. Using the policy above, you sbould still use IAM roles and users.
@jack Two AWS accounts cannot have S3 buckets with the same names. S3 bucket names must be globally unique, so your case cannot happen.

Collectives™ on Stack Overflow

Copy to Redshift from another accounts S3 bucket

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related