Query: ecs_security_group_remote_administration

with bad_groups as (
  select
    distinct arn
  from
    alicloud_ecs_security_group,
    jsonb_array_elements(permissions) as p
  where
    p ->> 'Policy' = 'Accept'
    and p ->> 'Direction' = 'ingress'
    and p ->> 'SourceCidrIp' = '0.0.0.0/0'
    and (
      p ->> 'PortRange' in ('-1/-1', '22/22', '3389/3389')
      or (
        3389 between split_part(p ->> 'PortRange', '/', 1) :: int and split_part(p ->> 'PortRange', '/', 2) :: int
        or 22 between split_part(p ->> 'PortRange', '/', 1) :: int and split_part(p ->> 'PortRange', '/', 2) :: int
      )
    )
)
select
  a.arn as resource,
  case
    when b.arn is null then 'ok'
    else 'alarm'
  end as status,
  case
    when b.arn is null then a.security_group_id || ' does not allow ingress from 0.0.0.0/0 to port 22 or 3389.'
    else a.security_group_id || ' allow ingress from 0.0.0.0/0 to port 22 or 3389.'
  end as reason
  
  , a.account_id as account_id, a.region as region
from
  alicloud_ecs_security_group as a
  left join bad_groups as b on a.arn = b.arn;