Dashboard: AWS IAM Role Dashboard

with roles_can_be_assumed_anonymously as (
  select
    name,
    stmt -> 'Principal',
    Principal
  from
    aws_iam_role role,
    jsonb_array_elements(role.assume_role_policy_std -> 'Statement') as stmt,
    jsonb_array_elements_text(stmt -> 'Principal' -> 'AWS') as principal
  where
    principal = '*'
    and stmt ->> 'Effect' = 'Allow'
)
select
  count(distinct name) as value,
  'Allows All Principals to Assume Role' as label,
  case when count(distinct name) > 0 then 'alert' else 'ok' end as type
from
  roles_can_be_assumed_anonymously;