turbot/alicloud_insights

Query: oss_bucket_by_ssl_enforced_status

Usage

powerpipe query alicloud_insights.query.oss_bucket_by_ssl_enforced_status

Steampipe Tables

SQL

with ssl_ok as (
select
distinct name
from
alicloud_oss_bucket,
jsonb_array_elements(policy -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Principal') as p,
jsonb_array_elements_text(s -> 'Resource') as r,
jsonb_array_elements_text(
s -> 'Condition' -> 'Bool' -> 'acs:SecureTransport'
) as ssl
where
p = '*'
and s ->> 'Effect' = 'Deny'
and ssl :: bool = false
),
ssl_enforced_status as (
select
case
when s.name is not null then 'enforced' else 'not enforced'
end as status
from
alicloud_oss_bucket b
left join ssl_ok s on s.name = b.name
)
select
status,
count(*)
from
ssl_enforced_status
group by
status;

Dashboards

The query is used in the dashboards: