This is a problem that has been occurring a lot for me lately and I have done multiple times and still manage to fail at it. I keep either missing parts, getting confused part way, or feeling as if I am blindly copy/pasting answers I don't understand. Thus this is my own attempt at writing out the solution for this problem in a way that teaches how and what relationships are being formed and in a hope that it may help others. In essense, this is a rewrite of the AWS documentation post aws.amazon.com/premiumsupport/knowledge-center/s3-access-denied-redshift-unload/ but in my own words and personalized examples in a format I personally find easier to comprehend.

The Problem

The situation this article is solving is when you have two separate AWS accounts. One of these accounts is running a Redshift cluster and the other is hosting your S3 bucket storage. What you would like to do is have Redshift write to the S3 buckets in the other account in a safe and secure manner, but continue running in its own account. Along with that, naming things is hard, so lets have a naming convention that makes sense as we go along

Parameters And Conditions

To keep things straight we are going use the following names for the various accounts, roles, and policies that we will be creating to solve this problem:

  • AWSRedshiftAccount - Account with Redshift Running on it. Account ID:110000000022
    • RedshiftClusterRole - The Role your redshift cluster assumes when doing anything
      • S3AWSS3AccountFromAWSRedshiftAccount-AssumePolicy - A policy allowing the RedshiftClusterRole to assume the S3AWSS3AccountFromAWSRedshiftAccountRole in the AWSS3Account
      • S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy - A policy specifying what buckets and what actions on those buckets can be done by redshift in AWSRedshiftAccount on the S3 buckets in AWSS3Account
  • AWSS3Account - Account with Redshift Running on it. Account ID: 550000000066
    • RedshiftStorageBucket - The name of the S3 Bucket we will be storing our Redshift data in
    • S3AWSS3AccountFromAWSRedshiftAccountRole - A role assumed by the RedshiftClusterRole to access S3 Bucket resources
      • S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy - A policy specifying what buckets and what actions on those buckets can be done by redshift in AWSRedshiftAccount on the S3 buckets in AWSS3Account
    • S3AWSS3AccountFromAWSRedshiftAccountTrustPolicy - A trust policy applied to the S3AWSS3AccountFromAWSRedshiftAccountRole specifying that the RedshiftClusterRole in the AWSRedshiftAccount is a trusted source to login with
    • S3AWSS3AccountFromAWSRedshiftAccountBucketPolicy - A policy applied to the bucket itself in S3 of the S3AWSAccount specifying access permissions for the S3AWSS3AccountFromAWSRedshiftAccountRole role

The above essentially gives a high level of all the pieces we are going to create and their relationships to each other. I have put policies as sub-bullets if they belong to the above role as a visual helper as well. In total we will be creating 2 Roles on each account and adding a number of policies and trust policies to them to give them the appropriate permissions to communicate with each other. Then we create an additional policy that is explicitly applied to the S3Buckets. Note that also some of these policies have identical names, this is intentional, as they do the identical things. The way AWS IAM works requires this duplication as inter-account communication is rather blind when it executes

Also, as an additional note, everything will be running in us-east-1. In more generic terms, this means this solution is only tried and trued with setups within the same regions. If you are communicating across regions, then I make no promises.

The Solution

Note! Order of this tutorial does matter. This tutorial has been written in the order listed in the overview to keep it sane and allow you to wrap your head around it. There will be issues if you follow it in the exact order though! AWS verifies Roles and Policy relations before they are submitted and if a Role or Policy references another Role or Policy that does not exist yet it will error on submission! However this ordering is not very clear to a user who is unaware or confused about what they are doing. The purpose of the tutorial is to make it clear and then to come back to this paragraph on how to actually complete the tutorial. In order to avoid receiving errors from AWS when creating the following Roles and Policies, create them in this order:

  1. Create RedshiftClusterRole in the AWSRedshiftAccount ("Create / Get Role Redshift Is/Will Use" Section)
  2. Create S3AWSS3AccountFromAWSRedshiftAccountRole and the S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy in the AWSS3Account ( "Create Role and BucketPolicy In AWSS3Account" Section)
  3. Create S3AWSS3AccountFromAWSRedshiftAccount-AssumePolicy and S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy inline policies in the AWSRedshiftAccount ("Add Policies To RedshiftClusterRole" Section)
  4. Edit the Trust Relationship in the AWSS3Account ("Edit Trust Relationship to Include Trust Relationship Policy" Section)
  5. Add the S3AWSS3AccountFromAWSRedshiftAccountBucketPolicy to the S3 Buckets in the AWSS3Account ("Add BucketPolicy" Section to S3)

