Query: cloudfront_distribution_use_secure_cipher

with origin_protocols as (
  select
    distinct arn,
    o -> 'CustomOriginConfig' ->> 'OriginSslProtocols' as origin_ssl_policy
  from
    aws_cloudfront_distribution,
    jsonb_array_elements(origins) as o
  where
    o -> 'CustomOriginConfig' -> 'OriginSslProtocols' -> 'Items' @> '["TLSv1"]'
    or o -> 'CustomOriginConfig' -> 'OriginSslProtocols' -> 'Items' @> '["SSLv3"]'
)
select
  distinct b.arn as resource,
  case
    when o.arn is null then 'ok'
    else 'alarm'
  end as status,
  case
    when o.arn is null then title || ' uses secure cipher.'
    else title || ' does not use secure cipher.'
  end as reason
  
  , b.region, b.account_id
from
  aws_cloudfront_distribution as b
  left join origin_protocols as o on b.arn = o.arn;