Masking policy state not properly registered with using `<<-EOT EOT` notation

[dlouseiro]

Provider Version

0.51.0

Terraform Version

v1.3.6

Describe the bug

While defining masking policies in Snowflake we decided to store the masking expression in a properly formatted SQL file instead of always placing the masking expression as a one-liner in the configuration. Having a one-liner worked alright in the beginning, but as masking expressions started to grow it became a pain to review, so we placed the masking expression in a SQL file and used the terraform file function (ref) to load the masking expression from the SQL file.

With this approach, the definition of the masking expression is defined as something like:

<<-EOT
            CASE
              WHEN <some_expression>
                THEN <masked result>
              ELSE val
            END
        EOT

instead of the usual oneliner"CASE WHEN <some_expression> THEN <masked result> ELSE val END".

This is expected as it's the notation for terraform to handle multiline strings. Although, while using this approach terraform always considers that the masking policies need to be changed even when no code was changed, seeming that this notation is not properly registered in the terraform state.

Expected behavior

terraform plan/apply to only detect changes when code is actually changed.

Code samples and commands

Resource definition example:

resource "snowflake_masking_policy" "mask_varchar" {
  for_each           = snowflake_database.market
  name               = "MASK_VARCHAR"
  database           = each.value.name
  schema             = "PUBLIC"
  value_data_type    = "VARCHAR"
  masking_expression = file("./masking_policies/mask_varchar.sql")
  return_data_type   = "VARCHAR(16777216)"
  comment            = "Masking policy for `varchar` columns"
}

./masking_policies/mask_varchar.sql content:

CASE
  WHEN system$get_tag_on_current_column('masked_markets') IS NULL
    OR ARRAYS_OVERLAP(SPLIT(system$get_tag_on_current_column('masked_markets'), ','),
                      TO_ARRAY(system$get_tag_on_current_column('market')))
    THEN IFF(ARRAYS_OVERLAP(SPLIT(UPPER(system$get_tag_on_current_column('public.authorized_roles')), ','),
                            PARSE_JSON(current_available_roles())),
             val, SHA2(val))
  ELSE val
END

terraform plan result returned every time, even when no code changes were made:

  # snowflake_masking_policy.mask_varchar["nl-prod"] will be updated in-place
  ~ resource "snowflake_masking_policy" "mask_varchar" {
        id                 = "PICNIC_NL_PROD|PUBLIC|MASK_VARCHAR"
      ~ masking_expression = <<-EOT
            CASE
              WHEN system$get_tag_on_current_column('masked_markets') IS NULL
                OR ARRAYS_OVERLAP(SPLIT(system$get_tag_on_current_column('masked_markets'), ','),
                                  TO_ARRAY(system$get_tag_on_current_column('market')))
                THEN IFF(ARRAYS_OVERLAP(SPLIT(UPPER(system$get_tag_on_current_column('public.authorized_roles')), ','),
                                        PARSE_JSON(current_available_roles())),
                         val, SHA2(val))
              ELSE val
            END
        EOT
        name               = "MASK_VARCHAR"
        # (6 unchanged attributes hidden)
    }

Result of terraform state show 'snowflake_masking_policy.mask_varchar["nl-prod"]'

# snowflake_masking_policy.mask_varchar["nl-prod"]:
resource "snowflake_masking_policy" "mask_varchar" {
    comment            = "Masking policy for `varchar` columns"
    database           = "PICNIC_NL_PROD"
    id                 = "PICNIC_NL_PROD|PUBLIC|MASK_VARCHAR"
    masking_expression = <<-EOT
        CASE
          WHEN system$get_tag_on_current_column('masked_markets') IS NULL
            OR ARRAYS_OVERLAP(SPLIT(system$get_tag_on_current_column('masked_markets'), ','),
                              TO_ARRAY(system$get_tag_on_current_column('market')))
            THEN IFF(ARRAYS_OVERLAP(SPLIT(UPPER(system$get_tag_on_current_column('public.authorized_roles')), ','),
                                    PARSE_JSON(current_available_roles())),
                     val, SHA2(val))
          ELSE val
        END
    EOT
    name               = "MASK_VARCHAR"
    qualified_name     = "\"PICNIC_NL_PROD\".\"PUBLIC\".\"MASK_VARCHAR\""
    return_data_type   = "VARCHAR(16777216)"
    schema             = "PUBLIC"
    value_data_type    = "VARCHAR"
}

Additional context

The behaviour is the same If I paste the masking expression using the EOT notation directly into the resource definition instead of using the file() function.

[dlouseiro]

We found a workaround that is to do a trim of \n after loading the SQL file content (using file() terraform function).

It appears that the problem does not come from the EOT notation or the fact that we're using a multiline string, but from the fact that the SQL file has a newline in the end of the file.

[kamilamarcinekpgs]

Duplicated https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/1097