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;