turbot/terraform_oci_compliance

Query: vcn_security_list_restrict_ingress_ssh_all

Usage

powerpipe query terraform_oci_compliance.query.vcn_security_list_restrict_ingress_ssh_all

Steampipe Tables

SQL

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;

Controls

The query is being used by the following controls: