AWS Athena Federated query gives permission error while running in AWS Batch

Question

I have set up MySQL datasource in Athena (it required creating Lambda for RDB access) and can run federated queries successfully in Athena console - I can do joins between RDB tables and Athena/Glue tables (when RDB table is referred, it is supposed to be specified as <datasource_name>.<db_name>.<table_name>) and get the results.

Now I am trying to run the same federated query in my AWS Batch application, and getting the following error:

The Amazon Athena query failed to run with error message: Amazon Athena experienced a permission error. Please provide proper permission and submitting the query again. If the issue reoccurs, contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience.

I can successfully run usual (non-federated) Athena queries that only use Athena/Glue tables, in AWS Batch.

My AWS Batch job definition uses ecsTaskExecutionRole as "execution role" and "job role ARN".

I have added the following policies into both ecsTaskExecutionRole and ecsInstanceRole. Is there any policy that I am missing?

policy for all Athena actions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "athena:*"
            ],
            "Resource": [
                "arn:aws:athena:<my_region>:<my_acc_id>:*"
            ]
        }
    ]
}

policy for all Glue actions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "glue:*"
            ],
            "Resource": [
                "arn:aws:glue:<my_region>:<my_acc_id>:*"
            ]
        }
    ]
}

policy for all actions of Lambda that was created for accessing MySQL datasource:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "lambda:*"
            ],
            "Resource": [
                "arn:aws:lambda:<my_region>:<my_acc_id>:function:<my_lambda_name>:*"
            ]
        }
    ]
}

policy for S3 buckets - the one with table data and the one for storing Athena output:

{
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:AbortMultipartUpload",
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::<table_bucket>",
                "arn:aws:s3:::<table_bucket>/*",
                "arn:aws:s3:::<athena_output_bucket>",
                "arn:aws:s3:::<athena_output_bucket>/*"
            ]
        }
    ]
}

UPD. just for convenience, added the following policy according to this doc: https://docs.aws.amazon.com/athena/latest/ug/federated-query-iam-access.html#fed-using-iam

    {
        "Effect": "Allow",
        "Action": "athena:ListWorkGroups",
        "Resource": "*"
    }

and also added this resource "arn:aws:s3:::<athena_output_bucket>/athena-spill*" (spill bucket is the same with athena output bucket) to S3 policy. Still no success

@Marcin no, I'm passing creds in connection string as `user=<...>&password=<...>`. but lambda still has a policy for secrets manager. I'm planning to use secrets manager after I get everything working — mangusta, Jul 17 '22 at 01:37

score 2 · Answer 1 · answered Jul 17 '22 at 02:05

2

Figured out the reason - the Lambda resource should be specified without wildcard at the end:

arn:aws:lambda:<my_region>:<my_acc_id>:function:<my_lambda_name>

answered Jul 17 '22 at 02:05

mangusta

3,470
5
24
47

AWS Athena Federated query gives permission error while running in AWS Batch

1 Answers1