Query: compute_firewall_rule_ingress_access_restricted_to_netbios_snn_port_139

with ip_protocol_all as (
  select
    name
  from
    gcp_compute_firewall
  where
    direction = 'INGRESS'
    and action = 'Allow'
    and (
      source_ranges ? & array [ '0.0.0.0/0' ]
      or source_ranges ? & array [ '::0' ]
      or source_ranges ? & array [ '0.0.0.0' ]
      or source_ranges ? & array [ '::/0' ]
      or source_ranges ? & array [ '::' ]
    )
    and (
      allowed @> '[{"IPProtocol":"all"}]'
      or (
        allowed @> '[{"IPProtocol":"tcp"}]'
        and allowed -> 0 -> 'ports' is null
      )
      or (
        allowed @> '[{"IPProtocol":"udp"}]'
        and allowed -> 0 -> 'ports' is null
      )
    )
),
ip_protocol_tcp_udp as (
  select
    name
  from
    gcp_compute_firewall,
    jsonb_array_elements(allowed) as p,
    jsonb_array_elements_text(p -> 'ports') as port
  where
    direction = 'INGRESS'
    and action = 'Allow'
    and (
      source_ranges ? & array [ '0.0.0.0/0' ]
      or source_ranges ? & array [ '::0' ]
      or source_ranges ? & array [ '0.0.0.0' ]
      or source_ranges ? & array [ '::/0' ]
      or source_ranges ? & array [ '::' ]
    )
    and (
      p ->> 'IPProtocol' = 'tcp'
      or p ->> 'IPProtocol' = 'udp'
    )
    and (
      port = '139'
      or (
        port like '%-%'
        and split_part(port, '-', 1) :: integer <= 139
        and split_part(port, '-', 2) :: integer >= 139
      )
    )
)
select
  self_link resource,
  case
    when name in (
      select
        name
      from
        ip_protocol_tcp_udp
    ) then 'alarm'
    when name in (
      select
        name
      from
        ip_protocol_all
    ) then 'alarm'
    else 'ok'
  end as status,
  case
    when name in (
      select
        name
      from
        ip_protocol_tcp_udp
    )
    or name in (
      select
        name
      from
        ip_protocol_all
    ) then title || ' allows access from internet to NetBIOS SSN port 139.'
    else title || ' restricts access from internet to NetBIOS SSN port 139.'
  end as reason,
  location as location,
  project as project
from
  gcp_compute_firewall;