Query: ecs_security_unrestricted_ingress

with ingress_sg as (
  select
    distinct arn
  from
    alicloud_ecs_security_group,
    jsonb_array_elements(permissions) as p
  where
    p ->> 'Policy' = 'Accept'
    and p ->> 'IpProtocol' <> 'ICMP'
    and p ->> 'Direction' = 'ingress'
    and p ->> 'SourceCidrIp' = '0.0.0.0/0'
    and (p ->> 'PortRange' in ('-1/-1', '1/65535'))
    and security_group_id = $1
)
select
  'Ingress (Excludes ICMP)' as label,
  case
    when count(*) = 0 then 'Restricted'
    else 'Unrestricted'
  end as value,
  case
    when count(*) = 0 then 'ok'
    else 'alert'
  end as type
from
  ingress_sg