Table: gcp_compute_instances

SELECT
  name AS resource_id,
  'Ensure that IP forwarding is not enabled on Instances (Automated)' AS title,
  project_id AS project_id,
  CASE WHEN can_ip_forward = true THEN 'fail' ELSE 'pass' END AS status
FROM
  gcp_compute_instances;

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;

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;

SELECT
  DISTINCT
  gci.name AS resource_id,
  'Ensure that Compute instances do not have public IP addresses (Automated'
    AS title,
  gci.project_id AS project_id,
  CASE
  WHEN gci.name NOT LIKE 'gke-%'
  AND (
      (ac4->>'nat_i_p') IS NOT NULL
      OR ac4->>'nat_i_p' != ''
      OR (ac6->>'nat_i_p') IS NOT NULL
      OR ac6->>'nat_i_p' != ''
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances AS gci,
  jsonb_array_elements(gci.network_interfaces) AS ni
  LEFT JOIN jsonb_array_elements(ni->'access_configs') AS ac4 ON true
  LEFT JOIN jsonb_array_elements(ni->'ipv6_access_configs') AS ac6 ON true;

SELECT
  name AS resource_id,
  'Ensure Compute instances are launched with Shielded VM enabled (Automated)'
    AS title,
  project_id AS project_id,
  CASE
  WHEN (shielded_instance_config->>'enable_integrity_monitoring')::BOOL = false
  OR (shielded_instance_config->>'enable_vtpm')::BOOL = false
  OR (shielded_instance_config->>'enable_secure_boot')::BOOL = false
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances;

SELECT
  gci.name AS resource_id,
  'Ensure "Block Project-wide SSH keys" is enabled for VM instances (Automated)'
    AS title,
  gci.project_id AS project_id,
  CASE
  WHEN (gcmi->>'key') IS NULL
  OR NOT (gcmi->>'value' = ANY ('{1,true,True,TRUE,y,yes}'))
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances AS gci
  LEFT JOIN jsonb_array_elements(gci.metadata->'items') AS gcmi ON
      gcmi->>'key' = 'block-project-ssh-keys';

SELECT
  name AS resource_id,
  'Ensure that Compute instances have Confidential Computing enabled (Automated)'
    AS title,
  project_id AS project_id,
  CASE
  WHEN (confidential_instance_config->>'enable_confidential_compute')::BOOL
  = false
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances;

SELECT
  name AS resource_id,
  'Ensure "Enable connecting to serial ports" is not enabled for VM Instance (Automated)'
    AS title,
  project_id AS project_id,
  CASE
  WHEN gcmi->>'key' = 'serial-port-enable'
  AND gcmi->>'value' = ANY ('{1,true,True,TRUE,y,yes}')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_compute_instances AS gci,
  jsonb_array_elements(gci.metadata->'items') AS gcmi;