Table: aws_rds_clusters

SELECT
  'Amazon Aurora clusters should have backtracking enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN backtrack_window IS NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_rds_clusters
WHERE
  engine IN ('aurora', 'aurora-mysql', 'mysql');

SELECT
  'IAM authentication should be configured for RDS clusters' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN iam_database_authentication_enabled IS NOT true THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_clusters;

SELECT
  'RDS clusters should have deletion protection enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN deletion_protection IS NOT true THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_clusters;

(
  SELECT
    'RDS databases and clusters should not use a database engine default port'
      AS title,
    account_id,
    arn AS resource_id,
    CASE
    WHEN (engine IN ('aurora', 'aurora-mysql', 'mysql') AND port = 3306)
    OR (engine LIKE '%postgres%' AND port = 5432)
    THEN 'fail'
    ELSE 'pass'
    END
      AS status
  FROM
    aws_rds_clusters
)
UNION
  (
    SELECT
      'RDS databases and clusters should not use a database engine default port'
        AS title,
      account_id,
      arn AS resource_id,
      CASE
      WHEN (
        engine IN ('aurora', 'aurora-mysql', 'mariadb', 'mysql')
        AND (endpoint->>'Port')::INT8 = 3306
      )
      OR (engine LIKE '%postgres%' AND (endpoint->>'Port')::INT8 = 5432)
      OR (engine LIKE '%oracle%' AND (endpoint->>'Port')::INT8 = 1521)
      OR (engine LIKE '%sqlserver%' AND (endpoint->>'Port')::INT8 = 1433)
      THEN 'fail'
      ELSE 'pass'
      END
        AS status
    FROM
      aws_rds_instances
  );

SELECT
  'RDS DB clusters should be configured for multiple Availability Zones'
    AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN multi_az IS NOT true THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_rds_clusters;

SELECT
  'RDS DB clusters should be configured to copy tags to snapshots' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN copy_tags_to_snapshot IS NOT true THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_clusters;

WITH
  any_category
    AS (
      SELECT
        DISTINCT true AS any_category
      FROM
        aws_rds_event_subscriptions
      WHERE
        (source_type IS NULL OR source_type = 'db-cluster')
        AND event_categories_list IS NULL
    ),
  any_source_id
    AS (
      SELECT
        COALESCE(array_agg(category), '{}'::STRING[]) AS any_source_categories
      FROM
        aws_rds_event_subscriptions, unnest(event_categories_list) AS category
      WHERE
        source_type = 'db-cluster' AND event_categories_list IS NOT NULL
    ),
  specific_categories
    AS (
      SELECT
        source_id, array_agg(category) AS specific_cats
      FROM
        aws_rds_event_subscriptions,
        unnest(source_ids_list) AS source_id,
        unnest(event_categories_list) AS category
      WHERE
        source_type = 'db-cluster'
      GROUP BY
        source_id
    )
SELECT
  'An RDS event notifications subscription should be configured for critical cluster events'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN any_category IS NOT true
  AND NOT (any_source_categories @> '{"failure","maintenance"}')
  AND (
      specific_cats IS NULL
      OR NOT (specific_cats @> '{"failure","maintenance"}')
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_clusters
  LEFT JOIN any_category ON true
  INNER JOIN any_source_id ON true
  LEFT JOIN specific_categories ON
      db_cluster_identifier = specific_categories.source_id;