AWS Athena query only returns a single column in results when using iam role

0

Hello. I've encountered an issue that might be related to not enough iam permissions, but I can't find any documentation about what policies I could be missing.

My IAM Role has the following managed policies:

arn:aws:iam::aws:policy/service-role/AWSQuicksightAthenaAccess
arn:aws:iam::aws:policy/AmazonS3FullAccess
arn:aws:iam::aws:policy/AmazonAthenaFullAccess

I have the iam role configured and working in my .aws/config file and when I execute an athena query like this:

aws athena start-query-execution \                                                    
    --query-string "SELECT * FROM my_schema.my_table WHERE dt='2022-08-17-10'" \
--query-execution-context Database=default,Catalog=awsdatacatalog \
--result-configuration OutputLocation=s3://aws-athena-query-results-my-athena-results123344-us-east-1/ \
--profile my-athena-role

I get a result like the following:

{
    "ResultSet": {
        "Rows": [
            {
                "Data": [
                    {
                        "VarCharValue": "dt"
                    }
                ]
            },
            {
                "Data": [
                    {
                        "VarCharValue": "2022-08-17-10"
                    }
                ]
            },
            {
                "Data": [
                    {
                        "VarCharValue": "2022-08-17-10"
                    }
                ]
            },
            {
                "Data": [
                    {
                        "VarCharValue": "2022-08-17-10"
                    }
                ]
            },
            {
                "Data": [
                    {
                        "VarCharValue": "2022-08-17-10"
                    }
                ]
            },
...

As you can see, only the column dt is returned in the resultset. That table contains many more columns.

Running the same query without the IAM Role, with my default account that has full access to all resources produces the desired results.

So it seems it's related to the limited permissions, but I can't figure out which ones I'm missing in my IAM Role.

Thanks in advanced.

  • I went deep into cloudtrail to see what were the differences when querying using the IAM Role and my user directly and found several events for the IAM Role:

    Event name: GetSchemaVersion Event source: glue.amazonaws.com Error code: AccessDenied

    So in order to fix the issue, I added a policy to my IAM Role to allow access to glue:GetSchemaVersion and that's what it finally make Athena work!

    Funny how the AmazonAthenaFullAccess policy doesn't provide full access really.

erickm
已提問 1 年前檢視次數 358 次
1 個回答
0

Hi, Greetings from AWS. Based on the problem behavior and the resolution you found, it seems that you were querying a table which uses AWS Glue Schema Registry feature. To successfully query such a table, as you correctly pointed out, additional "glue:GetSchemaVersion" permission is required. Reference: https://docs.aws.amazon.com/glue/latest/dg/schema-registry-gs.html#schema-registry-gs1b

I hope this information help!

AWS
Ethan_H
已回答 1 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南