Incorrect application of future grant * in snowflake

[coleheflin-gt]

Terraform CLI and Provider Versions

Terraform v1.6.1
Snowflake-Labs 0.82

Terraform Configuration

terraform {

  required_version = ">= 1.6.1"

  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "0.82.0"
    }
  }
}

provider "snowflake" {
  account       = local.snowflake_account
  authenticator = "JWT"
}

provider "snowflake" {
  alias         = "account_admin"
  role          = "ACCOUNTADMIN"
  account       = local.snowflake_account
  authenticator = "JWT"
}

provider "snowflake" {
  alias         = "security_admin"
  role          = "SECURITYADMIN"
  account       = local.snowflake_account
  authenticator = "JWT"
}

provider "snowflake" {
  alias         = "sys_admin"
  role          = "SYSADMIN"
  account       = local.snowflake_account
  authenticator = "JWT"
}

resource "snowflake_grant_privileges_to_role" "g14" {
  privileges = ["SELECT"]
  role_name  = "TEST"
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema          = "*"
    }
  }
}

Expected Behavior

This should grant select on future tables in all schemas in the specified database.

Actual Behavior

It incorrectly grants select on future tables in all schemas. It grants select on future tables in schema DB.TABLE when it should be DB.SCHEMA.TABLE

Whenever a new table is added to a schema we need to regrant permissions. This occurs for all database objects including: views, materialized views, external tables, file formats etc.

Steps to Reproduce

1. terraform apply

How much impact is this issue causing?

Low

Logs

No response

Additional Information

No response

[sfc-gh-jcieslak]

@coleheflin-gt
Hey 👋
Have you tried something like this ⬇️

resource "snowflake_grant_privileges_to_role" "g14" {
  privileges = ["SELECT"]
  role_name  = "TEST"
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_database        = "<database_name>" -- this changed
    }
  }
}

That way you should achieve what you described.

[coleheflin-gt]

@coleheflin-gt Hey 👋 Have you tried something like this ⬇️

@sfc-gh-jcieslak Hi! Thanks for the reply! I have not tried that but I believe that would grant these privileges at the database level, is this correct? If so, we can't grant at the database level because we commonly grant at the schema level which nullifies the db grants. From the snowflake docs: "When future grants are defined on the same object type for a database and a schema in the same database, the schema-level grants take precedence over the database level grants, and the database level grants are ignored. This behavior applies to privileges on future objects granted to one role or different roles."

Or are you saying we could use our database name with a wildcard to grant at the schema level such as "<database_name>.*"?

[sfc-gh-jcieslak]

@coleheflin-gt
So I pointed you to where the problem is because the star notation shouldn't be allowed there and the error should appear (error from Snowflake indicating syntax error). With your version of the provider, you should get an error like this.

If that didn't happen that's strange...
Going back to the problem according to the grant privileges docs, we only care about those two branches

| { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> } }
| { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }

because tables are schemaObjects. As you can see the functionalities are limited when it comes to your use case. What you can do with the Terraform is, e.g. create a data source that would fetch all of the schemas you are interested in and then use HCL for_each meta-argument. Then you can perform grant <privileges> on future tables in schema <schema_name>. That would look something like this

resource "snowflake_grant_privileges_to_role" "g14" {
  privileges = ["SELECT"]
  role_name  = "TEST"
  for_each = data.snowflake_schemas.selected_schemas
   on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema              = each.name
    }
  }
}

If you care about current tables you can also add the same config that would grant privileges to all tables in schemas, e.g.

resource "snowflake_grant_privileges_to_role" "g14" {
  privileges = ["SELECT"]
  role_name  = "TEST"
  for_each = data.snowflake_schemas.selected_schemas
   on_schema_object {
    all {
      object_type_plural = "TABLES"
      in_schema              = each.name
    }
  }
}

[coleheflin-gt]

Ah I see, thanks for letting us know. We originally were using the data object and iterating over the schemas to grant all/future privileges as you described. We moved to the * grant because the aforementioned strategy significantly increases our terraform plans/runtimes but it sounds like that is the only option so we will move back to that.

The error you are mentioning does not occur for us and it seems to provide the proper privileges for the ALL grants but not the FUTURE grants as noted in the original issue.

[sfc-gh-jcieslak]

@coleheflin-gt
Hey again. Sorry, I was confused because granting with * is something I didn't encounter before. Actually, I think this may be possible to grant x on_future with the current provider. The following setup works for me:

resource "snowflake_grant_privileges_to_role" "g14" {
  privileges = ["INSERT"]
  role_name  = "TEST_ROLE_123"
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema          = "\"TEST_DB\".\"*\""
    }
  }
}

I created a table after applying this resource and indeed it had insert granted. I'm not sure why I didn't work for you in the first place, but I suspect it could be how you specify the identifier in the in_schema field.
Again, sorry for the confusion. I hope this helps.

[coleheflin-gt]

Thanks for the response @sfc-gh-jcieslak.

The set-up you've provided above mirrors what we were doing and that was not working properly for us. In your testing environment, did you have any schema level grants? I'm guessing the reason this worked for you is it is being treated as a database level grant and without any schema level grants it works. If you did not, can you try adding a schema level grant and then attempting this permission to check if it applies correctly? Here's a screenshot from the snowflake docs regarding the order of precedence of grants that I'm thinking is affecting this.

[sfc-gh-jcieslak]

@coleheflin-gt
It was some time ago, but I checked now with SQL scripts and as far as I am doing everything correctly it is indeed on the database and not on the schema. I've asked a few people internally what they think about it. Still waiting for the response, but I'll get back to you as soon as they respond. Here's a script that should show how identifier definition can impact if it's granted on the database or schema level.

create role test_role;
create database test_database;
create schema test_schema_1;
grant update on future tables in schema test_database."*" to test_role;
show future grants in schema test_database.test_schema_1; -- no results
show future grants in database test_database; -- because it's here (database level)
create schema test_schema_2;
grant update on future tables in schema test_database.test_schema_2 to test_role;
show future grants in schema test_database.test_schema_2; -- it's here (schema level)

So as you can imagine it's more of the Snowflake thing rather than the TF Provider thing. Maybe in the future, we'll consider handling such shortcuts internally (and unwind multiple SQL scripts with wildcards), but for now, I guess the safest route would be to do for_each (at least for grant on future x in schema resources).

[coleheflin-gt]

Yeah that makes sense. Would it be best to create a ticket internally with snowflake to address this?

[sfc-gh-jcieslak]

Yeah, an internal Snowflake ticket would be the best option. If @sfc-gh-asawicki and I could be cc'd that would be great. We would like to know the result of it. As the issue will go through a different route, I guess we can close this GH issue, right?

[coleheflin-gt]

sounds good, I've created a ticket but I do not have your emails so I passed along your names and requested they cc you.

[sfc-gh-jcieslak]

Great, Thank You