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 = $1;