Table: gcp_sql_instances

SELECT
  DISTINCT
  gsi.name AS resource_id,
  'Ensure that Cloud SQL database instances are not open to the world (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND gsisican->>'value' = '0.0.0.0/0'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi,
  jsonb_array_elements(gsi.settings->'ipConfiguration'->'authorizedNetworks')
    AS gsisican;

SELECT
  DISTINCT
  gsi.name AS resource_id,
  'Ensure that Cloud SQL database instances do not have public IPs (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND gsiia->>'type' = 'PRIMARY'
  OR gsi.backend_type != 'SECOND_GEN'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi, jsonb_array_elements(gsi.ip_addresses) AS gsiia;

SELECT
  gsi.name AS resource_id,
  'Ensure that the Cloud SQL database instance requires all incoming connections to use SSL (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND (gsi.settings->'ipConfiguration'->>'requireSsl')::BOOL = false
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi;

SELECT
  gsi.name AS resource_id,
  'Ensure that Cloud SQL database instances are configured with automated backups (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND (gsi.settings->'backupConfiguration'->>'enabled')::BOOL = false
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi;

SELECT
  gsi.name AS resource_id,
  'Ensure that the "local_infile" database flag for a Cloud SQL Mysql instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'MYSQL%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'local_infile';

SELECT
  gsi.name AS resource_id,
  'Ensure "skip_show_database" database flag for Cloud SQL Mysql instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'MYSQL%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'skip_show_database';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_checkpoints" database flag for Cloud SQL PostgreSQL instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_checkpoints';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_connections" database flag for Cloud SQL PostgreSQL instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_connections';

SELECT
  gsi.name AS resource_id,
  'Ensure that the log_disconnections" database flag for Cloud SQL PostgreSQL instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_disconnections';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_duration" database flag for Cloud SQL PostgreSQL instance is set to "on" (Manual)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_duration';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_error_verbosity" database flag for Cloud SQL PostgreSQL instance is set to "DEFAULT" or stricter (Manual)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' NOT IN ('default', 'terse'))
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_error_verbosity';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_executor_stats" database flag for Cloud SQL PostgreSQL instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_executor_stats';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_hostname" database flag for Cloud SQL PostgreSQL instance is set appropriately (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_hostname';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_lock_waits" database flag for Cloud SQL PostgreSQL instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_lock_waits';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_min_duration_statement" database flag for Cloud SQL PostgreSQL instance is set to "-1" (disabled) (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != '-1')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_min_duration_statement';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_min_messages" database flag for Cloud SQL PostgreSQL instance is set appropriately (Manual)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND (
      (f->>'value') IS NULL
      OR f->>'value' NOT IN ('error', 'log', 'fatal', 'panic')
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_min_error_statement';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_parser_stats" database flag for Cloud SQL PostgreSQL instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_parser_stats';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_planner_stats" database flag for Cloud SQL PostgreSQL instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_planner_stats';

SELECT
  gsi.name AS resource_id,
  'Ensure "log_statement_stats" database flag for Cloud SQL PostgreSQL instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_statement_stats';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "log_temp_files" database flag for Cloud SQL PostgreSQL instance is set to "0" (on) (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'POSTGRES%'
  AND ((f->>'value') IS NULL OR f->>'value' != '0')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'log_temp_files';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "contained database authentication" database flag for Cloud SQL on the SQL Server instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'contained database authentication';

SELECT
  gsi.name AS resource_id,
  'Ensure that the "cross db ownership chaining" database flag for Cloud SQL SQL Server instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'cross db ownership chaining';

SELECT
  gsi.name AS resource_id,
  'Ensure "external scripts enabled" database flag for Cloud SQL SQL Server instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'external scripts enabled';

SELECT
  gsi.name AS resource_id,
  'Ensure "remote access" database flag for Cloud SQL SQL Server instance is set to "off" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'off')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'remote access';

-- In the original document in CIS GCP v1.2.0, it describes the configuration should be 'off', but it is a typo.
-- This constraint has been updated on CIS GCP v1.3.0, this flag should be 'on'.
 
SELECT
  gsi.name AS resource_id,
  'Ensure "3625 (trace flag)" database flag for Cloud SQL SQL Server instance is set to "on" (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%'
  AND ((f->>'value') IS NULL OR f->>'value' != 'on')
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = '3625';

SELECT
  gsi.name AS resource_id,
  'Ensure "user connections" database flag for Cloud SQL SQL Server instance is set as appropriate (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%' AND (f->>'value') IS NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'user connections';

SELECT
  gsi.name AS resource_id,
  'Ensure "user options" database flag for Cloud SQL SQL Server instance is not configured (Automated)'
    AS title,
  gsi.project_id AS project_id,
  CASE
  WHEN gsi.database_version LIKE 'SQLSERVER%' AND (f->>'value') IS NOT NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  gcp_sql_instances AS gsi
  LEFT JOIN jsonb_array_elements(gsi.settings->'databaseFlags') AS f ON
      f->>'name' = 'user options';