--- policy: /ce/ca/google/sql/instance-iam-database-authentication logic: /ce/ca/google/sql/instance-iam-database-authentication/prod.logic.yaml executionTime: 2026-05-26T22:48:12.215347582Z generationMs: 74 executionMs: 758 rows: - id: a8Eiam0 match: true status: expected: DISAPPEARED actual: DISAPPEARED conditionIndex: expected: 99 actual: 99 conditionText: expected: isDisappeared(CA10__disappearanceTime__c) actual: isDisappeared(CA10__disappearanceTime__c) runtimeError: {} - id: a8Eiam1 match: true status: expected: INAPPLICABLE actual: INAPPLICABLE conditionIndex: expected: 199 actual: 199 conditionText: expected: not(extract('CA10__databaseVersion__c').contains('MYSQL') || extract('CA10__databaseVersion__c').contains('POSTGRES')) actual: not(extract('CA10__databaseVersion__c').contains('MYSQL') || extract('CA10__databaseVersion__c').contains('POSTGRES')) runtimeError: {} - id: a8Eiam2 match: true status: expected: COMPLIANT actual: COMPLIANT conditionIndex: expected: 299 actual: 299 conditionText: expected: extract('CA10__databaseVersion__c').contains('POSTGRES') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c') == 'on' actual: extract('CA10__databaseVersion__c').contains('POSTGRES') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c') == 'on' runtimeError: {} - id: a8Eiam3 match: true status: expected: COMPLIANT actual: COMPLIANT conditionIndex: expected: 399 actual: 399 conditionText: expected: extract('CA10__databaseVersion__c').contains('MYSQL') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c') == 'on' actual: extract('CA10__databaseVersion__c').contains('MYSQL') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c') == 'on' runtimeError: {} - id: a8Eiam4 match: true status: expected: INCOMPLIANT actual: INCOMPLIANT conditionIndex: expected: 400 actual: 400 conditionText: expected: otherwise actual: otherwise runtimeError: {} - id: a8Eiam5 match: true status: expected: UNDETERMINED actual: UNDETERMINED conditionIndex: expected: 302 actual: 302 conditionText: expected: CA10__databaseFlagsJson__c.asJson().isEmpty() actual: CA10__databaseFlagsJson__c.asJson().isEmpty() runtimeError: {} usedFiles: - path: /ce/ca/google/sql/instance-iam-database-authentication/policy.yaml md5Hash: 8021796E0E5E7C05E76066002084F55D content: | --- names: full: "Google Cloud SQL Instance IAM Database Authentication is not enabled" contextual: "Cloud SQL Instance IAM Database Authentication is not enabled" description: "Enable IAM database authentication for Cloud SQL for MySQL and PostgreSQL instances to reduce reliance on static database passwords." type: COMPLIANCE_POLICY categories: - "SECURITY" frameworkMappings: - /frameworks/cis-gcp-v5.0.0/06/06 - /frameworks/cloudaware/resource-security/secure-access - path: /ce/ca/google/sql/instance-iam-database-authentication/prod.logic.yaml md5Hash: F39AC18E91D8147ED41565B35CA939A8 content: | --- inputType: "CA10__CaGoogleSqlInstance__c" testData: - file: test-data.json importExtracts: - file: /types/CA10__CaGoogleSqlInstance__c/object.extracts.yaml conditions: - status: "INAPPLICABLE" currentStateMessage: "IAM database authentication is applicable only to Cloud SQL for MySQL and PostgreSQL instances." check: NOT: arg: OR: args: - CONTAINS: arg: EXTRACT: "CA10__databaseVersion__c" search: TEXT: "MYSQL" - CONTAINS: arg: EXTRACT: "CA10__databaseVersion__c" search: TEXT: "POSTGRES" - status: "COMPLIANT" currentStateMessage: "IAM database authentication is enabled for this Cloud SQL PostgreSQL instance." check: AND: args: - CONTAINS: arg: EXTRACT: "CA10__databaseVersion__c" search: TEXT: "POSTGRES" - IS_EQUAL: left: EXTRACT: "caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c" right: TEXT: "on" - status: "COMPLIANT" currentStateMessage: "IAM database authentication is enabled for this Cloud SQL MySQL instance." check: AND: args: - CONTAINS: arg: EXTRACT: "CA10__databaseVersion__c" search: TEXT: "MYSQL" - IS_EQUAL: left: EXTRACT: "caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c" right: TEXT: "on" otherwise: status: "INCOMPLIANT" currentStateMessage: "IAM database authentication is not enabled for this Cloud SQL instance." remediationMessage: "Enable IAM database authentication for the Cloud SQL instance." - path: /ce/ca/google/sql/instance-iam-database-authentication/test-data.json md5Hash: 884530A53EA909B3E6422632F8595928 content: | [ { "expectedResult": { "status": "DISAPPEARED", "conditionIndex": "99", "conditionText": "isDisappeared(CA10__disappearanceTime__c)", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam0", "CA10__disappearanceTime__c": "2026-01-01T00:00:00Z", "CA10__databaseVersion__c": "POSTGRES_14", "CA10__databaseFlagsJson__c": "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"on\"}]" }, { "expectedResult": { "status": "INAPPLICABLE", "conditionIndex": "199", "conditionText": "not(extract('CA10__databaseVersion__c').contains('MYSQL') || extract('CA10__databaseVersion__c').contains('POSTGRES'))", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam1", "CA10__disappearanceTime__c": null, "CA10__databaseVersion__c": "SQLSERVER_2019_STANDARD", "CA10__databaseFlagsJson__c": "[]" }, { "expectedResult": { "status": "COMPLIANT", "conditionIndex": "299", "conditionText": "extract('CA10__databaseVersion__c').contains('POSTGRES') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c') == 'on'", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam2", "CA10__disappearanceTime__c": null, "CA10__databaseVersion__c": "POSTGRES_16", "CA10__databaseFlagsJson__c": "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"on\"}]" }, { "expectedResult": { "status": "COMPLIANT", "conditionIndex": "399", "conditionText": "extract('CA10__databaseVersion__c').contains('MYSQL') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c') == 'on'", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam3", "CA10__disappearanceTime__c": null, "CA10__databaseVersion__c": "MYSQL_8_0", "CA10__databaseFlagsJson__c": "[{\"name\":\"cloudsql_iam_authentication\",\"value\":\"on\"}]" }, { "expectedResult": { "status": "INCOMPLIANT", "conditionIndex": "400", "conditionText": "otherwise", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam4", "CA10__disappearanceTime__c": null, "CA10__databaseVersion__c": "POSTGRES_14", "CA10__databaseFlagsJson__c": "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"off\"}]" }, { "expectedResult": { "status": "UNDETERMINED", "conditionIndex": "302", "conditionText": "CA10__databaseFlagsJson__c.asJson().isEmpty()", "runtimeError": null }, "context": { "snapshotTime": "2026-05-13T00:00:00Z" }, "Id": "a8Eiam5", "CA10__disappearanceTime__c": null, "CA10__databaseVersion__c": "MYSQL_8_0", "CA10__databaseFlagsJson__c": "" } ] - path: /types/CA10__CaGoogleSqlInstance__c/object.extracts.yaml md5Hash: D357E312FE3FECE94E57ECC9C416D2B4 content: "---\nextracts:\n - name: \"CA10__databaseVersion__c\"\n value: \n\ \ FIELD:\n path: \"CA10__databaseVersion__c\"\n undeterminedIf:\n\ \ isEmpty: \"Database version should not be empty\"\n - name: \"\ CA10__ipv4Enabled__c\"\n value: \n FIELD:\n path: \"CA10__ipv4Enabled__c\"\ \n undeterminedIf:\n isEmpty: \"Ipv4Enabled should not be empty\"\ \n - name: \"caJsonFrom__databaseFlagsJson__c\"\n value: \n JSON_FROM:\n\ \ arg:\n FIELD:\n path: CA10__databaseFlagsJson__c\n\ \ returnType: BYTES\n undeterminedIf:\n isInvalid:\ \ \"Database Flags JSON is invalid.\"\n isEmpty: \"Database Flags JSON\ \ is empty.\"\n - name: \"caJsonText__databaseFlagsSkipShowDatabaseFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'skip_show_database'].value[] |\ \ [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsLocalInfileFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'local_infile'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsLogErrorVerbosityFlagValue__c\"\n \ \ value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_error_verbosity'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsLogConnectionsFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_connections'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsLogDisconnectionsFlagValue__c\"\n \ \ value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_disconnections'].value[] |\ \ [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsLogStatementFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_statement'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsLogMinMessagesFlagValue__c\"\n value:\ \ \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_min_messages'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsLogMinErrorStatementFlagValue__c\"\n\ \ value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_min_error_statement'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsLogMinDurationStatementFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_min_duration_statement'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsCloudsqlEnablePgauditFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'cloudsql.enable_pgaudit'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c\"\ \n value:\n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'cloudsql.iam_authentication'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c\"\ \n value:\n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'cloudsql_iam_authentication'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsExternalScriptsEnabledFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'external scripts enabled'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsCrossDbOwnershipChainingFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'cross db ownership chaining'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n\n - name: \"caJsonText__databaseFlagsUserConnectionsFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'user connections'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n\n - name: \"caJsonText__databaseFlagsUserOptionsFlagValue__c\"\n value:\ \ \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'user options'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsRemoteAccessFlagValue__c\"\n value:\ \ \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'remote access'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlags3625FlagValue__c\"\n value: \n \ \ JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == '3625'].value[] | [0])\"\n \ \ undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsContainedDatabaseAuthenticationFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'contained database authentication'].value[]\ \ | [0])\"\n undeterminedIf:\n evaluationError: \"The JSON query\ \ has failed.\"\n resultTypeMismatch: \"The JSON query did not return\ \ text type.\"\n - name: \"caJsonText__databaseFlagsLogCheckpointsFlagValue__c\"\ \n value: \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_checkpoints'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"caJsonText__databaseFlagsLogTempFilesFlagValue__c\"\n value:\ \ \n JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__databaseFlagsJson__c\"\ \n expression: \"to_string([?name == 'log_temp_files'].value[] | [0])\"\ \n undeterminedIf:\n evaluationError: \"The JSON query has failed.\"\ \n resultTypeMismatch: \"The JSON query did not return text type.\"\ \n - name: \"CA10__authorizedNetworksJson__c\"\n value: \n FIELD:\n\ \ path: \"CA10__authorizedNetworksJson__c\"\n returnType: BYTES\n\ \ - name: \"caJsonFrom__authorizedNetworksJson__c\"\n value: \n JSON_FROM:\n\ \ arg: \n EXTRACT: \"CA10__authorizedNetworksJson__c\"\n \ \ undeterminedIf:\n isInvalid: \"Authorized Networks JSON is invalid.\"\ \ \n - name: \"caJsonText__authorizedNetworksJsonValue__c\"\n value: \n\ \ JSON_QUERY_TEXT:\n arg:\n EXTRACT: \"caJsonFrom__authorizedNetworksJson__c\"\ \n expression: \"to_string([].value)\"\n undeterminedIf:\n \ \ evaluationError: \"The JSON query was failed.\"\n resultTypeMismatch:\ \ \"The JSON query did not return a text.\"\n - name: \"CA10__instanceType__c\"\ \n value: \n FIELD:\n path: \"CA10__instanceType__c\"\n \ \ undeterminedIf:\n isEmpty: \"Instance type should not be empty\"\ \n - name: \"CA10__backendType__c\"\n value: \n FIELD:\n path:\ \ \"CA10__backendType__c\"\n undeterminedIf:\n isEmpty: \"Backend\ \ type should not be empty\"\n - name: \"CA10__backupEnabled__c\"\n value:\ \ \n FIELD:\n path: \"CA10__backupEnabled__c\"\n undeterminedIf:\n\ \ isEmpty: \"Backup enabled should not be empty\"\n - name: \"CA10__backupStartTime__c\"\ \n value: \n FIELD:\n path: \"CA10__backupStartTime__c\"\n -\ \ name: \"CA10__sslMode__c\"\n value: \n FIELD:\n path: \"CA10__sslMode__c\"\ \n - name: \"CA10__createTime__c\"\n value: \n FIELD:\n path:\ \ \"CA10__createTime__c\"\n - name: \"CA10__state__c\"\n value: \n \ \ FIELD:\n path: \"CA10__state__c\"\n - name: \"CA10__cpuUtilizationAvg30Day__c\"\ \n value: \n FIELD:\n path: \"CA10__cpuUtilizationAvg30Day__c\"\ \n - name: \"CA10__memoryUtilizationAvg30Day__c\"\n value: \n FIELD:\n\ \ path: \"CA10__memoryUtilizationAvg30Day__c\"\n - name: \"CA10__diskReadIoOpsRate30Day__c\"\ \n value: \n FIELD:\n path: \"CA10__diskReadIoOpsRate30Day__c\"\ \n - name: \"CA10__diskWriteIoOpsRate30Day__c\"\n value: \n FIELD:\n\ \ path: \"CA10__diskWriteIoOpsRate30Day__c\"\n - name: \"CA10__cloudSqlConnectionsAvg30Day__c\"\ \n value: \n FIELD:\n path: \"CA10__cloudSqlConnectionsAvg30Day__c\"\ \n# Text\n - name: \"CA10__regionName__c\"\n value: \n FIELD:\n \ \ path: \"CA10__regionName__c\"\n" script: |- CREATE TEMP FUNCTION mock_ExpectedResult() RETURNS ARRAY >> DETERMINISTIC LANGUAGE js AS r""" return [ { "Id" : "a8Eiam0", "expectedResult" : { "status" : "DISAPPEARED", "conditionIndex" : "99", "conditionText" : "isDisappeared(CA10__disappearanceTime__c)", "runtimeError" : null } }, { "Id" : "a8Eiam1", "expectedResult" : { "status" : "INAPPLICABLE", "conditionIndex" : "199", "conditionText" : "not(extract('CA10__databaseVersion__c').contains('MYSQL') || extract('CA10__databaseVersion__c').contains('POSTGRES'))", "runtimeError" : null } }, { "Id" : "a8Eiam2", "expectedResult" : { "status" : "COMPLIANT", "conditionIndex" : "299", "conditionText" : "extract('CA10__databaseVersion__c').contains('POSTGRES') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c') == 'on'", "runtimeError" : null } }, { "Id" : "a8Eiam3", "expectedResult" : { "status" : "COMPLIANT", "conditionIndex" : "399", "conditionText" : "extract('CA10__databaseVersion__c').contains('MYSQL') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c') == 'on'", "runtimeError" : null } }, { "Id" : "a8Eiam4", "expectedResult" : { "status" : "INCOMPLIANT", "conditionIndex" : "400", "conditionText" : "otherwise", "runtimeError" : null } }, { "Id" : "a8Eiam5", "expectedResult" : { "status" : "UNDETERMINED", "conditionIndex" : "302", "conditionText" : "CA10__databaseFlagsJson__c.asJson().isEmpty()", "runtimeError" : null } } ]; """; CREATE TEMP FUNCTION mock_CA10__CaGoogleSqlInstance__c() RETURNS ARRAY >> DETERMINISTIC LANGUAGE js AS r""" return [ { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__disappearanceTime__c" : new Date("2026-01-01T00:00:00Z"), "CA10__databaseVersion__c" : "POSTGRES_14", "CA10__databaseFlagsJson__c" : "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"on\"}]", "Id" : "a8Eiam0" }, { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__databaseVersion__c" : "SQLSERVER_2019_STANDARD", "CA10__databaseFlagsJson__c" : "[]", "Id" : "a8Eiam1" }, { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__databaseVersion__c" : "POSTGRES_16", "CA10__databaseFlagsJson__c" : "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"on\"}]", "Id" : "a8Eiam2" }, { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__databaseVersion__c" : "MYSQL_8_0", "CA10__databaseFlagsJson__c" : "[{\"name\":\"cloudsql_iam_authentication\",\"value\":\"on\"}]", "Id" : "a8Eiam3" }, { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__databaseVersion__c" : "POSTGRES_14", "CA10__databaseFlagsJson__c" : "[{\"name\":\"cloudsql.iam_authentication\",\"value\":\"off\"}]", "Id" : "a8Eiam4" }, { "context" : { "snapshotTime" : new Date("2026-05-13T00:00:00Z") }, "CA10__databaseVersion__c" : "MYSQL_8_0", "CA10__databaseFlagsJson__c" : "", "Id" : "a8Eiam5" } ]; """; CREATE TEMP FUNCTION process_CA10__CaGoogleSqlInstance__c( obj STRUCT< CA10__disappearanceTime__c TIMESTAMP, CA10__databaseVersion__c STRING, CA10__databaseFlagsJson__c STRING, Id STRING >, snapshotTime TIMESTAMP ) RETURNS STRUCT DETERMINISTIC LANGUAGE js OPTIONS (library=['gs://compliance-platform-public/jmespath.min.js']) AS r""" var TextLib = new function () { this.normalize = function(arg) { return arg == null ? '' : arg.replace(/\s+/g, ' ').trim().toLowerCase(); }; this.isEmpty = function(arg) { return this.normalize(arg) == ''; }; this.isNotEmpty = function(arg) { return this.normalize(arg) != ''; }; this.equal = function(left, right) { return this.normalize(left) == this.normalize(right); }; this.notEqual = function(left, right) { return this.normalize(left) != this.normalize(right); }; this.startsWith = function(arg, substring) { return this.normalize(arg).startsWith(this.normalize(substring)); }; this.endsWith = function(arg, substring) { return this.normalize(arg).endsWith(this.normalize(substring)); }; this.contains = function(arg, substring) { return this.normalize(arg).includes(this.normalize(substring)); }; this.containsAll = function(arg, substrings) { if (substrings == null || substrings.length === 0) return false; let normalizedArg = this.normalize(arg); return substrings.every(sub => normalizedArg.includes(this.normalize(sub))); }; this.containsAny = function(arg, substrings) { if (substrings == null || substrings.length === 0) return false; let normalizedArg = this.normalize(arg); return substrings.some(sub => normalizedArg.includes(this.normalize(sub))); }; }(); var references1 = []; // condition[0], conditionIndex:[0..99] references1.push('Deleted From Google [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c); if (obj.CA10__disappearanceTime__c != null) { return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}; } // condition[1], conditionIndex:[100..199] function fieldChecked4() { if (TextLib.isEmpty(obj.CA10__databaseVersion__c)) { throw new Error("UNDETERMINED condition:101", {cause: {status: 'UNDETERMINED', conditionIndex: 101, conditionText: "CA10__databaseVersion__c.isEmpty()", currentStateMessage: "Database version should not be empty", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } return obj.CA10__databaseVersion__c; } function extract3() { if (!this.out) { this.out = fieldChecked4(); } return this.out; }; references1.push('Database Version [obj.CA10__databaseVersion__c]: ' + obj.CA10__databaseVersion__c); try { if (!(TextLib.contains(extract3.call(extract3), 'MYSQL') || TextLib.contains(extract3.call(extract3), 'POSTGRES'))) { return {status: 'INAPPLICABLE', conditionIndex: 199, conditionText: "not(extract('CA10__databaseVersion__c').contains('MYSQL') || extract('CA10__databaseVersion__c').contains('POSTGRES'))", currentStateMessage: "IAM database authentication is applicable only to Cloud SQL for MySQL and PostgreSQL instances.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}; } } catch (err) { if (err.cause && err.cause.status) { return err.cause; } else { throw err; } } // condition[2], conditionIndex:[200..299] function fieldChecked8() { if (TextLib.isEmpty(obj.CA10__databaseVersion__c)) { throw new Error("UNDETERMINED condition:201", {cause: {status: 'UNDETERMINED', conditionIndex: 201, conditionText: "CA10__databaseVersion__c.isEmpty()", currentStateMessage: "Database version should not be empty", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } return obj.CA10__databaseVersion__c; } function extract7() { if (!this.out) { this.out = fieldChecked8(); } return this.out; }; function jsonQueryChecked11() { var input = extract13.call(extract13); var out; try { out = jmespath.search(input, 'to_string([?name == \'cloudsql.iam_authentication\'].value[] | [0])'); if (out != null && typeof out != 'string') { throw new Error("UNDETERMINED condition:204", {cause: {status: 'UNDETERMINED', conditionIndex: 204, conditionText: "extract('caJsonFrom__databaseFlagsJson__c').jsonQueryText('to_string([?name == \\'cloudsql.iam_authentication\\'].value[] | [0])').isResultTypeMismatch()", currentStateMessage: "The JSON query did not return text type.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } } catch (e) { throw new Error("UNDETERMINED condition:205", {cause: {status: 'UNDETERMINED', conditionIndex: 205, conditionText: "extract('caJsonFrom__databaseFlagsJson__c').jsonQueryText('to_string([?name == \\'cloudsql.iam_authentication\\'].value[] | [0])').isEvaluationFailed()", currentStateMessage: "The JSON query has failed.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: e.message}}); } return out; } function jsonChecked14() { var input = obj.CA10__databaseFlagsJson__c; if (TextLib.isEmpty(input)) { throw new Error("UNDETERMINED condition:202", {cause: {status: 'UNDETERMINED', conditionIndex: 202, conditionText: "CA10__databaseFlagsJson__c.asJson().isEmpty()", currentStateMessage: "Database Flags JSON is empty.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } var out; try { out = JSON.parse(input); } catch (e) { throw new Error("UNDETERMINED condition:203", {cause: {status: 'UNDETERMINED', conditionIndex: 203, conditionText: "CA10__databaseFlagsJson__c.asJson().isInvalid()", currentStateMessage: "Database Flags JSON is invalid.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: e.message}}); } return out; } function extract13() { if (!this.out) { this.out = jsonChecked14(); } return this.out; }; function extract10() { if (!this.out) { this.out = jsonQueryChecked11(); } return this.out; }; references1.push('Database Flags JSON [obj.CA10__databaseFlagsJson__c]: ' + obj.CA10__databaseFlagsJson__c); try { if (TextLib.contains(extract7.call(extract7), 'POSTGRES') && TextLib.equal(extract10.call(extract10), 'on')) { return {status: 'COMPLIANT', conditionIndex: 299, conditionText: "extract('CA10__databaseVersion__c').contains('POSTGRES') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationFlagValue__c') == 'on'", currentStateMessage: "IAM database authentication is enabled for this Cloud SQL PostgreSQL instance.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}; } } catch (err) { if (err.cause && err.cause.status) { return err.cause; } else { throw err; } } // condition[3], conditionIndex:[300..399] function fieldChecked18() { if (TextLib.isEmpty(obj.CA10__databaseVersion__c)) { throw new Error("UNDETERMINED condition:301", {cause: {status: 'UNDETERMINED', conditionIndex: 301, conditionText: "CA10__databaseVersion__c.isEmpty()", currentStateMessage: "Database version should not be empty", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } return obj.CA10__databaseVersion__c; } function extract17() { if (!this.out) { this.out = fieldChecked18(); } return this.out; }; function jsonQueryChecked21() { var input = extract23.call(extract23); var out; try { out = jmespath.search(input, 'to_string([?name == \'cloudsql_iam_authentication\'].value[] | [0])'); if (out != null && typeof out != 'string') { throw new Error("UNDETERMINED condition:304", {cause: {status: 'UNDETERMINED', conditionIndex: 304, conditionText: "extract('caJsonFrom__databaseFlagsJson__c').jsonQueryText('to_string([?name == \\'cloudsql_iam_authentication\\'].value[] | [0])').isResultTypeMismatch()", currentStateMessage: "The JSON query did not return text type.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } } catch (e) { throw new Error("UNDETERMINED condition:305", {cause: {status: 'UNDETERMINED', conditionIndex: 305, conditionText: "extract('caJsonFrom__databaseFlagsJson__c').jsonQueryText('to_string([?name == \\'cloudsql_iam_authentication\\'].value[] | [0])').isEvaluationFailed()", currentStateMessage: "The JSON query has failed.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: e.message}}); } return out; } function jsonChecked24() { var input = obj.CA10__databaseFlagsJson__c; if (TextLib.isEmpty(input)) { throw new Error("UNDETERMINED condition:302", {cause: {status: 'UNDETERMINED', conditionIndex: 302, conditionText: "CA10__databaseFlagsJson__c.asJson().isEmpty()", currentStateMessage: "Database Flags JSON is empty.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}}); } var out; try { out = JSON.parse(input); } catch (e) { throw new Error("UNDETERMINED condition:303", {cause: {status: 'UNDETERMINED', conditionIndex: 303, conditionText: "CA10__databaseFlagsJson__c.asJson().isInvalid()", currentStateMessage: "Database Flags JSON is invalid.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: e.message}}); } return out; } function extract23() { if (!this.out) { this.out = jsonChecked24(); } return this.out; }; function extract20() { if (!this.out) { this.out = jsonQueryChecked21(); } return this.out; }; try { if (TextLib.contains(extract17.call(extract17), 'MYSQL') && TextLib.equal(extract20.call(extract20), 'on')) { return {status: 'COMPLIANT', conditionIndex: 399, conditionText: "extract('CA10__databaseVersion__c').contains('MYSQL') && extract('caJsonText__databaseFlagsCloudsqlIamAuthenticationMysqlFlagValue__c') == 'on'", currentStateMessage: "IAM database authentication is enabled for this Cloud SQL MySQL instance.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}; } } catch (err) { if (err.cause && err.cause.status) { return err.cause; } else { throw err; } } return {status: 'INCOMPLIANT', conditionIndex: 400, conditionText: "otherwise", currentStateMessage: "IAM database authentication is not enabled for this Cloud SQL instance.", currentStateReferences: references1.join('\n'), remediation: "Enable IAM database authentication for the Cloud SQL instance.", runtimeError: null}; """; SELECT expectedResult.Id as Id, IF ( IFNULL(expectedResult.expectedResult.status, '') = IFNULL(sObject.result.status, '') AND IFNULL(expectedResult.expectedResult.conditionIndex, -1) = IFNULL(sObject.result.conditionIndex, -1) AND IFNULL(expectedResult.expectedResult.conditionText, '') = IFNULL(sObject.result.conditionText, '') AND IFNULL(expectedResult.expectedResult.runtimeError, '') = IFNULL(sObject.result.runtimeError, ''), "MATCH", "FAIL" ) as match, expectedResult.expectedResult.status as expectedStatus, sObject.result.status as actualStatus, expectedResult.expectedResult.conditionIndex as expectedConditionIndex, sObject.result.conditionIndex as actualConditionIndex, expectedResult.expectedResult.conditionText as expectedConditionText, sObject.result.conditionText as actualConditionText, expectedResult.expectedResult.runtimeError as expectedRuntimeError, sObject.result.runtimeError as actualRuntimeError FROM UNNEST(mock_ExpectedResult()) expectedResult LEFT JOIN ( SELECT sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c, sObject.CA10__databaseVersion__c AS CA10__databaseVersion__c, sObject.CA10__databaseFlagsJson__c AS CA10__databaseFlagsJson__c, sObject.Id AS Id, process_CA10__CaGoogleSqlInstance__c( STRUCT( sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c, sObject.CA10__databaseVersion__c AS CA10__databaseVersion__c, sObject.CA10__databaseFlagsJson__c AS CA10__databaseFlagsJson__c, sObject.Id AS Id ), sObject.context.snapshotTime ) as result FROM UNNEST(mock_CA10__CaGoogleSqlInstance__c()) AS sObject ) sObject ON sObject.Id = expectedResult.Id;