SET MASKING POLICY resources

[TerjeRusska]

Following Snowflakes' guidelines in Using Dynamic Data Masking

Current option to add masking_policy in snowflake_table (Resource) is not viable for masking_admin role that has been granted APPLY MASKING POLICY

This account-level privilege controls who can [un]set masking policies on columns and is granted to the ACCOUNTADMIN role by default. This privilege only allows applying a masking policy to a column and does not provide any additional table privileges described in Access Control Privileges.

Provider for snowflake_table (Resource) to create/update the resource has OWNERSHIP privilege to the table. This is currently conflicting with the masking_admin provider, that should only apply the masking policies. In some cases, table owner should not be able to set or unset masking policies.

There's a need for separate resources that apply the following (and nothing else):

-- apply masking policy to a table column

ALTER TABLE IF EXISTS user_info MODIFY COLUMN email SET MASKING POLICY email_mask;

-- apply the masking policy to a view column

ALTER VIEW user_info_v MODIFY COLUMN email SET MASKING POLICY email_mask;

This will make sure that masking_admin can work without importing table resources owned by other roles.

resource "snowflake_set_table_masking_policy" "example" {
  database = "database"
  schema = "schema"
  table = "table"
  column = "column"
  masking_policy = "masking_policy"
}
resource "snowflake_set_view_masking_policy" "example" {
  database = "database"
  schema = "schema"
  view = "view"
  column = "column"
  masking_policy = "masking_policy"
}

Also should add column information to snowflake_tables (Data Source) (or a new data source snowflake_columns?) to see what policies have already been applied.

data "snowflake_columns" "current" {
  database = "database"
  schema   = "schema"
  table  = "table"
}

[TerjeRusska]

Set masking policy resources could utilize block list too just like snowflake_table

resource "snowflake_set_table_masking_policy" "example" {
  database = "database"
  schema = "schema"
  table = "table"
  column {
    name = "column1"
    masking_policy = "masking_policy"
  }
  column {
    name = "column2"
    masking_policy = "masking_policy"
  }
}

[sfc-gh-ngaberel]

Hi @TerjeRusska, thanks for submitting this issue.

As you suggest, it sounds like there's a need for being able to apply masking policies to columns separately from table creation and with a different role. Your suggestion to create a separate resource to manage policy applications makes sense and actually aligns with our plan to revamp the table resource (and fix the long standing issue #753).

Our current thinking is to implement a new masking policy application resource that could be used like so:

# Default provider for most resources
provider "snowflake" {
  role = "TF_ROLE"
}

# Alternative provider with masking_admin role
provider "snowflake" {
  alias = "masking"
  role  = "MASKING_ADMIN"
}

resource "snowflake_masking_policy" "policy" {
  provider = snowflake.masking # Create masking policy with masking_admin role

  name               = "EXAMPLE_MASKING_POLICY"
  database           = "EXAMPLE_DB"
  schema             = "EXAMPLE_SCHEMA"
  value_data_type    = "string"
  masking_expression = "case when current_role() in ('ANALYST') then val else sha2(val, 512) end"
  return_data_type   = "string"
}

# Table is created by the default provider
resource "snowflake_table" "table" {
  database = "EXAMPLE_DB"
  schema   = "EXAMPLE_SCHEMA"
  name     = "table"

  column {
    name     = "age"
    type     = "int"
  }
}

# Proposed new resource
resource "snowflake_table_column_masking_view_application" "application" {
  provider = snowflake.masking # Apply masking policy with masking_admin role

  table = snowflake_table.table.qualified_name
  column = "age"
  masking_policy = snowflake_masking_policy.policy.qualified_name
}

With that setup you should be able to finely control which role has table ownership and which role creates and applies masking policy.

Would that resolve your issue?

[TerjeRusska]

Hi @sfc-gh-ngaberel Yes this functionality is what we're looking for. Have you looked into the block list method as well? I think it would keep the overall state resource count down if they are grouped together on table level and not have for every column separately.

[sfc-gh-ngaberel]

Hi @TerjeRusska, the new resource has been merged in main (cf. #1739) and will be available in the next release of the provider (this week or next probably).
Regarding using a list of columns in the the table resource, unfortunately that model makes it very difficult to detect and reconcile changes properly as has been mentioned in many issues (eg. #753) so we will be moving to remove columns definition from the table resource in the near future to allow for better columns management. We'd love to our more ideas if you think there's a better way to tackle this issue!

[sfc-gh-ngaberel]

@TerjeRusska We just released v0.63 with the new resource, feel free to upgrade!

[TerjeRusska]

@sfc-gh-ngaberel I have implemented this resource for a test run, first thing I noticed is that when setting the table value as DATABASE.SCHEMA.TABLE then the next plan will try to set the value to "DATABASE"."SCHEMA"."TABLE" thus causing an endless force replace loop until the format is changed. I'm building the qualified name from scratch since this is currently missing from data source output.

[sfc-gh-ngaberel]

@TerjeRusska Oh yes, sorry about that. We're slowly updating the provider to use quotes for every identifier (lots of benefits, including case sensitivity and not having unexpected plans), however we're far from done and haven't gotten round to updating this data source's output yet.
Eventually you won't have to build identifiers manually but please bear with us while we go through this migration on our way to a stable v1!