Query: rbac_rule_analysis

select
  s ->> 'name' as "Principal",
  s ->> 'kind' as "Principal Kind",
  b.name as "Role Binding",
  role.name as "Role",
  v as "Verbs",
  re as "Resources",
  resource_name as "Resource Names"
from
  kubernetes_cluster_role_binding as b,
  kubernetes_cluster_role as role,
  kubernetes_service_account as a,
  jsonb_array_elements(subjects) as s,
  jsonb_array_elements(rules) as r,
  jsonb_array_elements_text(r -> 'resources') as re,
  jsonb_array_elements_text(r -> 'verbs') as v,
  jsonb_array_elements_text(coalesce(r -> 'resourceNames', '["*"]' :: jsonb)) as resource_name
where
  role.name = b.role_name
  and (
    s ->> 'kind' <> 'ServiceAccount'
    or s ->> 'name' in (
      select
        name
      from
        kubernetes_service_account
    )
  )
  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
  s ->> 'name' as "Principal",
  s ->> 'kind' as "Principal Kind",
  b.name as "Role Binding",
  role.name as "Role",
  v as "Verbs",
  re as "Resources",
  resource_name as "Resource Names"
from
  kubernetes_role_binding as b,
  kubernetes_role as role,
  kubernetes_service_account as a,
  jsonb_array_elements(subjects) as s,
  jsonb_array_elements(rules) as r,
  jsonb_array_elements_text(r -> 'resources') as re,
  jsonb_array_elements_text(r -> 'verbs') as v,
  jsonb_array_elements_text(coalesce(r -> 'resourceNames', '["*"]' :: jsonb)) as resource_name
where
  role.name = b.role_name
  and (
    s ->> 'kind' <> 'ServiceAccount'
    or s ->> 'name' in (
      select
        name
      from
        kubernetes_service_account
    )
  )
  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 [ ])
  )
order by
  1;