The same query working on Athena and not working on Lambda - S3 permissions

Question

I'm trying to query a partitioned table that is based on S3 bucket from Lambda
and get the following error:

But, when I used the same query via Athena it works well.

• My Lambda role includes S3 full permission for all the resources.

BTW I received access to other S3 bucket (another account), this is not my bucket but I've read, and list permissions. and using Lambda I'm able to create the partition table on their bucket.

Using Lambda, this query is working

ALTER TABLE access_Partition ADD PARTITION
(year = '2022', month = '03',day= '15' ,hour = '01') LOCATION 's3://sddds/2022/03/15/01/';

But select query on the above table (after the creation) get a permission error (When I open the executed query on Athena it's marked as failed but I can run it successfully )

select * from access_Partition

Please advise!!!

Answer 1

Amazon Athena uses the permissions of the entity making the call to access Amazon S3. So, when you run an Athena query in the console, it is using permissions from your IAM User. When it is run from Lambda, it uses the permissions from the IAM Role associated with the Lambda function.

When this command is run:

ALTER TABLE access_Partition ADD PARTITION
(year = '2022', month = '03',day= '15' ,hour = '01') LOCATION 's3://sddds/2022/03/15/01/';

it is updating information (metadata) in the data catalog used in Athena in your own account. It is not actually accessing the bucket until a query is run.

The fact that the query fails when it is run suggests that the IAM Role does not have permission to access the bucket in the other AWS Account.

You should add a Bucket Policy on the S3 bucket in the other account that grants access permission for the IAM Role used by the Lambda function.