turbot/gcp_insights

Query: iam_member_roles_for_iam_service_account

Usage

powerpipe query gcp_insights.query.iam_member_roles_for_iam_service_account

SQL

with role_name as (
select
name,
role_name
from
gcp_iam_role,
split_part(name,'roles/',2) as role_name
)
select
rn.name as role_id
from
role_name as rn,
gcp_service_account as s,
jsonb_array_elements(iam_policy -> 'bindings') as b,
split_part(b ->> 'role','roles/',2) as r
where
rn.role_name = r
and s.name = split_part($1, '/', 1);