CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-iamamazon-athena
Istvan
Istvan

Reputation: 8552

Athena permission denied while executing a query

I have an AWS Lambda function that runs an Athena query to read some data.

Here is the policy:

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "Sid0",
                "Effect": "Allow",
                "Action": [
                    "athena:GetQueryExecution",
                    "athena:GetQueryResults",
                    "athena:GetWorkGroup",
                    "athena:StartQueryExecution",
                    "athena:StopQueryExecution"
                ],
                "Resource": "*"
            },
            {
                "Sid": "Sid2",
                "Effect": "Allow",
                "Action": [
                    "glue:GetTable"
                ],
                "Resource": "*"
            },
            {
                "Sid": "Sid4",
                "Effect": "Allow",
                "Action": [
                    "s3:AbortMultipartUpload",
                    "s3:GetObject",
                    "s3:ListMultipartUploadParts",
                    "s3:PutObject"
                ],
                "Resource": [
                    "arn:aws:s3:::BUCKET/db-access/*"
                ]
            },
            {
                "Sid": "Sid5",
                "Effect": "Allow",
                "Action": [
                    "s3:ListBucketMultipartUploads",
                    "s3:ListBucket",
                    "s3:GetBucketLocation"
                ],
                "Resource": [
                    "arn:aws:s3:::BUCKET"
                ]
            }
        ]
    }

When I run the query with admin user it works. When I run it with the Lambda role/policy it fails.

    "StatementType": "DML",
    "ResultConfiguration": {
        "OutputLocation": "s3://BUCKET/db-access/athena-query-results/e26d9574-503c-45d8-9730-3d39e4293b3f.csv"
    },
    "QueryExecutionContext": {},
    "Status": {
        "State": "FAILED",
        "StateChangeReason": "Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; Request ID: 09DF293291383C76; S3 Extended Request ID: qx5MpYNSpoqWQHdQVT7rqExhkxaNI3oFlD4tXmPwwUtHNAw3wrd9cjrdeYb3mGA8X1Lnql+RSyA=; Proxy: null)",
        "SubmissionDateTime": "2020-10-20T12:17:34.855000+02:00",
        "CompletionDateTime": "2020-10-20T12:17:50.086000+02:00"
    },

One really annoying aspect of this that we have cloudtrail enabled for the account but there is not such a requestid (09DF293291383C76 for example) when we query Cloudtrail. It seems that Athena is unable to write the result to the location even though with the same policy I am able to PutObject to that location. Not sure what else Athena is running before the PutObject but that is the action that is missing from the action list. Not sure how to debug this further.

Update:

After some investigation and help from AWS we tracked down the issue being a missing read permission from the Athena table's S3 location. Unfortunately we missed that location with CloudTrail too and Athena's error messages does not let you determine what is the actual problem.

Upvotes: 0

Views: 5427

Answers (1)

Theo
Theo

Reputation: 132852

Are you writing your results to the same place where the table's data is stored? Otherwise there's your problem.

It could also be s3:PutObjectAcl that's missing, but IIRC Athena doesn't need it.

There's a lot that is unclear with your question, we can't possibly debug this without knowing much more about your environment. You say you think it's writing the results that is the problem, but what makes you say that?

S3 doesn't log object level operations in CloudTrail by default, but you can enable that, see https://docs.aws.amazon.com/awscloudtrail/latest/userguide/logging-data-events-with-cloudtrail.html

Upvotes: 2

Related Questions