Query: cloudfunction_function_no_disrupt_logging_permission

with role_with_disrupt_logging_permission as (
  select
    distinct name,
    project
  from
    gcp_iam_role,
    jsonb_array_elements_text(included_permissions) as p
  where
    not is_gcp_managed
    and p in (
      'logging.buckets.delete',
      'logging.buckets.update',
      'logging.logMetrics.delete',
      'logging.logMetrics.update',
      'logging.logs.delete',
      'logging.sinks.delete',
      'logging.sinks.update'
    )
),
policy_with_disrupt_logging_permission as (
  select
    distinct entity,
    project
  from
    gcp_iam_policy,
    jsonb_array_elements(bindings) as p,
    jsonb_array_elements_text(p -> 'members') as entity
  where
    p ->> 'role' in (
      'roles/logging.bucketWriter',
      'roles/logging.configWriter',
      'roles/logging.admin'
    )
    or p ->> 'role' in (
      select
        name
      from
        role_with_disrupt_logging_permission
    )
)
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 disrupt logging permission.'
    else f.title || ' restrict disrupt logging permission.'
  end as reason,
  f.project as project,
  f.project as project
from
  gcp_cloudfunctions_function as f
  left join policy_with_disrupt_logging_permission as b on f.project = b.project
  and b.entity = concat('serviceAccount:' || f.service_account_email);