Query: service_accounts_for_rbac

with rbac_role_input as (
  select
    distinct role.uid as uid
  from
    kubernetes_role_binding as b,
    kubernetes_role as role,
    jsonb_array_elements(rules) as r,
    jsonb_array_elements_text(r -> 'resources') as re,
    jsonb_array_elements_text(r -> 'verbs') as v
  where
    role.name = b.role_name
    and b.context_name = role.context_name
    and (
      v in (
        select
          unnest (string_to_array($1, ',') :: text [ ])
      )
      or v = '*'
    )
    and (
      re in (
        select
          unnest (string_to_array($2, ',') :: text [ ])
      )
      or re = '*'
    )
    and b.context_name in (
      select
        unnest (string_to_array($3, ',') :: text [ ])
    )
  union
  select
    distinct role.uid as uid
  from
    kubernetes_cluster_role_binding as b,
    kubernetes_cluster_role as role,
    jsonb_array_elements(rules) as r,
    jsonb_array_elements_text(r -> 'resources') as re,
    jsonb_array_elements_text(r -> 'verbs') as v
  where
    role.name = b.role_name
    and b.context_name = role.context_name
    and (
      v in (
        select
          unnest (string_to_array($1, ',') :: text [ ])
      )
      or v = '*'
    )
    and (
      re in (
        select
          unnest (string_to_array($2, ',') :: text [ ])
      )
      or re = '*'
    )
    and b.context_name in (
      select
        unnest (string_to_array($3, ',') :: text [ ])
    )
)
select
  a.uid as uid
from
  kubernetes_service_account as a,
  kubernetes_cluster_role as r,
  kubernetes_cluster_role_binding as b,
  jsonb_array_elements(subjects) as s
where
  b.role_name = r.name
  and s ->> 'kind' = 'ServiceAccount'
  and s ->> 'name' = a.name
  and a.context_name = r.context_name
  and r.uid in (
    select
      uid
    from
      rbac_role_input
  )
union
select
  a.uid as uid
from
  kubernetes_service_account as a,
  kubernetes_role as r,
  kubernetes_role_binding as b,
  jsonb_array_elements(subjects) as s
where
  b.role_name = r.name
  and s ->> 'kind' = 'ServiceAccount'
  and s ->> 'name' = a.name
  and a.context_name = r.context_name
  and r.uid in (
    select
      uid
    from
      rbac_role_input
  );