Query: s3_bucket_https_enforce

with ssl_ok as (
  select
    distinct name
  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
  'HTTPS' as label,
  case when s.name is not null then 'Enforced' else 'Not Enforced' end as value,
  case when s.name is not null then 'ok' else 'alert' end as type
from
  aws_s3_bucket as b
  left join ssl_ok as s on s.name = b.name
where
  arn = $1;