Control: Compute Instances should restrict data destruction permission

powerpipe control run gcp_compliance.control.compute_instance_no_data_destruction_permission

powerpipe login
powerpipe control run gcp_compliance.control.compute_instance_no_data_destruction_permission --share

with role_with_data_destruction_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 ( 'compute.instances.delete', 'compute.disks.delete', 'compute.snapshots.delete', 'compute.images.delete', 'storage.buckets.delete', 'cloudsql.databases.delete', 'cloudsql.instances.delete', 'file.instances.delete')
  ), policy_with_data_destruction_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/cloudsql.admin', 'roles/cloudsql.instanceUser', 'roles/compute.admin' , 'roles/compute.instanceAdmin.v1')
    or p ->> 'role' in (select name from role_with_data_destruction_permission)
), compute_instance_with_data_destruction_permission as (
  select
    distinct self_link
  from
    gcp_compute_instance as i,
    jsonb_array_elements(service_accounts) as e
    left join policy_with_data_destruction_permission as b on b.entity = concat('serviceAccount:' || (e ->> 'email'))
  where
    b.entity is not null
)
select
  i.self_link as resource,
  case
    when p.self_link is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when p.self_link is not null then i.title || ' allow data destruction permission.'
    else i.title || ' restrict data destruction permission.'
  end as reason
  
  , location as location, project as project
from
  gcp_compute_instance as i
  left join compute_instance_with_data_destruction_permission as p on p.self_link = i.self_link;