Query: network_security_group_ssh_access_restricted

with nsg_group as (
  select
    distinct address as name
  from
    terraform_resource,
    jsonb_array_elements(
      case jsonb_typeof(attributes_std -> 'security_rule')
        when 'array' then (attributes_std -> 'security_rule')
        when 'object' then jsonb_build_array(attributes_std -> 'security_rule')
        else null end
      ) sg,
    jsonb_array_elements_text(
      case
        when ((sg -> 'destination_port_ranges') != 'null') and jsonb_array_length(sg -> 'destination_port_ranges') > 0 then (sg -> 'destination_port_ranges')
        else jsonb_build_array(sg -> 'destination_port_range')
      end ) as dport,
    jsonb_array_elements_text(
      case
        when ((sg -> 'source_address_prefixes') != 'null') and jsonb_array_length(sg -> 'source_address_prefixes') > 0 then (sg -> 'source_address_prefixes')
        else jsonb_build_array(sg -> 'source_address_prefix')
      end) as sip
  where
    type = 'azurerm_network_security_group'
    and lower(sg ->> 'access') = 'allow'
    and lower(sg ->> 'direction') = 'inbound'
    and (lower(sg ->> 'protocol') ilike 'TCP' or lower(sg ->> '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 g.name is null then 'ok'
    else 'alarm'
  end as status,
  split_part(r.address, '.', 2) || case
    when g.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_group as g on g.name = r.name
where
  type = 'azurerm_network_security_group';