Query: oss_bucket_encryption_table

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;