Table: aws_s3_buckets

SELECT
  'Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket'
    AS title,
  t.account_id,
  t.arn AS resource_id,
  CASE
  WHEN b.logging_target_bucket IS NULL OR b.logging_target_prefix IS NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudtrail_trails AS t
  INNER JOIN aws_s3_buckets AS b ON t.s3_bucket_name = b.name;

SELECT
  'S3 Block Public Access setting should be enabled at the bucket-level'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN block_public_acls IS NOT true
  OR block_public_policy IS NOT true
  OR ignore_public_acls IS NOT true
  OR restrict_public_buckets IS NOT true
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_s3_buckets;

SELECT
  'S3 buckets should deny non-HTTPS requests' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_s3_buckets
WHERE
  arn
  NOT IN (
      SELECT
        arn
      FROM
        (
          SELECT
            aws_s3_buckets.arn,
            statements,
            statements->'Principal' AS principals
          FROM
            aws_s3_buckets,
            jsonb_array_elements(
              CASE jsonb_typeof(policy->'Statement')
              WHEN 'string' THEN jsonb_build_array(policy->>'Statement')
              WHEN 'array' THEN policy->'Statement'
              END
            )
              AS statements
          WHERE
            statements->'Effect' = '"Deny"'
        )
          AS foo,
        jsonb_array_elements_text(
          statements->'Condition'->'Bool'->'aws:securetransport'
        )
          AS ssl
      WHERE
        principals = '"*"'
        OR (
            principals::JSONB ? 'AWS'
            AND (principals->'AWS' = '"*"' OR principals->'AWS' @> '"*"')
          )
          AND ssl::BOOL = false
    );

SELECT
  'Ensure MFA Delete is enabled on S3 buckets (Automated)' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN versioning_status IS DISTINCT FROM 'Enabled'
  OR versioning_mfa_delete IS DISTINCT FROM 'Enabled'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_s3_buckets;

WITH
  policy_allow_public
    AS (
      SELECT
        arn, count(*) AS statement_count
      FROM
        (
          SELECT
            aws_s3_buckets.arn, statements->'Principal' AS principals
          FROM
            aws_s3_buckets,
            jsonb_array_elements(
              CASE jsonb_typeof(policy::JSONB->'Statement')
              WHEN 'string' THEN jsonb_build_array(policy::JSONB->>'Statement')
              WHEN 'array' THEN policy::JSONB->'Statement'
              END
            )
              AS statements
          WHERE
            statements->'Effect' = '"Allow"'
        )
          AS foo
      WHERE
        principals = '"*"'
        OR (
            principals::JSONB ? 'AWS'
            AND (principals->'AWS' = '"*"' OR principals->'AWS' @> '"*"')
          )
      GROUP BY
        arn
    )
SELECT
  'S3 buckets should prohibit public read access' AS title,
  aws_s3_buckets.account_id,
  aws_s3_buckets.arn AS resource_id,
  'fail' AS status
FROM
  aws_s3_buckets
  LEFT JOIN aws_s3_bucket_grants ON
      aws_s3_buckets.arn = aws_s3_bucket_grants.bucket_arn
  LEFT JOIN policy_allow_public ON aws_s3_buckets.arn = policy_allow_public.arn
WHERE
  (
    aws_s3_buckets.block_public_acls != true
    AND (
        grantee->>'URI' = 'http://acs.amazonaws.com/groups/global/AllUsers'
        AND permission IN ('READ_ACP', 'FULL_CONTROL')
      )
  )
  OR (
      aws_s3_buckets.block_public_policy != true
      AND policy_allow_public.statement_count > 0
    );

