Query: role_with_bind_cluster_role_bindings

with role_with_escalate as (
  select
    uid,
    count(*) as num
  from
    kubernetes_cluster_role,
    jsonb_array_elements(rules) rule
  where
    rule -> 'apiGroups' @> '["rbac.authorization.k8s.io"]'
    and (
      rule -> 'resources' @> '["rolebindings"]'
      or rule -> 'resources' @> '["clusterrolebindings"]'
    )
    and rule -> 'verbs' @> '["bind"]'
  group by
    uid
  union
  select
    uid,
    count(*) as num
  from
    kubernetes_role,
    jsonb_array_elements(rules) rule
  where
    rule -> 'apiGroups' @> '["rbac.authorization.k8s.io"]'
    and (
      rule -> 'resources' @> '["rolebindings"]'
      or rule -> 'resources' @> '["clusterrolebindings"]'
    )
    and rule -> 'verbs' @> '["bind"]'
  group by
    uid
), union_role_and_cluster_role as (
  select
    uid,
    path,
    name,
    start_line,
    end_line,
    source_type,
    context_name,
    tags,
    _ctx
  from
    kubernetes_role
  union
  select
    uid,
    path,
    name,
    start_line,
    end_line,
    source_type,
    context_name,
    tags,
    _ctx
  from
    kubernetes_cluster_role
)
select
  coalesce(r.uid, concat(r.path, ':', r.start_line)) as resource,
  case
    when e.num > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when e.num > 0  then name || ' contains ' || e.num || ' RBAC bind role bindings or cluster role bindings permissions.'
    else name || ' does not contain any RBAC bind role bindings or cluster role bindings permissions.'
  end as reason,
  name as role_name
  
  , coalesce(context_name, '') as context_name, source_type, coalesce(path || ':' || start_line || '-' || end_line, '') as path
from
  union_role_and_cluster_role as r
  left join role_with_escalate as e on e.uid = r.uid;