turbot/terraform_azure_compliance

Query: network_security_group_ssh_access_restricted

Usage

powerpipe query terraform_azure_compliance.query.network_security_group_ssh_access_restricted

Steampipe Tables

SQL

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';

Controls

The query is being used by the following controls: