Trigger: Detect & correct IAM users with more than one active key

with users_active_key_count as (
  select
    u.arn as user_arn,
    u.name as name,
    count(*) as num
  from
    aws_iam_user as u
    left join aws_iam_access_key as k on u.name = k.user_name
    and u.account_id = k.account_id
  where
    k.status = 'Active'
  group by
    u.arn,
    u.name
),
users_with_more_than_one_active_key as (
  select
    user_arn,
    name,
    num
  from
    users_active_key_count
  where
    num > 1
),
ranked_keys as (
  select
    k.access_key_id,
    k.user_name,
    k.create_date,
    k.access_key_last_used_date,
    account_id,
    row_number() over (
      partition by k.user_name
      order by
        k.create_date asc
    ) as rnk,
    extract(
      day
      from
        (now() - k.create_date)
    ) as access_key_age,
    -- Age in days since creation
    case
      when k.access_key_last_used_date is not null then extract(
        day
        from
          (now() - k.access_key_last_used_date)
      ) :: text
      else 'not_used'
    end as access_key_last_used_in_days,
    -- Days since last used, or "not_used"
    case
      when k.access_key_last_used_date is not null then k.access_key_last_used_date :: text
      else 'not_used'
    end as access_key_last_used,
    -- Last used date, or "not_used"
    sp_connection_name as conn
  from
    aws_iam_access_key as k
  where
    k.user_name in (
      select
        name
      from
        users_with_more_than_one_active_key
    )
)
select
  concat(rk1.user_name, ' [', rk1.account_id, ']') as title,
  rk1.user_name,
  rk1.account_id,
  rk1.access_key_id as access_key_id_1,
  rk1.access_key_last_used as access_key_1_last_used_date,
  (rk1.access_key_age) :: text as access_key_1_age,
  rk1.access_key_last_used_in_days as access_key_1_last_used_in_days,
  rk2.access_key_id as access_key_id_2,
  rk2.access_key_last_used as access_key_2_last_used_date,
  (rk2.access_key_age) :: text as access_key_2_age,
  rk2.access_key_last_used_in_days as access_key_2_last_used_in_days,
  rk1.account_id,
  rk1.conn
from
  ranked_keys rk1
  left join ranked_keys rk2 on rk1.user_name = rk2.user_name
  and rk2.rnk = 2
where
  rk1.rnk = 1
order by
  rk1.user_name;