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