Query: iam_policy_all_attached_no_star_star

with star_access_policies as (
  select
    arn,
    is_aws_managed,
    count(*) as num_bad_statements
  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
    s ->> 'Effect' = 'Allow'
    and resource = '*'
    and (
      (action = '*'
      or action = '*:*'
      )
    )
    and is_attached
  group by
    arn,
    is_aws_managed
)
select
  p.arn as resource,
  case
    when s.arn is not null and s.is_aws_managed then 'info'
    when s.arn is null then 'ok'
    else 'alarm'
  end status,
  case
    when s.arn is not null and s.is_aws_managed then p.name || ' is an AWS managed policy with ' || coalesce(s.num_bad_statements, 0) || ' statements that allow action "*" on resource "*".'
    else p.name || ' contains ' || coalesce(s.num_bad_statements, 0) || ' statements that allow action "*" on resource "*".'
  end as reason
  
  , p.account_id
from
  aws_iam_policy as p
  left join star_access_policies as s on p.arn = s.arn
where
  p.is_attached;