snowflake_table_column_masking_policy_application keeps getting recreated at every plan

[patcky]

Terraform CLI and Provider Versions

Terraform version: 1.2
Provider version: was initially using 0.67.0 but also tested with the most recent version (0.77.0 atm) to see if the bug was fixed, but it remained the same.

Terraform Configuration

# _database/_modules/_masking_policy/masking_policy.tf

resource "snowflake_masking_policy" "policy" {
  name     = "MY_APP_${upper(var.schema)}_STRING_MASK"
  database = var.database
  schema   = var.schema
  signature {
    column {
      name = "VAL"
      type = "VARCHAR"
    }
  }
  return_data_type = "VARCHAR(16777216)"
  masking_expression = trimspace(<<-EOT
  case
    when current_role() in (
      '${join("',\n    '", var.sensitive_select_roles)}'
    ) then val
    else sha2(val)) # This was changed but it doesn't matter because it's working properly
  end
  EOT
  )
}

resource "snowflake_masking_policy_grant" "ownership" {
  database_name       = var.database
  schema_name         = var.schema
  masking_policy_name = snowflake_masking_policy.policy.name
  privilege           = "OWNERSHIP"
  roles               = var.masking_admins
}

output "masking_policy" {
  value = snowflake_masking_policy.policy.qualified_name
}

# _database/schema/schema.tf

resource "snowflake_schema" "my_schema" {
  database     = var.database_name
  name         = "MY_SCHEMA"
  comment      = "Schema for xxx"
  is_transient = var.transient
}

...

module "masking_policies" {
  source                 = "../../_modules/_masking_policy"
  database               = snowflake_schema.my_schema.database
  schema                 = snowflake_schema.my_schema.name
  masking_admins         = var.masking_admins
  sensitive_select_roles = var.sensitive_select_roles
}

...

module "my_app_tables" {
  source         = "../../_modules/_table"
  database       = snowflake_schema.games.database
  schema         = snowflake_schema.games.name
  masking_policy = module.masking_policies.masking_policy
  
  # This is to create a table for each namespace passed to the schema
  for_each = {
    for n in var.namespaces : n.namespace => n
  }

  namespace      = each.value.namespace
  selector_roles = each.value.selector_roles
}

# _database/_modules/_table/table.tf

resource "snowflake_table" "my_table" {
  database = local.database
  schema   = local.schema
  name     = local.namespace

  comment    = "A table containing xxx for ${local.namespace}."
  cluster_by = ["SERVER_TIMESTAMP::date"]

  column {
    name     = "COLUMN_1"
    type     = "VARCHAR(36)"
    nullable = true
    comment  = "Column 1"
  }

  column {
    name     = "USER_ID"
    type     = "VARCHAR(36)"
    nullable = false
    comment  = "User ID"
  }

  column {
    name     = "OTHER_COLUMN"
    type     = "TIMESTAMP_TZ(9)"
    nullable = true
    comment  = "Other column"
  }

  lifecycle {
    ignore_changes = [column["USER_ID"].masking_policy]
  }
}

resource "snowflake_table_column_masking_policy_application" "mask_user_id" {
  table          = snowflake_table.telemetry_table.qualified_name
  column         = "USER_ID"
  masking_policy = var.masking_policy
}

Expected Behavior

When applying the masking policies to the column in the tables, they should only be created once. Using the lifecycle argument should prevent terraform from changing it again in the table level (not in the resource itself, though).

Actual Behavior

After creating and applying the policies for the first time, they show up in the plan as if changed, although nothing was changed. If terraform apply is executed, the issue persists and it keeps pointing the snowflake_table_column_masking_policy_application as if something changed:

  # module.schema.module.my_app_tables["name_of_the_table"].snowflake_table.my_table will be updated in-place
  ~ resource "snowflake_table" "my_table" {
        id                  = "MY_APP_TEST|SCHEMA|NAME_OF_THE_TABLE"
        name                = "NAME_OF_THE_TABLE"
        # (8 unchanged attributes hidden)

      ~ column {
          - masking_policy = "MY_APP_TEST.SCHEMA.MY_APP_SCHEMA_STRING_MASK" -> null
            name           = "USER_ID"
            # (3 unchanged attributes hidden)
        }

        # (13 unchanged blocks hidden)
    }

  # module.schema.module.my_app_tables["name_of_the_table"].snowflake_table_column_masking_policy_application.mask_user_id must be replaced
