AWS IAM User Access Analysis

Kevin Rheinheimer • June 19, 2024

Per AWS CIS Foundational Security v3.0, AWS IAM user access keys should be rotated every 90 days. Use CloudQuery to export your data from AWS to Postgres and make sure to include the aws_iam_credential_reports table in your AWS source configuration. Now you can access this data and target those users that are out of compliance with this best practice. Once exported, here’s how we can view this data in PostgreSQL:

select
	arn,
	SPLIT_PART(arn, ':', 5) as account_id,
	"user",
	user_creation_time,
	access_key1_active,
	access_key_1_last_rotated,
	access_key_1_last_used_date,
	access_key1_last_used_region,
	access_key1_last_used_service,
	access_key2_active,
	access_key_2_last_rotated,
	access_key_2_last_used_date,
	access_key2_last_used_region,
	access_key2_last_used_service
from aws_iam_credential_reports
where "user" <> '<root_account>'
and user_creation_time < date_subtract(current_timestamp, '90 day')
and (access_key1_active is true and access_key_1_last_rotated < date_subtract(current_timestamp, '90 day'))
or (access_key2_active is true and access_key_2_last_rotated < date_subtract(current_timestamp, '90 day'));

This query provides a report of all non-root AWS IAM user accounts who have a user created date longer than 90 days and at least one access key that has not been rotated within the last 90 days. If you have a requirement that is different than the 90 day interval, you can adjust the date_subtract function in the above query to change the time frame to suit your needs.

Ready to dive deeper? Join the CloudQuery Discord community to connect with other users and experts. You can also try out CloudQuery locally with our quick start guide or explore CloudQuery Cloud (currently in beta) for a more scalable solution.