Add on_existing_and_future option for grant resources where on_future is an option

[chriscardillo]

Is your feature request related to a problem? Please describe.

For all grant resources that allow on_future, I would like some mechanism to guarantee that all existing objects (tables/views/etc.) will also be available to the users/roles listed in the grant resource. More details below.

When using a grant resource like snowflake_table_grant, I am able to specify on_future for the database or schema and assign that grant to a role or set of roles. To the best of my knowledge, the on_future parameter is only useful if I never change the role that has these grants.

Let's take a simple example, where I grant SELECT to all future tables in a new schema to "role_a" using the snowflake_table_grant.

1. The initial snowflake_table_grant specifies "role_a" and on_future = true. "role_a" can now SELECT all tables I create in this new schema
2. I create two tables - "table_1" and "table_2", which "role_a" can SELECT because of the on_future specified in the above resource.
3. I then adjust this snowflake_table_grant resource, and I replace "role_a" with "role_b".
4. "role_b" would not be able to SELECT on "table_1" and "table_2", because "role_b" has only been granted SELECT on future tables, not existing tables.

Describe the solution you'd like

It would be nice to have something like on_existing_and_future, which would conflict with on_future.

resource "snowflake_table_grant" "select" {
  privilege              = "SELECT"
  database_name          = "db"
  schema_name            = "schema"
  roles                  = ["role"]
  on_existing_and_future = true
  with_grant_option      = false
}

The above would ensure that a role change on the resource would not result in a loss of data accessibility for the new role.

One important concern of adding this feature is the resulting terraform id. I do not know the intricacies of this package, but I believe that the resulting terraform id for this grant resource is the combination of database_name|schema_name|table_name|privilege|on_future|with_grant_option. To implement this feature in such a way that it would not drastically affect the resulting terraform id, the |on_future| id slot - which currently can be nil, true, or false - could render as |oeaf| if on_existing_and_future = true.

Describe alternatives you've considered

• In tandem with the snowflake_table_grant, use the snowflake_tables data source, issue per-table grants to the same role that is receiving the on_future grants. This would alleviate the issue but is does not scale desirably. This option all doesn't work when standing up infrastructure from scratch, so one would need to add this capability after the initial apply.
• Slow roll schema ownership to the target role. First, create a brand new schema with the proper on_future grants and assign to the new role. Second, migrate the data from the existing schema into the new schema. Third, retire the old role/schema as needed. This alleviates the issue but requires much coordination and lacks a certain agility.

Additional context

I think this feature would really make grants work more desirably, and would prevent the need to slow roll toward role changes. This missing feature is the one reason I am apprehensive to terraform my Snowflake infrastructure.

[JohnCalhoun]

yeah, at my previous job we forked this repo to build in that feature cause it was really usefull for migrating to terraform. I was not able to contribute it back but it was not hard to implement

[chriscardillo]

@JohnCalhoun - any interest in pairing and PR'ing into the provider?

[mcupo-calix]

Is there active work being done on this?

[brandocomando]

Also very interested in this feature

[si-te]

I second this feature to be very useful. Not having this feature clashes a bit with the "desired state config" philosophy of terraform.

[Relativity74205]

We are also very interested in this feature

[aidanmelen]

Hey folks, the snowsql provider doesn't have the same guarantees as the snowflake provider but it does provide an escape hatch for the tricky to implement ALL FUTURE grants. Here is an example that you might find useful.

[froblesmartin]

Also interested in this. It is a bit difficult because the resource can not ensure that all resources have the grant until it is executed again (unless the functionality is added in Snowflake itself, and is a configuration like the on future). Also, what happens with the resources that may not be managed by Terraform? Should it also be applied to those resources? I would say so 🤔.

In a Terraform module wrapping this provider, you could loop over all resources and generate a grant for each of them, reaching the same goal.

[sfc-gh-asawicki]

We are closing this issue as part of a cleanup described in announcement. If you believe that the issue is still valid in v0.89.0, please open a new ticket.