Apply Snowflake's Row Access Policy at table creation time

I’m using snowflake’s row access policies to limit the rows that each user can query in some tables. In dbt right now the only way we found to do that is to add a post-hook with an ALTER TABLE command to add the policy.

The problem with this approach is that the security is applied after the table creation and there is a risk that it is not applied. It would be great to add this as a configuration option for snowflake models or to be able to customize the CREATE TABLE DDL.

Do you know a better way to do this ? Could it be implemented in dbt in the future ? :slight_smile:

Damien

Hi Damien, just wanted to share that I’m testing a similar strategy in Snowflake using post-hooks to attach row access policies. I’m curious about the risk you mentioned about the policy not being applied. Have you been seeing issues with this?

As far as customizing the create table DDL, I think you could look at making a custom materialization to do this. I just noticed that dbt also has a guide/tutorial related to this topic which may be a good starting point: Create new materializations | dbt Developer Hub

Hi @lisa.fang , we did not have any issue yet :slight_smile: but even if the probability of an issue is low, we would like to address it.

It could happen for example if we mess with snowflake rights and forget to grant apply row access policyto the dbt user in our deployment pipeline.

And even if there is no issue during execution, with the current approach, the table exists for a few milliseconds unsecured.

We investigate different solutions for this issue :

  • blue/green deployment
  • creation of secured tables by terraform during deployment (before any dbt run)
  • (and thanks to you :slight_smile: ) custom materialization

Post hooks weren’t acceptable to us in snowflake due to the limitations around transactions. I overloaded the snowflake view materialization in our project so that the row access and column masking policies we add via model config properties are injected into the view DDL.