Snow mask version 0.1.8 or 0.1.9 introduces code that attempts to "create schema if not exists" prior to creating a masking policy, this is a permission we don't want to give to the dbt role

[jcbmllgn]

Hello, first of all thank you for making this great package! We've happily been using it in production for quite a while now.

Issue summary

We tried upgrading from version 0.1.7 to 0.1.9 today, however, we experienced a failure when trying to create a new masking policy.

After upgrading to 0.1.9 it seems snow mask first runs create schema if not exists db_name.schema_nam prior to trying to create the masking policy. However, the Snowflake role that we use to execute DDL does not have permission to create a new schema in this database, we very much do not want dbt to be creating schemas in our RAW database which is the database context for this failure. Additionally, we did not see anything in the release notes about this new behavior.

I found this out by looking at the full context in the dbt.log file, below is the relevant excerpt:

12:52:44.927852 [debug] [MainThread]: Creating schema "_ReferenceKey(database='raw', schema='elation_firefly_health', identifier=None)"
12:52:44.932793 [debug] [MainThread]: Using snowflake connection "macro_create_masking_policy"
12:52:44.932913 [debug] [MainThread]: On macro_create_masking_policy: /* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "default", "target_name": "dev", "connection_name": "macro_create_masking_policy"} */
create schema if not exists raw.elation_firefly_health -- HERE IS THE ISSUE
12:52:44.932981 [debug] [MainThread]: Opening a new connection, currently in state init
12:52:45.697066 [debug] [MainThread]: Snowflake adapter: Snowflake query id: 01a480a4-0504-a4df-0000-98a50fdebaae
12:52:45.697691 [debug] [MainThread]: Snowflake adapter: Snowflake error: 003001 (42501): SQL access control error:
Insufficient privileges to operate on database 'RAW'
12:52:45.698276 [debug] [MainThread]: Snowflake adapter: Error running SQL: macro create_schema
12:52:45.698552 [debug] [MainThread]: Snowflake adapter: Rolling back transaction.
12:52:45.698949 [debug] [MainThread]: Snowflake adapter: Error running SQL: macro create_masking_policy
12:52:45.699234 [debug] [MainThread]: Snowflake adapter: Rolling back transaction.
12:52:45.699601 [debug] [MainThread]: On macro_create_masking_policy: Close
12:52:45.942882 [error] [MainThread]: Encountered an error while running operation: Database Error
  003001 (42501): SQL access control error:
  Insufficient privileges to operate on database 'RAW'
12:52:45.943639 [debug] [MainThread]: 
12:52:45.944479 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1071ddb20>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1071dd9d0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1071ddaf0>]}
12:52:46.309796 [debug] [MainThread]: Connection 'macro_create_masking_policy' was properly closed.

I added a comment on the relevant line, see the spot where it says HERE IS THE ISSUE.

Question for the snow mask maintainers

Ideally snow mask would not try to create a table, this is pretty surprising behavior. Can this behavior be configurable behavior?

[entechlog]

@jcbmllgn , Thank you for raising this issue. I believe the issue is caused after https://github.com/entechlog/dbt-snow-mask/releases/tag/0.1.8 release. I will review this one and get back.

[jcbmllgn]

Thanks for the quick response @entechlog! Again, really appreciate the work you put into this package 🙌

Out of curiosity, what was the intent behind this workflow change to run create schema if not exists prior to creating masking policies?

[entechlog]

@jcbmllgn, It was added part of adding common db and schema for masking policy. So when creating the masking policy the process won't error out if there is no schema https://github.com/entechlog/dbt-snow-mask/pull/25/files#diff-cf387b66a67d7cd5ab4c7c4355916bda050a60d0489757a646d76a59e4aa897bR29

[entechlog]

@jcbmllgn So based on the above lines, we still need this logic, but I can add a flag for create schema and enable it by default in package but users who dont want dbt to create schema and disable in the project. How does that sound?

[jcbmllgn]

So based on the above lines, we still need this logic, but I can add a flag for create schema and enable it by default in package but users who dont want dbt to create schema and disable in the project. How does that sound?

That sounds good to me. Thank you!

[nadesansiva]

@jcbmllgn , I have created a release candidate for this change here https://github.com/entechlog/dbt-snow-mask/releases/tag/0.2.0rc2. Do you have sometime to test this out before I create the final release ?

[jcbmllgn]

Thanks for the very speedy turnaround!

I just tested the release candidate and it worked for me.

[nadesansiva]

Thank You @jcbmllgn for validating the change. I have just released a new version 0.2.0 and should be reflected in dbt hub in about an hour OR so. Closing the issue now.