Control: Network security groups should restrict inbound TCP port 1433 access from internet

powerpipe control run azure_compliance.control.network_security_group_restrict_inbound_tcp_port_1433

powerpipe login
powerpipe control run azure_compliance.control.network_security_group_restrict_inbound_tcp_port_1433 --share

with unrestricted_inbound as (
  select
    distinct name sg_name
  from
    azure_network_security_group nsg,
    jsonb_array_elements(security_rules || default_security_rules) sg,
    jsonb_array_elements_text(
      case
        when jsonb_array_length(sg -> 'properties' -> 'destinationPortRanges') > 0 then (sg -> 'properties' -> 'destinationPortRanges')
        else jsonb_build_array(sg -> 'properties' -> 'destinationPortRange')
      end
    ) as dport,
    jsonb_array_elements_text(
      case
        when jsonb_array_length(sg -> 'properties' -> 'sourceAddressPrefixes') > 0 then (sg -> 'properties' -> 'sourceAddressPrefixes')
        else jsonb_build_array(sg -> 'properties' -> 'sourceAddressPrefix')
      end
    ) as sip
  where
    sg -> 'properties' ->> 'access' = 'Allow'
    and sg -> 'properties' ->> 'direction' = 'Inbound'
    and (
      sg -> 'properties' ->> 'protocol' ilike 'TCP'
      or sg -> 'properties' ->> 'protocol' = '*'
    )
    and sip in (
      '*',
      '0.0.0.0',
      '0.0.0.0/0',
      'Internet',
      'any',
      '<nw>/0',
      '/0'
    )
    and (
      dport in ('1433', '*')
      or (
        dport like '%-%'
        and (
          split_part(dport, '-', 1) :: integer = 1433
          and split_part(dport, '-', 2) :: integer = 214335
        )
      )
    )
)
select
  sg.id resource,
  case
    when nsg.sg_name is null then 'ok'
    else 'alarm'
  end as status,
  case
    when nsg.sg_name is null then sg.title || ' restricts TCP port 1433 access from internet.'
    else sg.title || ' allows TCP port 1433 access from internet.'
  end as reason,
  sg.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_network_security_group sg
  left join unrestricted_inbound nsg on nsg.sg_name = sg.name
  join azure_subscription sub on sub.subscription_id = sg.subscription_id;