Query: cloudtrail_trail_bucket_publicly_accessible

with public_bucket_data as (
  select
    t.s3_bucket_name as name,
    b.arn,
    t.region,
    t.account_id,
    count(acl_grant) filter (
      where
        acl_grant -> 'Grantee' ->> 'URI' like '%acs.amazonaws.com/groups/global/AllUsers'
    ) as all_user_grants,
    count(acl_grant) filter (
      where
        acl_grant -> 'Grantee' ->> 'URI' like '%acs.amazonaws.com/groups/global/AuthenticatedUsers'
    ) as auth_user_grants,
    count(s) filter (
      where
        s ->> 'Effect' = 'Allow'
        and p = '*'
    ) as anon_statements
  from
    aws_cloudtrail_trail as t
    left join aws_s3_bucket as b on t.s3_bucket_name = b.name
    left join jsonb_array_elements(acl -> 'Grants') as acl_grant on true
    left join jsonb_array_elements(policy_std -> 'Statement') as s on true
    left join jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p on true
  where
    t.region = t.home_region
  group by
    t.s3_bucket_name,
    b.arn,
    t.region,
    t.account_id
),
bucket_status as (
  select
    case
      when all_user_grants > 0
      or auth_user_grants > 0
      or anon_statements > 0 then 'public'
      else 'private'
    end as bucket_publicly_accessible_status
  from
    public_bucket_data
)
select
  bucket_publicly_accessible_status,
  count(*)
from
  bucket_status
group by
  bucket_publicly_accessible_status;