WITH
  policy_allow_public
    AS (
      SELECT
        arn, count(*) AS statement_count
      FROM
        (
          SELECT
            aws_s3_buckets.arn, statements->'Principal' AS principals
          FROM
            aws_s3_buckets,
            jsonb_array_elements(
              CASE jsonb_typeof(policy::JSONB->'Statement')
              WHEN 'string' THEN jsonb_build_array(policy::JSONB->>'Statement')
              WHEN 'array' THEN policy::JSONB->'Statement'
              END
            )
              AS statements
          WHERE
            statements->'Effect' = '"Allow"'
        )
          AS foo
      WHERE
        principals = '"*"'
        OR (
            principals::JSONB ? 'AWS'
            AND (principals->'AWS' = '"*"' OR principals->'AWS' @> '"*"')
          )
      GROUP BY
        arn
    )
SELECT
  'S3 buckets should prohibit public write access' AS title,
  aws_s3_buckets.account_id,
  aws_s3_buckets.arn AS resource_id,
  'fail' AS status
FROM
  aws_s3_buckets
  LEFT JOIN aws_s3_bucket_grants ON
      aws_s3_buckets.arn = aws_s3_bucket_grants.bucket_arn
  LEFT JOIN policy_allow_public ON aws_s3_buckets.arn = policy_allow_public.arn
WHERE
  (
    aws_s3_buckets.block_public_acls != true
    AND (
        grantee->>'URI' = 'http://acs.amazonaws.com/groups/global/AllUsers'
        AND permission IN ('WRITE_ACP', 'FULL_CONTROL')
      )
  )
  OR (
      aws_s3_buckets.block_public_policy != true
      AND policy_allow_public.statement_count > 0
    );

SELECT
  'Amazon S3 permissions granted to other AWS accounts in bucket policies should be restricted'
    AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  (
    SELECT
      aws_s3_buckets.arn,
      account_id,
      name,
      region,
      CASE
      WHEN jsonb_typeof(statements->'Principal') = 'string'
      THEN jsonb_build_array(statements->'Principal')
      WHEN jsonb_typeof(statements->'Principal'->'AWS') = 'string'
      THEN jsonb_build_array(statements->'Principal'->'AWS')
      WHEN jsonb_typeof(statements->'Principal'->'AWS') = 'array'
      THEN statements->'Principal'->'AWS'
      END
        AS principals,
      CASE
      WHEN jsonb_typeof(statements->'Action') = 'string'
      THEN jsonb_build_array(statements->'Action')
      WHEN jsonb_typeof(statements->'Action') = 'array'
      THEN statements->'Action'
      END
        AS actions
    FROM
      aws_s3_buckets,
      jsonb_array_elements(
        CASE jsonb_typeof(policy->'Statement')
        WHEN 'string' THEN jsonb_build_array(policy->>'Statement')
        WHEN 'array' THEN policy->'Statement'
        END
      )
        AS statements
    WHERE
      statements->'Effect' = '"Allow"'
  )
    AS flatten_statements,
  jsonb_array_elements(to_jsonb(actions)) AS a,
  jsonb_array_elements(to_jsonb(principals)) AS p
WHERE
  (
    p.value::STRING NOT LIKE '"arn:aws:iam::' || account_id || ':%"'
    OR p.value::STRING = '"*"'
  )
  AND (
      a.value::STRING LIKE '"s3:%*"'
      OR a.value::STRING LIKE '"s3:DeleteObject"'
    );

SELECT
  'S3 buckets with replication rules should be enabled' AS title,
  aws_s3_buckets.account_id,
  aws_s3_buckets.arn AS resource_id,
  CASE
  WHEN r->>'Status' IS DISTINCT FROM 'Enabled' THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_s3_buckets,
  jsonb_array_elements(
    CASE jsonb_typeof(replication_rules)
    WHEN 'array' THEN replication_rules
    ELSE '[]'
    END
  )
    AS r;

SELECT
  'S3 buckets should have server-side encryption enabled' AS title,
  aws_s3_buckets.account_id,
  arn AS resource_id,
  CASE
  WHEN aws_s3_bucket_encryption_rules.bucket_arn IS NULL THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_s3_buckets
  LEFT JOIN aws_s3_bucket_encryption_rules ON
      aws_s3_bucket_encryption_rules.bucket_arn = aws_s3_buckets.arn;