allow Amazon Redshift to load data from S3 bucket in another AWS account and possibly in different AWS Region

aws-redshift-copy-s3-role.md

Summary

This quick document highlight how allow Amazon Redshift to load data from S3 bucket in another AWS account and possibly in different AWS Region

Setup

AWS Account A
region us-west-2 -> S3 bucket my.bucket

AWS Account B
us-east-1 redshift-cluster

Need

AWS Redshift cluster in Accou nt A need to load S3 bucket objects from AWS Account B in a different AWS region.

Implementation using console

On Account B

Go to IAM->Roles

Create new role

 Select role type "Amazon Redshift"
 Do not select any policy and click next
 Set role name
 Set description
 Click next

We will copy the role ARN and add some role Inline Policies.

 Back in role list select the newly created role.
 Once in the role take note of the role ARN (Will be required in Account B)
   Example : arn:aws:iam::111111111111:role/example_role
 In Permissions tab expand the Inline Policies
 Then click on the "There are no inline policies to show. To create one, click here."
 Select the custom policy radio button
 Enter policy name ( Example: redshift_policy2 )
 Then Set this as policy :
 (Replacing items in <>)

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListAllMyBuckets",
                "s3:GetBucketLocation"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::my.bucket"
                <add more bucket here if required>
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject"
                <Add more actions here if required>
            ],
            "Resource": [
                "arn:aws:s3:::my.bucket/*"
                 <Add more bucket here if required>
            ]
        }
    ]
}

  Click validate policy, then apply policy if all goes well.

Apply the role to the Redshift cluster

  Click on services -> Redshift
  In the left pane click on "Clusters"
  Select your redshift cluster and click on "Manage IAM roles" button.
  Select the newly created role in the "Available roles" dropdown list, then "Apply changes".
  Wait for the cluster to be updated.

On Account A

We need to add an access policy to the S3 bucket, to allow the Redshift role from Account B.

  In Services go to S3
  Search for the target S3 bucket and click on it.
  Once in the bucket root click on the "Permissions" tab
  Then click on the "Bucket Policy" button
  Set the policy :

  Replace the Principal role ARN with the one that you copied from the Account B
  (Our example uses : arn:aws:iam::11111111111:role/example_role)
  

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::11111111111:role/example_role"
            },
            "Action": [
                "s3:List*",
                "s3:Get*"
            ],
            "Resource": [
                "arn:aws:s3:::my.bucket",
                "arn:aws:s3:::my.bucket/**"
            ]
        }
    ]
}

That should be it for roles to work

Then you can use the Redshift COPY command to load data.

Example :

COPY mytable FROM 's3://my.bucket/path/to/object'
iam_role 'arn:aws:iam::11111111111:role/example_role'
region 'us-west-2';

At this point you need to read about COPY command and change it to your needs. See : Using the COPY Command to Load from Amazon S3