Query: ram_policy_no_full_wildcard_privileges

with policy_statements as (
  select
    p.account_id,
    p.policy_name,
    jsonb_array_elements(coalesce(p.policy_document_std -> 'Statement', '[]'::jsonb)) as statement
  from
    alicloud_ram_policy as p
),
wildcard_policies as (
  select
    account_id,
    policy_name
  from
    policy_statements
  where
    lower(coalesce(statement ->> 'Effect', '')) = 'allow'
    and (
      (jsonb_typeof(statement -> 'Action') = 'array' and (statement -> 'Action') ?| array['*', '*:*'])
      or (jsonb_typeof(statement -> 'Action') = 'string' and statement ->> 'Action' in ('*', '*:*'))
    )
    and (
      (jsonb_typeof(statement -> 'Resource') = 'array' and (statement -> 'Resource') ? '*')
      or (jsonb_typeof(statement -> 'Resource') = 'string' and statement ->> 'Resource' = '*')
    )
)
select
  'acs:ram::' || p.account_id || ':policy/' || p.policy_name as resource,
  case
    when w.policy_name is null then 'ok'
    else 'alarm'
  end as status,
  case
    when w.policy_name is null then p.policy_name || ' does not allow full administrative privileges.'
    else p.policy_name || ' allows all actions on all resources.'
  end as reason
  , p.account_id as account_id
from
  alicloud_ram_policy as p
  left join wildcard_policies as w on p.account_id = w.account_id and p.policy_name = w.policy_name;