-/+ resource "snowflake_table_column_masking_policy_application" "mask_user_id" {
      ~ id             = "\"MY_APP_TEST\".\"SCHEMA\".\"NAME_OF_THE_TABLE\".\"USER_ID\"" -> (known after apply)
      ~ masking_policy = "MY_APP_TEST.SCHEMA.MY_APP_SCHEMA_STRING_MASK" -> "\"MY_APP_TEST\".\"SCHEMA\".\"MY_APP_SCHEMA_STRING_MASK\"" # forces replacement
        # (2 unchanged attributes hidden)
    }

Plan: 1 to add, 1 to change, 1 to destroy.

Steps to Reproduce

1. Create masking policies and apply them to a column in a table, according to the provider documentation
2. Run terraform plan
3. Run terraform apply
4. Run terraform plan again

How much impact is this issue causing?

High

Logs

https://gist.github.com/patcky/4324cc01455c6ff817deeccc2e73590b

Additional Information

After some investigation and debugging, I found that the issue seems to be with the way the provider compares the snowflake_table_column_masking_policy_application returned by snowflake with the value saved in the state file.
The issue could be in this line, particularly in the function ParseFullyQualifiedObjectID, in strings.ReplaceAll(s, "\"", "") command. Currently, as it is, the command removes the double quotes but not the escape characters \ which could be the cause of the issue, but it requires further investigation.
I am attaching the relevant logs for debugging purposes.

[sfc-gh-asawicki]

Hey @patcky. Thanks for reporting the issue.

We are currently migrating the table resource to the new SDK implementation; we will revisit this problem after this migration happens (maybe it will be enough to solve the issue).

[yinxu0619]

Hi @sfc-gh-asawicki actually this issue also happens on both network_policy and snowflake_account_password_policy_attachment modules with the current latest v0.92.
below is a terraform plan output.

Terraform will perform the following actions:

  # snowflake_account_password_policy_attachment.account_password_policy_attachment_account_password_policy must be replaced
-/+ resource "snowflake_account_password_policy_attachment" "account_password_policy_attachment_account_password_policy" {
      ~ id              = "SNOWFLAKE_GOV|PUBLIC|ACCOUNT_LEVEL_PASSWORD_POLICY" -> (known after apply)
      ~ password_policy = "\"SNOWFLAKE_GOV\".\"PUBLIC\".\"ACCOUNT_LEVEL_PASSWORD_POLICY\"" -> "SNOWFLAKE_GOV.PUBLIC.ACCOUNT_LEVEL_PASSWORD_POLICY" # forces replacement
    }

  # snowflake_network_policy.network_policy_INTERNAL_TRAFFIC will be updated in-place
  ~ resource "snowflake_network_policy" "network_policy_INTERNAL_TRAFFIC" {
      ~ allowed_network_rule_list = [
          - "\"SNOWFLAKE_GOV\".\"PUBLIC\".\"INTERNAL_TRAFFIC\"",
          + "SNOWFLAKE_GOV.PUBLIC.INTERNAL_TRAFFIC",
        ]
        id                        = "INTERNAL_TRAFFIC"
        name                      = "INTERNAL_TRAFFIC"
        # (1 unchanged attribute hidden)
    }

  # snowflake_network_policy.network_policy_LOCAL_IP will be updated in-place
  ~ resource "snowflake_network_policy" "network_policy_LOCAL_IP" {
      ~ allowed_network_rule_list = [
          - "\"SNOWFLAKE_GOV\".\"PUBLIC\".\"LOCAL_IP\"",
          + "SNOWFLAKE_GOV.PUBLIC.LOCAL_IP",
        ]
        id                        = "LOCAL_IP"
        name                      = "LOCAL_IP"
        # (2 unchanged attributes hidden)
    }

Plan: 1 to add, 2 to change, 1 to destroy.

[sfc-gh-asawicki]

Hey @yinxu0619. Thanks for reaching out to us.

The problem you described is not directly connected with either of these issues. It's connected with how the identifiers are handled inside the provider. We are currently working on them as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#our-roadmap. Also, we are currently redesigning the network policy resource in (#2914). I will make sure the issue you've mentioned is covered. cc: @sfc-gh-jcieslak

