Query: iam_roles_allow_all_action

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
), all_action as (
select
  case
    when c.role_name is not null then 'allows all actions'
    else 'limited actions' end as allow_all_action
from
  aws_iam_role as r
  left join roles_allow_all_actions as c on c.role_name = r.name
)
select
  allow_all_action,
  count(*)
from
  all_action
group by
  allow_all_action;