Query: compute_instance_no_database_write_permission

with role_with_deployments_manager_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 (
      'cloudsql.databases.update',
      'cloudsql.instances.update',
      'datastore.databases.update',
      'datastore.entities.update',
      'datastore.indexes.update',
      'spanner.databases.update',
      'spanner.databases.write',
      'spanner.instances.update',
      'bigtable.clusters.update',
      'bigtable.instances.update',
      'bigtable.tables.update',
      'redis.instances.update',
      'memcache.instances.update',
      'datamigration.migrationjobs.update',
      'datamigration.connectionprofiles.update',
      'datamigration.conversionworkspaces.update',
      'alloydb.clusters.update',
      'alloydb.instances.update',
      'gcp.redisenterprise.com-databases.update',
      'gcp.redisenterprise.com-subscriptions.update'
    )
),
policy_with_deployments_manager_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/datastore.indexAdmin',
      'roles/datastore.owner',
      'roles/datastore.user',
      'roles/alloydb.admin',
      'roles/bigtable.admin',
      'roles/datamigration.admin',
      'roles/datamigration.serviceAgent',
      'roles/memcache.admin',
      'roles/redis.admin',
      'roles/redisenterprisecloud.admin',
      'roles/spanner.admin',
      'roles/spanner.databaseAdmin'
    )
    or p ->> 'role' in (
      select
        name
      from
        role_with_deployments_manager_permission
    )
),
compute_instance_with_deployments_manage_permission as (
  select
    distinct self_link
  from
    gcp_compute_instance as i,
    jsonb_array_elements(service_accounts) as e
    left join policy_with_deployments_manager_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 database write permission.'
    else i.title || ' restrict database write permission.'
  end as reason,
  location as location,
  project as project
from
  gcp_compute_instance as i
  left join compute_instance_with_deployments_manage_permission as p on p.self_link = i.self_link;