Query: compute_firewall_allow_ssh_port_22_ingress

with rules as (
  select
    distinct address
  from
    terraform_resource,
    jsonb_array_elements(
      case jsonb_typeof(attributes_std -> 'allow')
        when 'array' then (attributes_std -> 'allow')
        when 'object' then jsonb_build_array(attributes_std -> 'allow')
        else null end
      ) allow,
    jsonb_array_elements_text(
      case
        when ((allow -> 'ports') != 'null') and jsonb_array_length(allow -> 'ports') > 0 then (allow -> 'ports')
        else jsonb_build_array(allow -> 'ports')
      end) as port,
    jsonb_array_elements_text(
      case
        when ((attributes_std -> 'source_ranges') != 'null') and jsonb_array_length(attributes_std -> 'source_ranges') > 0 then (attributes_std -> 'source_ranges')
        else jsonb_build_array(attributes_std -> 'source_ranges')
      end) as sip
  where
    type = 'google_compute_firewall'
    and (attributes_std ->> 'direction' is null or lower(attributes_std ->> 'direction') = 'ingress')
    and lower(sip) in ('*', '0.0.0.0', '0.0.0.0/0', 'internet', 'any', '<nw>/0', '/0')
    and (
      port in ('22', '*')
      or (
        port like '%-%'
        and split_part(port, '-', 1) :: integer <= 22
        and split_part(port, '-', 2) :: integer >= 22
      )
    )
)
select
  r.address as resource,
  case
    when g.address is null then 'ok'
    else 'alarm'
  end as status,
  split_part(r.address, '.', 2) || case
    when g.address is null then ' restricts FTP access from internet through port 22'
    else ' allows FTP access from internet through port 22'
  end || '.' reason
  
  , path || ':' || start_line
from
  terraform_resource as r
  left join rules as g on g.address = r.address
where
  type = 'google_compute_firewall';