grant_privileges_to_role doesn't recognize changes to `with_grant_option` that were made outside of Terraform

[joey-squid]

Terraform CLI and Provider Versions

Terraform v1.6.2 on darwin_arm64

• provider registry.terraform.io/hashicorp/aws v5.33.0
• provider registry.terraform.io/snowflake-labs/snowflake v0.84.0

Terraform Configuration

resource "snowflake_grant_privileges_to_role" "grant_metrics_table_usage" {
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE"]
  role_name  = snowflake_role.metrics_role.name
  on_schema_object {
    object_name = "\"${snowflake_database.metrics_db.name}\".\"PUBLIC\".\"METRICS\""
    object_type = "TABLE"
  }
  with_grant_option = true
}

Expected Behavior

Those permissions, which currently have their grant_option set to false (as reported in show grants to role), should change to have their grants set to true.

Actual Behavior

terraform plan shows

      ~ privileges        = [
          + "DELETE",
          + "INSERT",
          + "SELECT",
          + "UPDATE",
        ]

and terraform apply doesn't actually change anything.

Steps to Reproduce

terraform apply or terraform plan

How much impact is this issue causing?

Low

Logs

No response

Additional Information

I took a gander at the code to see if there was anything I was doing obviously wrong, and it seems that the UpdateGrantPrivilegesToRole function never calls d.Get("with_grant_option").(bool) (the CreateGrantPrivilegesToRole function does), so I'm fairly certain this is just a bug in the provider. I don't have the bandwidth to fix it but I do have the bandwidth to test a fix if someone else gets there.

[sfc-gh-jcieslak]

Hey @joey-squid 👋 The privileges showing up in the plan (even though they were granted) were a bug we fixed in the latest releases. Please upgrade to the latest version and take a look if the non-empty plan persists. with_grant_option not updating is an interesting one, because I don't see any reason why shouldn't it work. with_grant_option is a ForceNew parameter, which means that on update (e.g. setting it from false to true) Terraform will perform Delete and Create operations. Because of that with_grant_option doesn't have to be in the Update section. Also, there's a "new" snowflake_grant_privileges_to_role which is called snowflake_grant_privileges_to_account_role, here is the migration guide that may help you with migrating.

[joey-squid]

Ah, I didn't realize ForceNew was a thing, though I probably should have. In any case, unfortunately the non-empty plan does persist when I upgrade to v0.85. It should be noted that while I'm not sure what Terraform thinks, Snowflake thinks the grant option is disabled. The ID is "PROD_METRICS|INSERT,DELETE,SELECT,UPDATE|false|true|false|false|false|true|false|false|TABLE|\"PROD_METRICS\".\"PUBLIC\".\"LOGS\"||false||false|" and I don't know how to parse it.

[sfc-gh-jcieslak]

