Query: iam_role_no_administrator_access_policy_attached

with admin_roles as (
  select
    arn,
    name,
    attachments
  from
    aws_iam_role,
    jsonb_array_elements_text(attached_policy_arns) as attachments
  where
    split_part(attachments, '/', 2) = 'AdministratorAccess'
)
select
  r.arn as resource,
  case
    when ar.arn is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when ar.arn is not null then r.name || ' have AdministratorAccess policy attached.'
    else r.name || ' does not have AdministratorAccess policy attached.'
  end as reason
  
  , r.region, r.account_id
from
  aws_iam_role as r
  left join admin_roles ar on r.arn = ar.arn
order by
  r.name;