Trigger: Detect & correct CloudTrail trails using public S3 bucket

with public_bucket_data as (
  select
    t.s3_bucket_name as name,
    b.arn,
    t.region,
    t.account_id,
    t.tags,
    t.sp_connection_name,
    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
  group by
    t.s3_bucket_name,
    b.arn,
    t.region,
    t.account_id,
    t.tags,
    t.sp_connection_name
)
select
  concat(name, ' [', account_id, '/', region, ']') as title,
  name,
  region,
  account_id,
  sp_connection_name as conn
from
  public_bucket_data
where
  all_user_grants > 0
  and auth_user_grants > 0
  and anon_statements > 0;