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
  );