AccessDenied on AWS Athena

user_55070 0
Viewed 12142

When I run a simple select * query on AWS Athena I get an access denied error.

The query is:

select * from sensor.sensordata

The Schema is:

CREATE EXTERNAL TABLE sensor.sensordata (
  sig string,
  `data` struct<`iat`:timestamp,
  `sub`:string,
  tMax: float,
  tMin: float,
  `tAvg`: float,
  `hAvg`: float,
  hMin: float,
  hMax: float
  >
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://mybucket/data/';

The error I get (IDs shortened) is that a file can not be read:

com.amazonaws.services.s3.model.AmazonS3Exception: 
Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; 
Request ID: B0048904...; S3 Extended Request ID: CKchfW8...), S3 Extended Request ID: 
CKchfW8... (Path: s3://mybucket/data/sensor=01235EFD886C7DF1EE/t=1561513414.json)

However I even made the bucket policy public for everyone:

{
  "Version": "2008-10-17",
  "Statement": [
    {
        "Effect": "Allow",
        "Principal": "*",
        "Action": [
            "s3:PutObject",
            "s3:GetObject"
        ],
        "Resource": [
            "arn:aws:s3:::mybucket/*",
            "arn:aws:s3:::mybucket"
        ]
    }
  ]
}

Beside the bucket policy I also have in the ACL the standard full access to the bucket owner, which is the same Account I run my Athena Query from. I run my query in the AWS Management Console.

Not sure if related: AWS Glue Crawler is not able to read the files. But can list them, I get an error for every file.

What can I do to make the query work?

amazon-athena amazon-web-services amazon-s3
3 Answers

You didn't show us your table definition, but I suspect that Athena is wanting to list the contents of the path to discover what files exist, so it can read through them.

The policy only grants permission to Put and Get objects, not to List the bucket. Try adding ListBucket permission.

By the way, it's a "really bad idea" to use a Bucket Policy like this since you are making your content public. Instead, the permission should be assigned to the credentials (eg IAM User) that is calling Athena. That way, the bucket is not public.

user_1747770

Your policy is missing ListBucket access which is required for Athena to list the contents of the bucket before get/put object.

Below policy should work.

{
  "Version": "2008-10-17",
  "Statement": [
    {
        "Effect": "Allow",
        "Principal": "*",
        "Action": [
            "S3:ListBucket",
            "s3:PutObject",
            "s3:GetObject"
        ],
        "Resource": [
            "arn:aws:s3:::mybucket/*",
            "arn:aws:s3:::mybucket"
        ]
    }
  ]
}
user_50689830

The AWS Athena docs point to this example managed policy AWSQuicksightAthenaAccessto show all the permissions required for SQL clients and BI tools. It is for AWS QuickSight but the permissions are applicable to other tools as well.

Specifically for S3 permissions it has this:

{
  Version": "2012-10-17",
  "Statement": [
    {
        "Effect": "Allow",
        "Action": [
            "s3:GetBucketLocation",
            "s3:GetObject",
            "s3:ListBucket",
            "s3:ListBucketMultipartUploads",
            "s3:ListMultipartUploadParts",
            "s3:AbortMultipartUpload",
            "s3:CreateBucket",
            "s3:PutObject"
        ],
        "Resource": [
            "arn:aws:s3:::aws-athena-query-results-*"
        ]
    }
  ]
}

The resource in that policy is an S3 ARN pattern that will match all buckets used as the default location for your Athena query results. If you have changed the default location bucket then you should apply similar permissions for that bucket.

Notes

  • You don't need a Principal in a policy that is attached to a group/user.
  • The Actions PutObject and GetObject apply to objects in s3, not buckets, so the resource "arn:aws:s3:::mybucket" does not need these permissions
  • The Quicksight managed policy uses a pattern that will include both the bucket and all the objects in it, so it mixes both bucket and object Actions together which works but is not very specific.
  • If you use Permission Boundaries for your IAM Groups / Users then make sure that is not restricting access to the bucket. I made the mistake of using the Quicksight policy as the permission boundary at the same time as using a custom bucket location, and the permission boundary prevented my additional S3 policy from working.
user_13357930
Related