Query: iam_policy_custom_no_permissive_role_assumption

with bad_policies as (
  select
    arn,
    count(*) as num
  from
    aws_iam_policy,
    jsonb_array_elements(policy_std -> 'Statement') as s,
    jsonb_array_elements_text(s -> 'Resource') as resource,
    jsonb_array_elements_text(s -> 'Action') as action
  where
    not is_aws_managed
    and s ->> 'Effect' = 'Allow'
    and resource = '*'
    and (
      ( action = '*'
        or action = 'sts:*'
        or action = 'sts:AssumeRole'
      )
    )
  group by
    arn
)
select
  p.arn as resource,
  case
    when b.arn is not null then 'alarm'
    else 'ok'
  end as status,
  p.name || ' contains ' || coalesce(b.num, 0) ||
      ' statements that allow overly permissive STS role assumption.' as reason
  
  , p.region, p.account_id
from
  aws_iam_policy as p
  left join bad_policies as b on p.arn = b.arn
where
    not is_aws_managed;