My Amazon S3 bucket has data files created using the UNLOAD command from an Amazon Redshift cluster in another account. Why can't I access those files?

Short description

By default, an S3 object is owned by the AWS account that uploaded it. This is true even when the bucket is owned by another account. Therefore, when Amazon Redshift data files are put into your bucket by another account, you don't have default permission for those files.

To get access to the data files, an AWS Identity and Access Management (IAM) role with cross-account permissions must run the UNLOAD command again. Follow these steps to set up the Amazon Redshift cluster with cross-account permissions to the bucket:

1.    From the account of the S3 bucket, create an IAM role with permissions to the bucket. This is the bucket role.

2.    From the account of the Amazon Redshift cluster, create another IAM role with permissions to assume the bucket role. This is the cluster role.

3.    Update the bucket role to grant bucket access, and then create a trust relationship with the cluster role.

4.    From the Amazon Redshift cluster, run the UNLOAD command using the cluster role and bucket role.

Important: This resolution doesn't apply to Amazon Redshift clusters or S3 buckets that use server-side encryption with AWS Key Management Service (AWS KMS).

Resolution

Create a bucket role

From the account of the S3 bucket, create an IAM role with permissions to the bucket:

1.    From the account of the S3 bucket, open the IAM console.

2.    Create an IAM role. As you create the role, select the following:

For Select type of trusted entity, choose AWS service.

For Choose the service that will use this role, choose Redshift.

For Select your use case, choose Redshift - Customizable.

3.    After you create the IAM role, attach a policy that grants permission to the bucket. You can use a policy that's similar to the following:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1234537676482",
      "Action": [
        "s3:ListBucket",
        "s3:PutObject"
      ],
      "Effect": "Allow",
      "Resource": [
        "arn:aws:s3:::awsexamplebucket/*",
        "arn:aws:s3:::awsexamplebucket"
      ]
    }
  ]
}

4.     Get the bucket role's Amazon Resource Name (ARN). You need the role's ARN for a later step.

Create a cluster role

From the account of the Amazon Redshift cluster, create another IAM role with permissions to assume the bucket role:

1.    From the account of the Amazon Redshift cluster, open the IAM console.

2.    Create an IAM role. As you create the role, select the following:

For Select type of trusted entity, choose AWS service.

For Choose the service that will use this role, choose Redshift.

For Select your use case, choose Redshift - Customizable.

3.    After you create the IAM role, attach the following policy to the role:

Important: Replace arn:aws:iam::123456789012:role/Bucket_Role with the ARN of the Bucket Role that you created.

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

4.     Get the cluster role's ARN. You need the role's ARN for a later step.

Update the bucket role to create a trust relationship with the cluster role

1.    From the account of the S3 bucket, open the IAM console.

2.    In the navigation pane, choose Roles.

3.    From the list of roles, open the Bucket Role that you created.

4.    Choose the Trust relationships tab.

5.    Choose Edit trust relationship.

6.    For the Policy Document, replace the existing policy with the following:

Important: Replace arn:aws:iam::012345678901:role/Cluster_Role with the ARN of the Cluster Role that you created.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::012345678901:role/Cluster_Role"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

7.    Choose Update Trust Policy.

From the Amazon Redshift cluster, run the unload operation using the Cluster Role and Bucket Role

1.    Connect to the Amazon Redshift cluster.

2.    Run the UNLOAD command with both the IAM roles that you created, similar to the following:

Important: Replace arn:aws:iam::012345678901:role/Cluster_Role with the ARN of your Cluster Role. Then, replace arn:aws:iam::123456789012:role/Bucket_Role with the ARN of your Bucket Role.

unload ('select * from TABLE_NAME')
to 's3://awsexamplebucket' 
iam_role 'arn:aws:iam::012345678901:role/Cluster_Role,arn:aws:iam::123456789012:role/Bucket_Role';

After you run the UNLOAD command, the data files are owned by the same account as the bucket that they're stored in.

---

Related information

UNLOAD Examples