If you are a first-time reader. I would strongly recommend you read this top to bottom first if you would like to be able to properly grasp what is all going on and the relationship that is being created with these IAM roles and accounts. After which, you can come back and read and copy/paste each section in the order mentioned aboe. If you are confident in your IAM and wanting to jump through this, then make sure to read the Parameters and Conditions section for the naming convention and then follow the above listing

Create / Get Role Redshift Is/Will Use:

Within the AWSRedshiftAccount, your Redshift cluster will have a Role it assumes when making certain actions. If that doesn't exist, go create one. Were going to assume that is named RedshiftClusterRole.

Add Policies To RedshiftClusterRole

Still within your AWSRedshiftAccount, select the RedshiftClusterRole within IAM and select "Add inline policy". Paste the following policy information:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3AWSS3AccountFromAWSRedshiftAccountAssumePolicy",
            "Action": [
                "sts:AssumeRole"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:iam::550000000066:role/S3AWSS3AccountFromAWSRedshiftAccountRole"
        }
    ]
}

Name the above policy: S3AWSS3AccountFromAWSRedshiftAccount-AssumePolicy. What this policy specifies is allow the RedshiftClusterRole in the AWSRedshiftAccount to assume the S3AWSS3AccountFromAWSRedshiftAccountRole role in the AWSS3Account. Note that we use the full ARN value in the "Resource" parameter to specify the account (using the account number) and the role.

Repeat this process, and create an additional inline policy to the RedshiftClusterRole and paste the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3AWSS3AccountFromAWSRedshiftAccountBucketPolicy",
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::RedshiftStorageBucket",
                "arn:aws:s3:::RedshiftStorageBucket/*",
            ]
        }
    ]
}

Name the above policy: S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy. This policy tells the RedshiftClusterRole that it is allowed to do any action (s3:*) on the RedshiftStorageBucket and its subfolders (RedshiftStorageBucket/*) in S3. Since all S3 bucket names are universally unique, there is no need to specify an account number, and thus it is not included in the full ARN

Create Role and BucketPolicy In AWSS3Account

Switch now to your AWSS3Account and go to IAM. Create a new role with the following settings:

  1. In "Select Type Of Trust Entity" choose "AWS Service"
  2. From the Service section choose "Redshift"
  3. Then, from the use case section below, choose "Redshift-Customizable"

On the policy page then select "Create Policy". For this policy paste the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3AWSS3AccountFromAWSRedshiftAccountBucketPolicy",
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::RedshiftStorageBucket",
                "arn:aws:s3:::RedshiftStorageBucket/*",
            ]
        }
    ]
}

Name the above policy: S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy. Note that this policy has the identical name as the policy we created in the AWSRedshiftAccount as an inline policy for the RedshiftClusterRole. This is needed by AWS and means each account now explicitly knows its permissions on the S3 buckets. Finish creation of the policy.

When creating this policy, AWS will have opened a new tab. Switch back to the other tab which contains the Role we were creating. In the policy listing on that page, click the refresh button and then search for S3AWSS3AccountFromAWSRedshiftAccount-BucketPolicy. Add that policy to the role. Select next and then name this role S3AWSS3AccountFromAWSRedshiftAccountRole

Edit Trust Relationship to Include Trust Relationship Policy

Select the S3AWSS3AccountFromAWSRedshiftAccountRole that you just created and select the "Trust Relationship" tab, then select "Edit Trust Relationship". Delete all JSON contents and paste the following:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com",
        "AWS": "arn:aws:iam::110000000022:role/RedshiftClusterRole"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

The above now specifies that the RedshiftClusterRole in the AWSRedshiftAccount is a trusted role that can assume the S3AWSS3AccountFromAWSRedshiftAccountRole in the S3BucketAccount

Add BucketPolicy to S3 Buckets

Now go to S3 within the S3BucketAccount and select the bucket which will be storing the Redshift data. In our example the bucket is named RedshiftStorageBucket. Select the bucket, then Permissions and then Bucket Policy. Within the bucket policy paste the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3AWSS3AccountFromAWSRedshiftAccountBucketPolicy",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::110000000022:role/RedshiftClusterRole"
            },
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::RedshiftStorageBucket",
                "arn:aws:s3:::RedshiftStorageBucket/*",
            ]
        }
    ]
}

The above policy specifies that the RedshiftClusterRole in the AWSRedshiftAccount is allowed to performa any action (s3:*) on the RedshiftStorageBucket and its subfolders (RedshiftStorageBucket/*) in S3.

Upon completion of that, you will have successfully configured permissions between Redshift and S3 across your two accounts!