Query: compute_instance_no_iam_write_permission

with role_with_iam_write_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 (
      'accessapproval.requests.approve',
      'accessapproval.requests.dismiss',
      'accessapproval.settings.delete',
      'accessapproval.settings.update',
      'accesscontextmanager.accessLevels.create',
      'accesscontextmanager.accessLevels.delete',
      'accesscontextmanager.accessLevels.replaceAll',
      'accesscontextmanager.accessLevels.update',
      'accesscontextmanager.accessPolicies.create',
      'accesscontextmanager.accessPolicies.delete',
      'accesscontextmanager.accessPolicies.setIamPolicy',
      'accesscontextmanager.accessPolicies.update',
      'accesscontextmanager.gcpUserAccessBindings.create',
      'accesscontextmanager.gcpUserAccessBindings.delete',
      'accesscontextmanager.gcpUserAccessBindings.update',
      'accesscontextmanager.policies.create',
      'accesscontextmanager.policies.delete',
      'accesscontextmanager.policies.setIamPolicy',
      'accesscontextmanager.policies.update',
      'iam.roles.create',
      'iam.roles.delete',
      'iam.roles.undelete',
      'iam.roles.update',
      'iam.serviceAccounts.getAccessToken',
      'iam.serviceAccountKeys.create',
      'iam.serviceAccountKeys.delete',
      'iam.serviceAccounts.create',
      'iam.serviceAccounts.delete',
      'iam.serviceAccounts.disable',
      'iam.serviceAccounts.enable',
      'iam.serviceAccounts.setIamPolicy',
      'iam.serviceAccounts.undelete',
      'iam.serviceAccounts.update',
      'iam.serviceAccounts.implicitDelegation',
      'iam.serviceAccounts.signBlob',
      'iam.serviceAccounts.signJwt',
      'iam.serviceAccounts.actAs',
      'compute.backendServices.setIamPolicy',
      'compute.disks.removeResourcePolicies',
      'compute.disks.setIamPolicy',
      'compute.firewallPolicies.setIamPolicy',
      'compute.globalOperations.setIamPolicy',
      'compute.images.setIamPolicy',
      'compute.instanceTemplates.setIamPolicy',
      'compute.instances.removeResourcePolicies',
      'compute.instances.setIamPolicy',
      'compute.instances.setServiceAccount',
      'compute.machineImages.setIamPolicy',
      'compute.maintenancePolicies.setIamPolicy',
      'compute.snapshots.setIamPolicy'
    )
),
policy_with_iam_write_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 (
      select
        name
      from
        role_with_iam_write_permission
    )
),
compute_instance_with_iam_write_permission as (
  select
    distinct self_link
  from
    gcp_compute_instance as i,
    jsonb_array_elements(service_accounts) as e
    left join policy_with_iam_write_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 IAM write permission.'
    else i.title || ' restrict IAM write permission'
  end as reason,
  location as location,
  project as project
from
  gcp_compute_instance as i
  left join compute_instance_with_iam_write_permission as p on p.self_link = i.self_link;