Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

The AWS policy query in enabled_in_all_regions.sql does not properly handle global CloudTrails or Organization-wide CloudTrails.

With a global CloudTrail, the trails in each region have the same ARN. In that situation, the inner join in the query turns into a many-to-many join instead of a one-to-one join (i.e. one aws_cloudtrail_trails record to one aws_cloudtrail_trail_event_selectors records).

With Organization-wide CloudTrails, the trails in all accounts have the same ARN. This again results in the inner join returning a many-to-many results instead of one-to-one.

Expected Behavior

The inner join should be a one-to-many join in all situations, regardless of using global CloudTrails or not. I.e., a one aws_cloudtrail_trails record to one aws_cloudtrail_trail_event_selectors records.

CloudQuery (redacted) config

---
kind: source
spec:
  name: team-daily
  path: cloudquery/aws
  version: "v16.0.1"

  tables:
  - aws_acm_certificates
  # ...
  - aws_waf_web_acls

  destinations: ["postgresql"]
  spec: 
    regions: 
      - "*"
    accounts:
      - id: my-account
        role_arn: "arn:aws:iam::123456789012:role/somerole"
---
kind: destination
spec:
  name: postgresql
  path: cloudquery/postgresql
  version: "v3.0.2"
  spec:
    connection_string: "postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}?sslmode=${DB_SSLMODE}"
  migrate_mode: forced

Steps To Reproduce

1. Configure target AWS account to use a global CloudTrail.
2. Run sync to collect CloudTrail data from AWS in target account.
3. Run any AWS policy query collection that includes the enabled_in_all_regions.sql query.

CloudQuery (redacted) logs

n/a

CloudQuery version

cloudquery version 2.5.2

Additional Context

No response

Pull request (optional)

• I can submit a pull request