Query: vcn_security_list_restrict_ingress_ssh_all

with all_security_list as (
  select
    *
  from
    terraform_resource
  where
    type = 'oci_core_security_list'
), non_complaint as (
  select
    name,
    count(name) as count
  from
    all_security_list,
    jsonb_array_elements(
    case jsonb_typeof(attributes_std -> 'ingress_security_rules')
      when 'array' then (attributes_std -> 'ingress_security_rules')
      else null end
    ) as p
  where
    p ->> 'source' = '0.0.0.0/0'
    and (
      p ->> 'protocol' = 'all'
      or (
        p ->> 'protocol' = '6'
        and (p -> 'tcp_options' ->> 'min')::integer <= 22
        and (p -> 'tcp_options' ->> 'max')::integer >= 22
      )
    )
  group by
  name
)
select
  a.address as resource,
  case
    when b.count > 0 or
      ((a.attributes_std -> 'ingress_security_rules' ->> 'source' = '0.0.0.0/0' )
      and (
        (a.attributes_std -> 'ingress_security_rules' ->> 'protocol' = 'all')
        or ((a.attributes_std -> 'ingress_security_rules' ->> 'protocol' = '6')
          and (a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' is null))
        or ((a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' ->> 'min') ::integer <= 22
          and (a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' ->> 'max')::integer >= 22
        )
      ))
    then 'alarm'
    else 'ok'
  end as status,
  split_part(a.address, '.', 2) || case
    when b.count > 0  or
      ((a.attributes_std -> 'ingress_security_rules' ->> 'source' = '0.0.0.0/0' )
      and (
        (a.attributes_std -> 'ingress_security_rules' ->> 'protocol' = 'all')
        or ((a.attributes_std -> 'ingress_security_rules' ->> 'protocol' = '6')
          and (a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' is null))
        or ((a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' ->> 'min') ::integer <= 22
          and (a.attributes_std -> 'ingress_security_rules' -> 'tcp_options' ->> 'max')::integer >= 22
        )
      ))
      then ' ingress rule(s) allowing SSH from 0.0.0.0/0'
    else ' ingress restricted for SSH from 0.0.0.0/0'
  end || '.' reason
  
  , path || ':' || start_line
from
  all_security_list as a
  left join non_complaint as b on a.name = b.name;