SQL Query to get list of S3 buckets exposed to internet

SQL Query to get list of S3 buckets exposed to internet

  2. WITH nested_acl_grants as (
  3.   select
  4.     *,
  5.     contains(
  6.       transform(
  7.         CAST(acl_grants AS ARRAY < JSON >),
  8.         a -> json_extract_scalar(a, '$.Grantee.URI') in (
  9.           'http://acs.amazonaws.com/groups/global/AllUsers',
  10.           'http://acs.amazonaws.com/groups/global/AuthenticatedUsers'
  11.         )
  12.       ),
  13.       true
  14.     ) as acl_flag
  15.   from
  16.     aws_s3_bucket_current
  17. ),
  18. unnested_acl_grants as(
  19.   select
  20.     *,
  21.     CASE WHEN ignore_public_acls = true
  22.     and restrict_public_buckets = true THEN false WHEN (
  23.       restrict_public_buckets = false
  24.       or restrict_public_buckets is null
  25.     )
  26.     and is_bucket_policy_status_public = true THEN true WHEN (
  27.       ignore_public_acls is null
  28.       or ignore_public_acls = false
  29.     )
  30.     and acl_flag = true
  31.     and acl_flag is not null THEN true else false end as public_access
  32.   from
  33.     nested_acl_grants
  34. ),
  35. s3 AS (
  36.   select
  37.     a.region_code AS regionCode,
  38.     a.bucket_name AS bucketName,
  39.     a.customer_id AS customerId,
  40.     a.acl_grants AS aclGrants,
  41.     a.acl_owner_display_name AS aclOwnerDisplayName,
  42.     a.acl_owner_id AS aclOwnerId,
  43.     a.block_public_acls AS blockPublicAcls,
  44.     a.block_public_policy AS blockPublicPolicy,
  45.     a.bucket_accelerate_status AS bucketAccelerateStatus,
  46.     a.bucket_cors_enabled AS bucketCorsEnabled,
  47.     a.bucket_creation_date AS bucketCreationDate,
  48.     a.bucket_logging_enabled AS bucketLoggingEnabled,
  49.     a.bucket_logging_target_bucket AS bucketLoggingTargetBucket,
  50.     a.bucket_logging_target_grants AS bucketLoggingTargetGrants,
  51.     a.bucket_logging_target_prefix AS bucketLoggingTargetPrefix,
  52.     a.bucket_policy_serverside_encryption_enabled AS bucketPolicyServersideEncryptionEnabled,
  53.     a.bucket_public_access_block_enabled AS bucketPublicAccessBlockEnabled,
  54.     a.bucket_region AS bucketRegion,
  55.     a.bucket_request_payer AS bucketRequestPayer,
  56.     a.bucket_ssl_access_only AS bucketSslAccessOnly,
  57.     a.bucket_versioning_status_enabled AS bucketVersioningStatusEnabled,
  58.     a.cors_rules AS corsRules,
  59.     a.ignore_public_acls AS ignorePublicAcls,
  60.     a.is_bucket_policy_status_public AS isBucketPolicyStatusPublic,
  61.     a.mfa_delete_status_enabled AS mfaDeleteStatusEnabled,
  62.     a.metrics_configuration_list AS metricsConfigurationList,
  63.     a.notification_config_event_bridge_config AS notificationConfigEventBridgeConfig,
  64.     a.notification_config_lambda_function_configs AS notificationConfigLambdaFunctionConfigs,
  65.     a.notification_config_queue_configs AS notificationConfigQueueConfigs,
  66.     a.notification_config_topic_configs AS notificationConfigTopicConfigs,
  67.     a.object_lock_configuration_rule AS objectLockConfigurationRule,
  68.     a.object_lock_enabled AS objectLockEnabled,
  69.     a.object_ownership_control_rules AS objectOwnershipControlRules,
  70.     a.public_access_block_enabled AS publicAccessBlockEnabled,
  71.     a.restrict_public_buckets AS restrictPublicBuckets,
  72.     a.server_side_encryption_rules AS serverSideEncryptionRules,
  73.     a.serverside_encryption_enabled AS serversideEncryptionEnabled,
  74.     a.tags AS tags,
  75.     a.website_enabled AS websiteEnabled,
  76.     a.account_id AS accountId,
  77.     a.region AS region,
  78.     b.public_access AS isBucketPublic
  79.   FROM
  80.     aws_s3_bucket_current a
  81.     INNER JOIN unnested_acl_grants b ON a.bucket_name = b.bucket_name
  82. )
  83. SELECT
  84.     *
  85. FROM
  86.     s3
  87. WHERE
  88.   
  89.   isBucketPublic=true