AWS

Tutorials

AWS IAM User Access Analysis

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 PostgreSQL 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" <> ''
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.

Want to see CloudQuery in action? Schedule a demo with our team or check out the platform documentation to learn more.

Try CloudQuery yourself

Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.

Related posts

AWS

Security

Tutorials

Finding AWS Unrestricted Outbound Access in 2026: EC2, Lambda, and Beyond

Find EC2, Lambda, and ECS Fargate resources with unrestricted outbound access in AWS using SQL. Why egress controls still break attack chains in 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Security

Tutorials

AWS IAM Identity Center with Google Workspace as an IdP: 2026 Step-by-Step Guide

Set up AWS IAM Identity Center with Google Workspace: SAML authentication, SCIM user provisioning, and group sync with CLI or ssosync. Updated for 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Cloud Infrastructure

Security

Tutorials

Integrate AbuseIPDB Threat Intelligence with CloudQuery

Enrich your cloud asset inventory with AbuseIPDB threat intelligence to identify public cloud IPs with known abuse reports.

Joe Karlsson

Joe Karlsson

April 20, 2026

AWS

Cloud Infrastructure

Tutorials

How to Query ipinfo.io Data with CloudQuery

Learn how to enrich your cloud asset inventory with ipinfo.io IP geolocation and threat intelligence data using CloudQuery.

Joe Karlsson

Joe Karlsson

April 20, 2026

AWS

Security

Tutorials

AWS IAM Identity Center (Formerly AWS SSO): The Complete 2026 Guide

AWS IAM Identity Center replaced AWS SSO in 2022. Learn how to set it up, configure permission sets, use the AWS CLI, and audit access with CloudQuery.

Joe Karlsson

Joe Karlsson

April 6, 2026

AWS

Tutorials

AWS Pinpoint Is Being Deprecated: How to Assess Your Migration Scope

AWS Pinpoint reaches end of support October 2026. Query your Pinpoint usage across all accounts to find what needs migrating before the deadline.

Joe Karlsson

Joe Karlsson

March 24, 2026

AWS

Best Practices

Tutorials

CloudQuery Sync Performance: A Practical Troubleshooting Guide

Diagnose why CloudQuery syncs slow down after plugin upgrades, identify expensive tables, and use the CLI command that replaces the tables wildcard.

Joe Karlsson

Joe Karlsson

March 6, 2026

AWS

Security

Tutorials

Set Up AWS CLI with SSO (IAM Identity Center) — Step by Step

Set up AWS CLI v2 with IAM Identity Center in 5 minutes. Covers SSO registration scopes, sso-session config, token refresh, and multi-account profiles.

Yevgeny Pats

Yevgeny Pats

February 25, 2026

AWS

Cloud Infrastructure

Tutorials

AWS Lambda Python 3.9 End-of-Support: Find and Upgrade Every Affected Function

AWS Lambda Python 3.9 security patches stopped Dec 15, 2025. Find every affected function across all accounts with SQL queries and upgrade to 3.12 or 3.13.

Joe Karlsson

Joe Karlsson

February 10, 2026

Own your pipeline.
Own your data.