turbot/kubernetes_insights

Query: service_accounts_for_rbac

Usage

powerpipe query kubernetes_insights.query.service_accounts_for_rbac

SQL

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);

Params

ArgsNameDefaultDescriptionVariable
$1verb
    $2resource
      $3cluster_context