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.

1 Answer
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
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions