AWS Athena read external S3 bucket

Question

Is it possible to analyze data from external S3 bucket in Athena?

I have used official documentation with bucket policy, which allows access to account in which is AWS Athena, but this does not work. I constantly get access denied errors.

I have also tried the same using role as a Principal, but this also doesn't work. This should work, as I found that Athena uses same principal for S3 access as that executing queries.

I have left one other option, which is to copy S3 contents, but this is not what I want to accomplish. All official documentation says is that cross-account access to S3 bucket via bucket policy is possible, but I do not see that such thing is working. If anybody have experience with this, or can test it, I would appreciate.

PS: I already read similar answers here, and did not find any of these working.

UPDATE: These two policies were used. The account numbers are not same.

This is official AWS policy.

{
   "Version": "2012-10-17",
   "Id": "MyPolicyID",
   "Statement": [
      {
         "Sid": "MyStatementSid",
         "Effect": "Allow",
         "Principal": {
            "AWS": "arn:aws:iam::123456789123:root"
         },
         "Action": [
            "s3:GetBucketLocation",
            "s3:GetObject",
            "s3:ListBucket",
            "s3:ListBucketMultipartUploads",
            "s3:ListMultipartUploadParts",
            "s3:AbortMultipartUpload",
            "s3:PutObject"
         ],
         "Resource": [
            "arn:aws:s3:::my-athena-data-bucket",
            "arn:aws:s3:::my-athena-data-bucket/*"
         ]
      }
   ]
}

Another which I tried is the same, just with wildcards:

{
   "Version": "2012-10-17",
   "Id": "MyPolicyID",
   "Statement": [
      {
         "Sid": "MyStatementSid",
         "Effect": "Allow",
         "Principal": {
            "AWS": "arn:aws:iam::123456789123:root"
         },
         "Action": [
            "s3:Get*",
            "s3:List*"
         ],
         "Resource": [
            "arn:aws:s3:::my-athena-data-bucket",
            "arn:aws:s3:::my-athena-data-bucket/*"
         ]
      }
   ]
}

Can you provide the S3 bucket policy you tried? You can skip the account numbers. — amsh
By external, I presume you mean cross-account access in Athena to S3 buckets. — jarmod
Yes, you are right. I mean on the cross-account access to S3. I have two accounts, one hosting IAM user and Athena and S3 bucket with Athena query results, and another account which hosts the S3 bucket with data which I want to analyze. This is pretty common requirement — bkalcho

Theo Theo · Accepted Answer · 2020-10-16T14:29:02

For cross-account access to S3 to work the following must be true (account A owns the bucket, account B is the one accessing the bucket):

Account A must set a bucket policy that grants the requisite actions to account B
Account B must have an IAM user or role with the requisite permissions to the bucket and actions
The objects in the bucket must be owned by account A
The objects are not encrypted with KMS

In addition to this to make it work with Athena the IAM user or role running the query must have permission to write the results in the output location, which is often a separate bucket from the bucket with the data.

If the objects are encrypted with KMS, that requires another set of policies in account A granting access to account B, and the IAM user or role in account B to have permission to decrypt the key.

It's important that it is not sufficient with just a bucket policy, the IAM user or role must also have permission to perform the S3 actions. The bucket policy just grants another account permission to do something, but that does not mean that all IAM users and roles in that account have those permissions automatically.

The third point is more subtle, S3 object ownership is not something you often come across, but it can really mess with permissions. An account can only use a bucket policy to grant access to objects it owns. Say there's a third account, C, which writes an object in the bucket owned by account B. In this situation account C is the owner of the object, even if account B is the owner of the bucket, and can therefore not grant account A access to these objects.

Cross-account S3 access is difficult, and using Athena doesn't make it easier. The way you need to go about debugging this is to try to perform list and get operations using the AWS CLI and the credentials you would use to run a query, if you can get that to work you can probably run a query too.

AWS Athena read external S3 bucket

1 Answers