Query: iam_inline_policy_no_administrative_privileges

with full_administrative_privilege_policies as (
  select
    arn,
    inline_policies_std,
    name,
    account_id,
    region,
    _ctx,
    'iam_user' as type
  from
    aws_iam_user
  union
  select
    arn,
    inline_policies_std,
    name,
    account_id,
    region,
    _ctx,
    'iam_role' as type
  from
    aws_iam_role
  union
  select
    arn,
    inline_policies_std,
    name,
    account_id,
    region,
    _ctx,
    'iam_group' as type
  from
    aws_iam_group
),
bad_policies as (
  select
    arn,
    count(*) as statements_num
  from
    full_administrative_privilege_policies,
    jsonb_array_elements(inline_policies_std) as policy_std,
    jsonb_array_elements(policy_std -> 'PolicyDocument' -> '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 = '*:*'
      )
    )
  group by
    arn
)
select
  p.arn as resource,
  case
    when bad.arn is null then 'ok'
    else 'alarm'
  end status,
  p.name || ' contains ' || coalesce(bad.statements_num, 0) || ' statements that allow action "*" on resource "*".' as reason,
  p.account_id
from
  full_administrative_privilege_policies as p
  left join bad_policies as bad on p.arn = bad.arn;