Table: aws_rds_instances

SELECT
  'Database logging should be enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN enabled_cloudwatch_logs_exports IS NULL
  OR (
      engine IN ('aurora', 'aurora-mysql', 'mariadb', 'mysql')
      AND NOT
          (enabled_cloudwatch_logs_exports @> '{audit,error,general,slowquery}')
    )
  OR (
      engine LIKE '%postgres%'
      AND NOT (enabled_cloudwatch_logs_exports @> '{postgresql,upgrade}')
    )
  OR (
      engine LIKE '%oracle%'
      AND NOT
          (enabled_cloudwatch_logs_exports @> '{alert,audit,trace,listener}')
    )
  OR (
      engine LIKE '%sqlserver%'
      AND NOT (enabled_cloudwatch_logs_exports @> '{error,agent}')
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_instances;

SELECT
  'Enhanced monitoring should be configured for RDS DB instances and clusters'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN enhanced_monitoring_resource_arn IS NULL THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_instances;

SELECT
  'IAM authentication should be configured for RDS instances' 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_instances;

SELECT
  'RDS automatic minor version upgrades should be enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN auto_minor_version_upgrade IS NOT true THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_instances;

(
  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 instances 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_instances;

SELECT
  'RDS DB instances should be configured with 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_instances;

SELECT
  'RDS DB instances 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_instances;

SELECT
  'RDS DB instances should have encryption at rest enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN storage_encrypted IS NOT true THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_rds_instances;

SELECT
  'RDS DB instances should prohibit public access, determined by the PubliclyAccessible configuration'
    AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN publicly_accessible IS true THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_rds_instances;

WITH
  any_category
    AS (
      SELECT
        DISTINCT true AS any_category
      FROM
        aws_rds_event_subscriptions
      WHERE
        (source_type IS NULL OR source_type = 'db-instance')
        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-instance' 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-instance'
      GROUP BY
        source_id
    )
SELECT
  'An RDS event notifications subscription should be configured for critical database instance events'
    AS title,
  aws_rds_instances.account_id,
  aws_rds_instances.arn AS resource_id,
  CASE
  WHEN any_category IS NOT true
  AND NOT
      (
        any_source_categories
        @> '{"maintenance","configuration change","failure"}'
      )
  AND (
      specific_cats IS NULL
      OR NOT
          (specific_cats @> '{"maintenance","configuration change","failure"}')
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_instances
  LEFT JOIN any_category ON true
  INNER JOIN any_source_id ON true
  LEFT JOIN specific_categories ON
      db_instance_identifier = specific_categories.source_id;

SELECT
  'RDS instances should be deployed in a VPC' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (db_subnet_group->>'VpcId') IS NULL THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_instances;