Query: cloudfunction_function_restricted_permission

with unapproved_bindings as (
  select
    project,
    p,
    entity
  from
    gcp_iam_policy,
    jsonb_array_elements(bindings) as p,
    jsonb_array_elements_text(p -> 'members') as entity
  where
    p ->> 'role' in ('roles/editor','roles/owner')
)
select
  f.project as resource,
  case
    when f.service_account_email is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when f.service_account_email is not null then f.title || ' allow roles/editor or roles/owner permission.'
    else f.title || ' restrict roles/editor and roles/owner permision permission.'
  end as reason
  , f.project as project
  , f.project as project
from
  gcp_cloudfunctions_function as f
  left join unapproved_bindings as b on f.project = b.project and b.entity = concat('serviceAccount:' || f.service_account_email);