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개 답변
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
답변함 일 년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