turbot/alicloud_insights

Query: oss_bucket_encryption_table

Usage

powerpipe query alicloud_insights.query.oss_bucket_encryption_table

SQL

with ssl_ok as (
select
distinct name,
arn,
'ok' as status
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
)
select
b.name as "Name",
case when ssl.status = 'ok' then 'Enabled' else null end as "HTTPS Enforced",
case when b.server_side_encryption ->> 'SSEAlgorithm' <> '' then 'Enabled' else null end as "Default Encryption",
b.server_side_encryption ->> 'SSEAlgorithm' as "SSE Algorithm",
k.key_id as "KMS Key ID",
k.creator as "Creator",
a.title as "Account",
b.account_id as "Account ID",
b.region as "Region",
b.arn as "ARN"
from
alicloud_oss_bucket as b
left join alicloud_kms_key k on b.server_side_encryption ->> 'KMSMasterKeyID' = k.key_id
left join ssl_ok as ssl on b.arn = ssl.arn
left join alicloud_account as a on b.account_id = a.account_id
order by
b.name;

Dashboards

The query is used in the dashboards: