Query: compute_firewall_allow_ftp_port_21_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 ('21', '*')
      or (
        port like '%-%'
        and split_part(port, '-', 1) :: integer <= 21
        and split_part(port, '-', 2) :: integer >= 21
      )
    )
)
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 21'
    else ' allows FTP access from internet through port 21'
  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';