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
posta un anno fa359 visualizzazioni
1 Risposta
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
con risposta un anno fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande