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?

Answer 1

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

1. AWS Account A has an S3 bucket called source-bucket-account-a.
2. AWS Account B has a Redshift cluser called TargetCluster.
3. 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

4. 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.