CIS 2.0.0 Control 1.11 Query is wrong

[turbot]

CIS 2.0.0 Control 1.11 Query is wrong

[rajlearner17]

@ResonanceSecurity Thanks for using Steampipe and helping with this finding.

The fix is applied in this PR

Attaching the query here to give it a try at your end and share feedback with us.

  with users_with_roles as (
      select
        distinct split_part(member_entity, ':', 2) as user_name,
        project,
        _ctx,
        array_agg(distinct p ->> 'role') as assigned_roles
      from
        gcp_iam_policy,
        jsonb_array_elements(bindings) as p,
        jsonb_array_elements_text(p -> 'members') as member_entity
      where
        split_part(member_entity, ':', 1) = 'user'
      group by
        user_name,
        project,
        _ctx
    ),
    kms_roles_users as (
      select
        user_name,
        project,
        assigned_roles
      from
        users_with_roles
      where
        'roles/cloudkms.admin' = any(assigned_roles)
        and assigned_roles && array['roles/cloudkms.cryptoKeyEncrypterDecrypter', 'roles/cloudkms.cryptoKeyEncrypter', 'roles/cloudkms.cryptoKeyDecrypter']
    )
    select
      distinct r.user_name as resource,
      case
        when 'roles/cloudkms.admin' = any(r.assigned_roles) and k.user_name is null then 'ok'
        when k.user_name is not null then 'alarm'
        else 'ok'
      end as status,
      case
        when 'roles/cloudkms.admin' = any(r.assigned_roles) and k.user_name is null then r.user_name || ' assigned only with KMS admin role.'
        when k.user_name is not null then r.user_name || ' assigned with roles/cloudkms.admin, ' ||
          concat_ws(', ',
            case when 'roles/cloudkms.cryptoKeyEncrypterDecrypter' = any(r.assigned_roles) then 'roles/cloudkms.cryptoKeyEncrypterDecrypter' end,
            case when 'roles/cloudkms.cryptoKeyEncrypter' = any(r.assigned_roles) then 'roles/cloudkms.cryptoKeyEncrypter' end,
            case when 'roles/cloudkms.cryptoKeyDecrypter' = any(r.assigned_roles) then 'roles/cloudkms.cryptoKeyDecrypter' end
            ) || ' KMS role(s).'
        else r.user_name || ' not assigned with KMS admin and additional encrypter/decrypter roles.'
      end as reason
      --${replace(local.common_dimensions_qualifier_global_sql, "__QUALIFIER__", "r.")}
    from
      users_with_roles as r
      left join kms_roles_users as k on k.user_name = r.user_name and k.project = r.project

CC @khushboo9024