Trigger: Detect & correct VPC network ACLs allowing ingress to remote server administration ports

with bad_rules as (
  select
    network_acl_id,
    att ->> 'RuleNumber' as bad_rule_number,
    region,
    account_id,
    sp_connection_name as conn
  from
    aws_vpc_network_acl,
    jsonb_array_elements(entries) as att
  where
    att ->> 'Egress' = 'false' -- as per aws egress = false indicates the ingress
    and (
      att ->> 'CidrBlock' = '0.0.0.0/0'
      or att ->> 'Ipv6CidrBlock' = '::/0'
    )
    and att ->> 'RuleAction' = 'allow'
    and (
      (
        att ->> 'Protocol' = '-1' -- all traffic
        and att ->> 'PortRange' is null
      )
      or (
        (att -> 'PortRange' ->> 'From') :: int <= 22
        and (att -> 'PortRange' ->> 'To') :: int >= 22
        and att ->> 'Protocol' in('6', '17') -- TCP or UDP
      )
      or (
        (att -> 'PortRange' ->> 'From') :: int <= 3389
        and (att -> 'PortRange' ->> 'To') :: int >= 3389
        and att ->> 'Protocol' in('6', '17') -- TCP or UDP
      )
    )
),
aws_vpc_network_acls as (
  select
    network_acl_id,
    partition,
    region,
    account_id,
    sp_connection_name as conn
  from
    aws_vpc_network_acl
  order by
    network_acl_id,
    region,
    account_id,
    conn
)
select
  concat(
    acl.network_acl_id,
    '/',
    bad_rules.bad_rule_number,
    ' [',
    acl.account_id,
    '/',
    acl.region,
    ']'
  ) as title,
  acl.network_acl_id as network_acl_id,
  (bad_rules.bad_rule_number) :: int as rule_number,
  acl.region as region,
  acl.conn as conn
from
  aws_vpc_network_acls as acl
  left join bad_rules on bad_rules.network_acl_id = acl.network_acl_id
where
  bad_rules.network_acl_id is not null;