Query: ram_role_with_admin_access

with admin_roles as (
  select
    distinct name
from
  alicloud_ram_role,
  jsonb_array_elements(attached_policy) as policies
where
  policies ->> 'PolicyName' = 'AdministratorAccess'
)
select
  case when a.name is not null then 'Enabled' else 'Disabled' end as value,
  'Admin Access' as label,
  case when a.name is not null then 'alert' else 'ok' end as type
from
  alicloud_ram_role as r
  left join admin_roles as a on r.name = a.name
where
  r.arn = $1
  and r.account_id = split_part($1,':',4);