Query: network_security_rule_ssh_access_restricted

with nsg_rule as (
  select
    distinct address as name
  from
    terraform_resource,
    jsonb_array_elements_text(
      case
        when (
          (attributes_std -> 'destination_port_ranges') != 'null'
        )
        and jsonb_array_length(attributes_std -> 'destination_port_ranges') > 0 then (attributes_std -> 'destination_port_ranges')
        else jsonb_build_array(attributes_std -> 'destination_port_range')
      end
    ) as dport,
    jsonb_array_elements_text(
      case
        when (
          (attributes_std -> 'source_address_prefixes') != 'null'
        )
        and jsonb_array_length(attributes_std -> 'source_address_prefixes') > 0 then (attributes_std -> 'source_address_prefixes')
        else jsonb_build_array(attributes_std -> 'source_address_prefix')
      end
    ) as sip
  where
    type = 'azurerm_network_security_rule'
    and lower(attributes_std ->> 'access') = 'allow'
    and lower(attributes_std ->> 'direction') = 'inbound'
    and (
      lower(attributes_std ->> 'protocol') ilike 'TCP'
      or lower(attributes_std ->> 'protocol') = '*'
    )
    and lower(sip) in (
      '*',
      '0.0.0.0',
      '0.0.0.0/0',
      'internet',
      'any',
      '<nw>/0',
      '/0'
    )
    and (
      dport in ('22', '*')
      or (
        dport like '%-%'
        and split_part(dport, '-', 1) :: integer <= 22
        and split_part(dport, '-', 2) :: integer >= 22
      )
    )
)
select
  r.address as resource,
  case
    when rule.name is null then 'ok'
    else 'alarm'
  end as status,
  split_part(r.address, '.', 2) || case
    when rule.name is null then ' restricts SSH access from internet'
    else ' allows SSH access from internet'
  end || '.' reason,
  path || ':' || start_line
from
  terraform_resource as r
  left join nsg_rule as rule on rule.name = r.name
where
  type = 'azurerm_network_security_rule';