Query: role_with_rbac_approve_certificate_signing_requests

with role_with_escalate as (
  select
    uid,
    count(*) as num
  from
    kubernetes_cluster_role,
    jsonb_array_elements(rules) rule
  where
    rule -> 'apiGroups' @> '["certificates.k8s.io"]'
    and (
      (
        rule -> 'resources' @> '["certificatesigningrequests/approval"]'
        and rule -> 'verbs' @> '["update", "patch"]'
      )
      or (
        rule -> 'resources' @> '["signers"]'
        and rule -> 'verbs' @> '["approve"]'
      )
    )
  group by
    uid
)
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 cluster role grant permissions to approve CertificateSigningRequests.'
    else name || ' does not contains any cluster role granting permissions to approve CertificateSigningRequests.'
  end as reason,
  name as role_name,
  coalesce(context_name, '') as context_name,
  source_type,
  coalesce(path || ':' || start_line || '-' || end_line, '') as path
from
  kubernetes_cluster_role as r
  left join role_with_escalate as e on e.uid = r.uid;