Query: s3_bucket_https_unenforced_count

with ssl_ok as (
  select
    distinct name,
    arn,
    'ok' as status
  from
    aws_s3_bucket,
    jsonb_array_elements(policy_std -> 'Statement') as s,
    jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
    jsonb_array_elements_text(s -> 'Action') as a,
    jsonb_array_elements_text(s -> 'Resource') as r,
    jsonb_array_elements_text(s -> 'Condition' -> 'Bool' -> 'aws: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
  aws_s3_bucket as b
where
  b.arn not in (
    select
      arn
    from
      ssl_ok
  );