Query: kms_key_users_limited_to_3

with public_keys as (
  select
    distinct self_link
  from
    gcp_kms_key,
    jsonb_array_elements(iam_policy -> 'bindings') as b
  where
    b -> 'members' ?| array['allAuthenticatedUsers', 'allUsers']
), key_members_count as (
   select
    distinct self_link,
    jsonb_array_length(b -> 'members') as members_count
  from
    gcp_kms_key,
    jsonb_array_elements(iam_policy -> 'bindings') as b
)
select
  k.self_link as resource,
  case
    when p.self_link is not null then 'alarm'
    when c.members_count > 3 then 'alarm'
    else 'ok'
  end as status,
  case
    when p.self_link is not null then title || ' in ' || k.key_ring_name || ' key ring  publicly accessible.'
    when c.members_count is null then title || ' has no user.'
    else title || ' has ' || (c.members_count) || ' user(s).'
  end as reason
  
  , location as location, project as project
from
  gcp_kms_key k
  left join public_keys p on k.self_link = p.self_link
  left join key_members_count as c on c.self_link = k.self_link;