Query: oss_bucket_by_ssl_enforced_status

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;