Table: gcp_compute_projects

This table shows data for GCP Compute Projects.

The primary key for this table is self_link.

Columns

Name	Type
_cq_id	`uuid`
_cq_parent_id	`uuid`
project_id	`utf8`
common_instance_metadata	`json`
creation_timestamp	`utf8`
default_network_tier	`utf8`
default_service_account	`utf8`
description	`utf8`
enabled_features	`list<item: utf8, nullable>`
id	`int64`
kind	`utf8`
name	`utf8`
quotas	`json`
self_link (PK)	`utf8`
usage_export_location	`json`
vm_dns_setting	`utf8`
xpn_project_status	`utf8`

Example Queries

These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.

Ensure that instances are not configured to use the default service account (Automated)

SELECT
  DISTINCT
  gci.name AS resource_id,
  'Ensure that instances are not configured to use the default service account (Automated)'
    AS title,
  gci.project_id AS project_id,
  CASE
  WHEN gci.name NOT LIKE 'gke-'
  AND gcisa->>'email'
    = (
        SELECT
          default_service_account
        FROM
          gcp_compute_projects
        WHERE
          project_id = gci.project_id
      )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances AS gci,
  jsonb_array_elements(gci.service_accounts) AS gcisa;

Ensure that instances are not configured to use the default service account with full access to all Cloud APIs (Automated)

SELECT
  DISTINCT
  gci.name AS resource_id,
  'Ensure that instances are not configured to use the default service account with full access to all Cloud APIs (Automated)'
    AS title,
  gci.project_id AS project_id,
  CASE
  WHEN gcisa->>'email'
  = (
      SELECT
        default_service_account
      FROM
        gcp_compute_projects
      WHERE
        project_id = gci.project_id
    )
  AND ARRAY['https://www.googleapis.com/auth/cloud-platform']
    <@ ARRAY (SELECT jsonb_array_elements_text(gcisa->'scopes'))
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances AS gci,
  jsonb_array_elements(gci.service_accounts) AS gcisa;

Ensure oslogin is enabled for a Project (Automated)

SELECT
  name AS resource_id,
  'Ensure oslogin is enabled for a Project (Automated)' AS title,
  project_id AS project_id,
  CASE
  WHEN (cimd->>'key') IS NULL
  OR NOT (cimd->>'value' = ANY ('{1,true,True,TRUE,y,yes}'))
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_projects
  LEFT JOIN jsonb_array_elements(common_instance_metadata->'items') AS cimd ON
      cimd->>'key' = 'enable-oslogin';