Query: kms_key_separation_of_duties_enforced

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,
  r.project as project
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