Query: iam_roles_for_pubsub_topic

with iam_role as (
  select
    name
  from
    gcp_iam_role
  where
    project = split_part($1, '/', 6)
), pubsub_topic as (
  select
    self_link,
    iam_policy
  from
    gcp_pubsub_topic
  where
    project = split_part($1, '/', 6)
    and self_link = $1
)
select
  i.name as role_id
from
  iam_role i,
  pubsub_topic t,
  jsonb_array_elements(t.iam_policy->'bindings') as roles
where
  roles ->> 'role' = i.name;