[sfc-gh-jcieslak]

Hey @yinxu0619 👋
Network policies were refactored in 0.94.0 and shouldn't cause any issues regarding identifiers. The diffs were mostly connected to how the identifier is specified in the config/state vs returned by Snowflake. Snowflake has certain rules on returning quoted or non-quoted identifiers (sometimes they're partially quoted), which caused plan diffs that should be no longer visible in the later versions of the provider. We would appreciate feedback on that once you've upgraded to at least version 0.94.0.

Hey @patcky 👋
We should also take care of table and table-related resources soon with the same approach of suppressing such diffs. We're also planning to do the same for other resources, so they won't cause any quote-related diffs.

[AaronCoquet-Easypark]

Hi SFC folks,
I'm experiencing the same (Masking Policy) issue: Every plan is destroy&recreating all of my masking policies. I first encountered this on provider v0.90.0, but it persisted through to current (v0.96.0). Plan snippet:

 # snowflake_table_column_masking_policy_application.$WHATEVER[$KEY] must be replaced
-/+ resource "snowflake_table_column_masking_policy_application" "$WHATEVER" {
      ~ id             = "\"$DATABASE\".\"$SCHEMA\".\"$TABLE\".\"$COLUMN\"" -> (known after apply)
      ~ masking_policy = "$DATABASE.$SCHEMA.$UNIQUE_MASKING_POLICY_NAME" -> "\"$DATABASE\".\"$SCHEMA\".$UNIQUE_MASKING_POLICY_NAME\"" # forces replacement
        # (2 unchanged attributes hidden)
    }

Plan: 1413 to add, 0 to change, 1413 to destroy.

That plan was generated immediately after I had applied a similar plan - no changes to my TF code.
I think this resource was perhaps missed during the identifier rework.

If it would be useful, I can provide more details and / or open a new issue.

[sfc-gh-jmichalak]

Hi @AaronCoquet-Easypark 👋

This is caused by the legacy parsing of masking policy identifiers in this resource. We'll address this during table rework, which should happen soon. As a workaround, please use $DATABASE.$SCHEMA.$UNIQUE_MASKING_POLICY_NAME as masking_policy masking_policy value (to match the value saved in the state).

[yinxu0619]

I have a workaround for re-creating the plan, i use the jsonnet to render the terraform and add a converter in the head like below:

local fqn_trans(database, schema, resource) = std.asciiUpper(database + '.' + schema + '.' + resource);
local masking_policy_trans(database, schema, resource) = std.asciiUpper(database + '.' + schema + '.' + resource);
local tlb_trans(database, schema, resource) = std.asciiUpper('"' + database + "\".\"" + schema + "\".\"" + resource + '"');

local sig_trans(column) = {
  signature: {
    column: {
      name: column.name,
      type: std.asciiUpper(column.type),
    },
  },
};
{
  generateMaskingPolicy(policy_list, prefix): {
    [std.asciiUpper(policy_item.name)]: {
      name: std.asciiUpper(policy_item.name),
      database: std.asciiUpper(policy_item.database),
      schema: std.asciiUpper(policy_item.schema),
      signature: std.asciiUpper(policy_item.signature),
      masking_expression: policy_item.expression,
      return_data_type: policy_item.return_data_type,
      comment: policy_item.comment,
      provider: 'snowflake.default',
    } + sig_trans(policy_item.column)
    for policy_item in policy_list
  },
  generateMaskingPolicyApplication(policy_application_list, prefix): {
    [std.asciiUpper(policy_application_item.table_name + '_' + policy_application_item.column)]: {
      depends_on: ['snowflake_masking_policy.' + std.asciiUpper(policy_application_item.policy_name)],
      table: tlb_trans(policy_application_item.database, policy_application_item.schema, policy_application_item.table_name),
      column: std.asciiUpper(policy_application_item.column),
      masking_policy: masking_policy_trans(policy_application_item.database, policy_application_item.schema, policy_application_item.policy_name),
      provider: 'snowflake.default',
    }
    for policy_application_item in policy_application_list
  },
}

by this, it can append double quotes to the {database}.{schema} automatically.

[liamjamesfoley]

Still hitting this in V1:
#1764 (comment)