Unable to view results in S3 bucket after executing Athena query in different account?

Question

I have two accounts: Account A and Account B.

I'm executing an Athena query in Account A and want to have the query results populated in an S3 bucket in Account B.

I've tested the script that does this countless times within a singular account so know that there is no issues with my code. The query history in Athena also indicates that my code has ran successfully, so it must be a permissions issue.

I'm able to see an object containing a CSV file with the query results in Account B (as expected) but for some reason cannot open or download it to view the contents. When I attempt to do so, I only see XML code that says:

<Code>AccessDenied</Code>
<Message>Access Denied</Message>

Within the file properties, I see Unknown Error under Server-side encryption settings and You don't have permission to get object ACL with a message about not having allowed the s3:GetObjectAcl action.

I've tried to give both Account A and Account B full S3 permissions as follows via the bucket policy in Account B:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "This is for Account A",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::iam-number-account-a:root"
      },
      "Action": "s3:*",
      "Resource": [
        "arn:aws:s3:::my-bucket-name",
        "arn:aws:s3:::my-bucket-name/*"
      ]
    },
    {
      "Sid": "This is for Account B",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::iam-number-account-b:root"
      },
      "Action": "s3:*",
      "Resource": [
        "arn:aws:s3:::my-bucket-name",
        "arn:aws:s3:::my-bucket-name/*"
      ]
    }
  ]
}

Some other bucket (Account B) configuration settings that may be contributing to my issue:

• Default encryption: Disabled

• Block public access: Off for everything

• Object ownership: Bucket owner preferred

• Access control list:

Bucket Owner - Account B: Objects (List, Write), Bucket ACL (Read, Write)

External Account - Account A: Objects (Write), Bucket ACL (Write)

If anyone can help identify my issue and what I need to fix, that'd be greatly appreciated. I've been struggling to find a solution for this for a few hours.

Answer 1

A common problem when creating objects in an Amazon S3 bucket belonging to a different AWS Account is that the object 'owner' remains the original Account. When copying objects in Amazon S3, this can be resolved by specifying ACL=bucket-owner-full-control.

However, this probably isn't possible when creating the file with Amazon Athena.

See other similar StackOverflow questions:

• How to ensure that Athena result S3 object with bucket-owner-full-control - Stack Overflow
• AWS Athena: cross account write of CTAS query result - Stack Overflow

A few workarounds might be:

• Write to an S3 bucket in Account A and use a Bucket Policy to grant Read access to Account B, or
• Write to an S3 bucket in Account A and have S3 trigger an AWS Lambda function that copies the object to the bucket in Account B, while specifying ACL=bucket-owner-full-control, or
• Grant access to the source data to an IAM User or Role in Account B, and run the Athena query from Account B, so that it is Account B writing to the 'output' bucket

Answer 2

CTAS queries have the bucket-owner-full-control ACL by default for cross-account writes via Athena