Table: aws_cloudfront_distributions

SELECT
  'CloudFront distributions should have logging enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (distribution_config->'Logging'->>'Enabled')::BOOL IS NOT true
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudfront_distributions;

SELECT
  'Find all CloudFront distributions' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_cloudfront_distributions;

SELECT
  'API Gateway should be associated with an AWS WAF web ACL' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN distribution_config->>'WebACLId' = '' THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudfront_distributions;

SELECT
  'CloudFront distributions should have a default root object configured'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN distribution_config->>'DefaultRootObject' = '' THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudfront_distributions;

SELECT
  'Disabled CloudFront distribution' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_cloudfront_distributions
WHERE
  (distribution_config->>'Enabled')::BOOL IS NOT true;

WITH
  origin_groups
    AS (
      SELECT
        acd.arn, distribution_config->'OriginGroups'->'Items' AS ogs
      FROM
        aws_cloudfront_distributions AS acd
    ),
  oids
    AS (
      SELECT
        DISTINCT
        account_id,
        acd.arn AS resource_id,
        CASE
        WHEN o.ogs = 'null'
        OR o.ogs->'Members'->'Items' = 'null'
        OR jsonb_array_length(o.ogs->'Members'->'Items') = 0
        THEN 'fail'
        ELSE 'pass'
        END
          AS status
      FROM
        aws_cloudfront_distributions AS acd
        LEFT JOIN origin_groups AS o ON o.arn = acd.arn
    )
SELECT
  'CloudFront distributions should have origin failover configured' AS title,
  account_id,
  resource_id,
  status
FROM
  oids;

WITH
  cachebeviors
    AS (
      SELECT
        DISTINCT arn, account_id
      FROM
        (
          SELECT
            arn, account_id, d AS cachebehavior
          FROM
            aws_cloudfront_distributions,
            jsonb_array_elements(distribution_config->'CacheBehaviors'->'Items')
              AS d
          WHERE
            distribution_config->'CacheBehaviors'->'Items' != 'null'
          UNION
            SELECT
              arn,
              account_id,
              distribution_config->'DefaultCacheBehavior' AS cachebehavior
            FROM
              aws_cloudfront_distributions
        )
          AS cachebeviors
      WHERE
        cachebehavior->>'ViewerProtocolPolicy' = 'allow-all'
    )
SELECT
  'CloudFront distributions should require encryption in transit' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  cachebeviors;