Feature request: Handle updated policies automatically

[rumbin]

Currently, if an existing policy is updated, it needs to be unapplied, deleted, created (or CREATE OR REPLACE...) and applied again. This is imho a manual process which is cumbersome when applied to production environments. It would be great if we could find a solution which handles these things automatically.

[rumbin]

Just got aware of the fact that this is ak known limitation. Nevertheless, I think that handling this as an open issue is probably a good idea,so we can discuss ideas here...

[eugenekim-orrum]

I ran into this as well and had to choose between the IF EXISTS clause or the OR REPLACE clause. Maybe a separate Update procedure that unapplies, drops, then creates?

[entechlog]

• @eugenekim-orrum Sorry that you faced this issue.
• I am managing with REPLACE clause now but thinking to enhance unapply macro.
• Do you have a list of actions which needs to happen and the scenarios on which this policy update would happen ?
• Tagging @robscriva as well here to discuss ideas and options

[mjahammel]

I'm coming to the party late so maybe things have changed in Snowflake since previous comments. Snowflake has an ALTER MASKING POLICY command which allows you to change the masking policy without having to go through the "unapplied, deleted, created (or CREATE OR REPLACE...) and applied again" process.

I'm still new to dbt, but I was able to build a macro that checks for the existence of the masking policy and does a CREATE or ALTER, depending on the result of the check.

Perhaps something like that could be added to the package.

[entechlog]

Hello @mjahammel, I am open for suggestions and enhancements to the package. Do you mind sharing the source code for your macro, perhaps we can check and work together to see if this enhancement can be implemented ?

[mjahammel]

@entechlog , I will create a pull request with the new macro and an implementation of it.

[romansegador]

Hi, I found this looking for a more elegant solution, but in case it helps, I'm solving this by adding both create masking policy if not exists and then, if I have to edit it, adding a alter masking policy if exists

{% macro create_masking_policy_mp_mask_pii(node_database,node_schema) %}

CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.mp_mask_pii AS (val string) 
  RETURNS string -> IFF(CURRENT_ROLE() IN ('ROLE1'),  val, '**********' );

ALTER MASKING POLICY IF EXISTS {{node_database}}.{{node_schema}}.mp_mask_pii SET BODY ->
  IFF(CURRENT_ROLE() IN ('ROLE1', 'ROLE2'),  val, '**********' )

{% endmacro %}

First time you run the macro in a new environment, it will run both, the create and alter, following runs, it will run only the alter as it is already created.