Python – boto3 and AWS Athena permissions

I’m trying to interact with AWS Athena using boto3, v. 1.7.4 via the following script:

import boto3
import botocore

# Test access to the input bucket
bucket = boto3.resource('s3'). Bucket('s3_input')
print(list(bucket.objects.all())

client = boto3.client('athena', region_name='us-east-1')

# Create a new database
db_query = 'CREATE DATABASE IF NOT EXISTS france; '
response = client.start_query_execution(
    QueryString=db_query,
    ResultConfiguration={'OutputLocation': 's3_output'})

# Create a new table
table_query = '''
CREATE EXTERNAL TABLE IF NOT EXISTS france.by_script (`content` string ) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',')
LOCATION 's3_input'; '''

response = client.start_query_execution(
    QueryString=table_query,
    ResultConfiguration={'s3_output'},
    QueryExecutionContext={'Database': 'france'})

With the current permissions of my account, the test of reading s3_input content works well. I can also create a database with db_query, but the table creation fails with the following error message:

Your query has the following errors:FAILED: Execution Error, return
code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
MetaException(message:Got exception: java.io.IOException
com.amazon.ws.emr.hadoop.fs.shaded.com.amazonaws.services.s3.model.AmazonS
Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code:
AccessDenied; Request ID: [...]), S3 Extended Request ID: [...])

If I run the table_query command from the console, console.aws.amazon.com/athena/home With the same account, there are no problems, the table is created correctly.

The permissions are

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Sid": "VisualEditor0",
           "Effect": "Allow",
           "Action": "s3:GetObject",
           "Resource": "s3_input"
       },
       {
           "Sid": "VisualEditor1",
           "Effect": "Allow",
           "Action": [
               "s3:ListAllMyBuckets",
               "s3:HeadBucket"
           ],
           "Resource": "*"
       }
   ]
}

I’d love to know what I’m missing here.
Thanks in advance.

Here’s how to create a policy for users who need to run athena queries from Boto3.

-- S3 files bucket: sqladmin-cloudtrail
-- S3 output bucket: aws-athena-query-results-XXXXXXXXXX-us-east-1

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": [
                "arn:aws:s3:::aws-athena-query-results-XXXXXXXXXX-us-east-1",
                "arn:aws:s3:::sqladmin-cloudtrail"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::aws-athena-query-results-XXXXXXXXXXXXXXXX-us-east-1/*"
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "s3:GetObjectAcl",
                "s3:GetObject",
                "s3:GetObjectTagging",
                "s3:GetBucketPolicy"
            ],
            "Resource": [
                "arn:aws:s3:::sqladmin-cloudtrail",
                "arn:aws:s3:::sqladmin-cloudtrail/*"
            ]
        },
        {
            "Sid": "VisualEditor3",
            "Effect": "Allow",
            "Action": [
                "athena:StartQueryExecution",
                "athena:CreateNamedQuery",
                "athena:RunQuery"
            ],
            "Resource": "*"
        }
    ]
}

Here’s what I did for automation: https://www.sqlgossip.com/automate-aws-athena-create-partition-on-daily-basis/