Feature request: Enable multiple grant resources for the same privilege

[dstuck]

I wanted to create a ticket to discuss what it would take to enable granting privileges to the same snowflake object in multiple resources.

Currently this limitation is blocking the ability to use submodules since all roles need to have permissions granted in one place. It also limits the ability to write code in a logically separated way. For example we want to create a local sysadmin that will be used to create some schemas and then add roles for accessing those schemas, but we have to create all roles and grant db usage up front, then create the schemas and finally grant privileges on those schemas.

For now we're going to just avoid using any grants and manage privileges using local-exec rather than design a module that requires merging all privilege grants up front. Having that capability would be really valuable though and we're interested in discussing the feasibility and work involved in allowing overlapping grant resources.

This is related to but a bigger ask than #142 which is requesting raising when this issue is present rather than developing a fix.

[leftathome]

Corollary: Some of the grant resources say that they're exclusive grants, but others don't. It appears they're all exclusive.

Even with the additional flexibility provided by Terraform 12, there are scenarios involving modules where "one grant resource to rule them all" is not proving to be enough. I'm not sure how much work is required under the hood for this but I think it would be a big deal for users as their Snowflake account grows in complexity...

[robbruce]

Scenario described in #211 (comment)

[ryanking]

In a post terraform 0.13 world (with for_each on modules) this seems like a more sustainable approach. It might take us a bit of time to plan on execute on though.

[funes79]

I also agree with @dstuck , in our case we would like to separate the terraform state and the whole module into multiple state files. Not sure if it is possible, but consider a scenario where you have 1 role (out of 20) which has access to DEV and PROD.
And DEV has tens or hundreds of grants and resources and PROD has tens or hundreds of grants and resources. If we would be able to split and separate, the CICD can then just run for dev only changes only on the DEV part.

[dstuck]

@ryanking I noticed that with the migration of docs to the terraform registry that there are no longer warnings about grants removing any other applied grants. Was this issue actually resolved or is it just less explicit?

[rasmusab]

Adding to the comment by @dstuck . I'm also not able to find any documentation on that it's not possible to have multiple grant resources of the same kind. Looking through the GitHub history I found this documentation in the README, but I can't fins it in the official documentation over at https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs . I think it's easy to get bitten by this and I think it would be great to include the old README info in the new documentation.

[ajwootto]

Echoing the concern about this warning being missing from the provider docs. I just got bitten by this yesterday and accidentally removed a bunch of pre-created grants from the database. It basically took doing that to discover that this is how those resources work.

I would also love the ability to separate grants as stated. It does make it hard to use these resources when all the roles need to be listed out in one place to create the grant.

[dstuck]

It looks like this is being addressed in part by #520. This looks like a smaller fix than I expected was necessary. Are there plans to standardize all grants in this way?

[louis-vines]

I like @robbruce's suggestion in #211 (comment). Is this something people think could be pursued?

[pierupaa]

One workaround for this is to create a role for the database usage grant e.g. DEV_DB_USAGE and then grant the database usage to this role and finally grant this role to the schemas ACC_R, ACC_RW and ACC_FULL roles. This works because as @dstuck pointed out in #520 the snowflake_role_grants resource was modified to look at only the grants that are part of the specified terraform resource. I tried the workaround and it works so no need for single global resource file for all object grants. Perhaps create a database ACC role for the usage grant?

[dstuck]

@jaisojar I think this just moves the problem up one level of abstraction. If I want to make a data access role in another module I still would need to pass every one of my single-use roles into the module or statically reference them which doesn't seem any better than just referencing the role grant directly in a snowsql statement
It does give a nicer way to handle thing that are all in the same module though and definitely makes things more workable

[pierupaa]

Good point, yes indeed it is a workaround, and not the ideal solution. I agree with you: this feature request totally makes sense to me and I think there are related feature requests e.g. to improve documentation related to how the resource grants behave when implemented with this provider.

I think there might be a workaround with using single user roles with terraform: In the case of account grants such as "EXECUTE TASK" and "CREATE ROLE" you could create a configuration with a single use role for each grant as an output. Then you could use this whole configuration or module as an input variable to your other module, no need to input each role separately as and input variable. Or perhaps you could output each role as on output of map type, and then use this as input variable when creating the schema acc roles.

Not ideal, but for me it works better than the other alternatives.

[dstuck]

I believe this is addressed as of v0.26.0
I am overjoyed that this pretty fundamental limitation has been addressed!