Query: iam_member_roles_for_iam_service_account

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