Query: oss_bucket_ssl_not_enforced_count

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
)
select
  count(*) as value,
  'HTTPS Unenforced' as label,
  case count(*) when 0 then 'ok' else 'alert' end as "type"
from
  alicloud_oss_bucket b
where
  b.name not in (
    select name from ssl_ok
  );