So I tried your configuration and there was no sign of non-empty plans. Could you provide the exact steps to reproduce the issue? If so, then I could be able to help, because right now everything seems correct (tested against the 0.85.0 version with the same granting resource and privileges, and tried updating with_grant_option; show grants to role x were showing correct results). The ID can be read in the documentation for every resource (at the bottom there're Import sections) - https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_role#import. You can also check our newer version of granting privileges to role that has more readable ID - https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_account_role#import

[joey-squid]

~I'm going to do my best to reproduce this later today, but for the moment I want to comment that this might be related to the fact that running grant ... with grant option seems to be a no-op if the relevant permission already exists with no grant option. I'll continue researching this and might raise an issue with our Snowflake support folks if that's the case, because that seems counterintuitive to me.~

[joey-squid]

I take back my previous comment. I was misclicking in the UI.

[joey-squid]

OK, I think this is a full reproduction of the issue I'm seeing. It involves changing permissions out from under Terraform.

This is my config:

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

provider "snowflake" {
  profile = "dev-snowflake"
}

resource "snowflake_grant_privileges_to_role" "joey_test" {
  privileges = ["TRUNCATE"]
  role_name  = "DEV_METRICS"
  on_schema_object {
    object_name = "\"DEV_METRICS\".\"PUBLIC\".\"METRICS_SNOWPIPE\""
    object_type = "TABLE"
  }
  with_grant_option = true
}

I applied it, then went into a Snowflake worksheet and ran the following two statements:

revoke truncate on DEV_METRICS.PUBLIC.METRICS_SNOWPIPE from dev_metrics

grant truncate on DEV_METRICS.PUBLIC.METRICS_SNOWPIPE to dev_metrics

(i.e., removed the grant option).

Now terraform plan says it will try to update the permission in place, when it should actually be destroying and recreating the permission.

  # snowflake_grant_privileges_to_role.joey_test will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "joey_test" {
        id                = "DEV_METRICS|TRUNCATE|false|true|false|false|false|true|false|false|TABLE|\"DEV_METRICS\".\"PUBLIC\".\"METRICS_SNOWPIPE\"||false||false|"
      ~ privileges        = [
          + "TRUNCATE",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

For completeness, I will run the same test with grant_privileges_to_account_role and let you know what happens.

[joey-squid]

(I should mention that terraform apply didn't actually change anything and even after running it, running it a second time showed the exact same diffs)

[joey-squid]

I added account_ in two places in my config (lines 14 and 16) and got exactly the same result, for what it's worth.

[sfc-gh-jcieslak]

Hey, Thank you for the detailed description. I'll try to reproduce the issue and find its cause as soon as I have some time.

[joey-squid]

Much appreciated and thanks for all your support and patience so far. In the meantime I've worked around this issue by creating the correct permissions manually.

[chrisweis]

Hey @sfc-gh-jcieslak and @joey-squid, I think I'm also seeing this permadiff in the same way, and it's blocking one of my project's deployments. My resource is:

resource "snowflake_grant_privileges_to_account_role" "ENV_DW_DB__DBT_ENV_ROLE" {
  privileges = ["USAGE","CREATE SCHEMA","MODIFY","MONITOR"]
  account_role_name  = "DBT_${var.ENV}_ROLE"
  on_account_object {
    object_type = "DATABASE"
    object_name = snowflake_database.ENV_DW_DB.name
  }
  with_grant_option = true
}

Version:

terraform {

  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "~> 0.86.0"
    }
  }

}

I also tested it in 0.87.0, and the problem persists.

When I watch the Snowflake queries I see that it is repeatedly applying the grants on each terraform apply:

It doesn't seem like the SQL is arriving with the WITH GRANT OPTION into Snowflake:

GRANT MONITOR ON DATABASE "DEV_DW_DB" TO ROLE "DBT_DEV_ROLE"

Oddly I'd noticed that it was occuring in my production (PRD) database but not my development (DEV) database, so I made this modification to that DEV environment... From: privileges = ["USAGE","CREATE SCHEMA","MODIFY","MONITOR"] To: privileges = ["USAGE","CREATE SCHEMA","MODIFY"] Back to: privileges = ["USAGE","CREATE SCHEMA","MODIFY","MONITOR"] ...and now the permadiff also exists in DEV. Weird!

The query view of the DEV removal/addition of the "MONITOR" permission:

I tried an experiment of removing the with_grant_option = true and the permadiff goes away, so it seems related to that property.

(Also, I tried hard-coding my environment variable value but it had no impact, still had a permadiff: From: account_role_name = "DBT_${var.ENV}_ROLE" To: account_role_name = "DBT_DEV_ROLE")

One more clue, it looks like the MONITOR grant is never arriving in Snowflake: If I manually add the grant (joey-squid's workaround) then the permadiff goes away.

[chrisweis]

@sfc-gh-jcieslak could the issue possibly be related to this line in the Go code?

https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/07ce00a2b2a647a21f037c5019cca6bbff1399b2/pkg/snowflake/grant.go#L373

[sfc-gh-jcieslak]

@chrisweis No, the snowflake package is deprecated. Now, we use the sdk one. I'm going to work on this issue today and we'll release it next week.

[chrisweis]

@sfc-gh-jcieslak Please let me know if there's anything I can do to help with this issue - happy to help! Thanks!

[sfc-gh-jcieslak]

Hey 👋 The fix will be available in the next provider version (we'll release it next week). The fix only applies to the non-deprecated grant resources snowflake_grant_privileges_to_database_role and snowflake_grant_privileges_to_account_role, the snowflake_grant_privileges_to_role will not be fixed as it's a deprecated resource now. If you are still using the deprecated resources, the migration guide may help in the migration process to use the latest resources.