List Inactive IAM Users

Queries & Insights
, ,
1

Inactive IAM users represent dormant accounts that could be compromised by attackers to gain unauthorized access to AWS resources without being noticed. Regular identification and removal of unused accounts reduces the attack surface and helps maintain the principle of least privilege by ensuring only active users retain access permissions.

To list the inactive IAM users, sync the aws_iam_credential_reports and aws_iam_user_access_keys with CloudQuery AWS plugin to a PostgreSQL database and then run this query:

SELECT DISTINCT 
    r.account_id,
    r.user,
    LEAST(COALESCE(r.password_last_used, NOW()),
          COALESCE(k.last_used, NOW()),
          COALESCE(r.access_key_1_last_used_date, NOW()),
          COALESCE(r.access_key_2_last_used_date, NOW())) AS last_authenticated
FROM 
    aws_iam_credential_reports r 
LEFT JOIN aws_iam_user_access_keys k ON k.user_arn = r.arn 
WHERE (
    (
        r.password_status IN ('TRUE', 'true') AND 
        r.password_last_used < NOW() - INTERVAL '30 days'
    ) OR (
        r.password_status IN ('TRUE', 'true') AND 
        r.password_last_used IS NULL AND 
        r.password_last_changed < NOW() - INTERVAL '30 days'
    ) OR (
        k.last_used < NOW() - INTERVAL '30 days'
    ) OR (
        r.access_key1_active AND
        r.access_key_1_last_used_date < NOW() - INTERVAL '30 days'
    ) OR (
        r.access_key1_active AND 
        r.access_key_1_last_used_date IS NULL AND 
        r.access_key_1_last_rotated < NOW() - INTERVAL '30 days'
    ) OR (
        r.access_key2_active AND 
        r.access_key_2_last_used_date < NOW() - INTERVAL '30 days'
    ) OR (
        r.access_key2_active AND 
        r.access_key_2_last_used_date IS NULL AND
        r.access_key_2_last_rotated < NOW() - INTERVAL '30 days')
    ) 
ORDER by last_authenticated DESC NULLS LAST;