Query: iam_roles_allow_all_action_count

with roles_allow_all_actions as (
  select
    r.name as role_name,
    r.account_id as account_id,
    p.name as policy_name
  from
    aws_iam_role as r,
    jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
    aws_iam_policy as p,
    jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
    jsonb_array_elements_text(stmt -> 'Action') as action
  where
    policy_arn = p.arn
    and stmt ->> 'Effect' = 'Allow'
    and action = '*'
  order by
    r.name
)
select
  count(role_name)::numeric as value,
  'Allows All Actions' as label,
  case when count(*) > 0 then 'alert' else 'ok' end as type
from
  roles_allow_all_actions;