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

joey-squid · 2024-02-02T19:16:39Z

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.

The text was updated successfully, but these errors were encountered:

sfc-gh-jcieslak · 2024-02-06T11:07:05Z

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 · 2024-02-06T20:20:01Z

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 · 2024-02-07T09:31:56Z

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 · 2024-02-07T19:33:53Z

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 · 2024-02-07T19:38:37Z

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

joey-squid · 2024-02-07T20:00:05Z

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 · 2024-02-07T20:01:15Z

(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 · 2024-02-07T20:04:52Z

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 · 2024-02-08T12:15:10Z

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 · 2024-02-08T18:42:23Z

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 · 2024-02-28T19:39:23Z

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 · 2024-02-29T04:33:36Z

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

terraform-provider-snowflake/pkg/snowflake/grant.go

Line 373 in 07ce00a

} else if w {

sfc-gh-jcieslak · 2024-02-29T10:27:20Z

@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 · 2024-03-04T22:50:11Z

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

Fixes: #2459 The issue was related to the fact that the Read operation is only concerned about privileges (with_grant_option is also taken into consideration, but it's never set). Given this configuration: ```terraform resource "snowflake_grant_privileges_to_account_role" "test" { account_role_name = "TEST_ROLE" privileges = ["TRUNCATE"] on_schema_object { object_type = "TABLE" object_name = "TEST_DATABASE.TEST_SCHEMA.TEST_TABLE" } with_grant_option = true } ``` after apply we run the following commands by hand ```sql revoke truncate on table test_table from role test_role; grant truncate on table test_table to role test_role; -- notice we don't add "with grant option" which our resource should detect ``` Now, when we run `plan` or `apply` our resource is seeing a drift (the "TRUNCATE" privilege is not present, because `with_grant_option` is not matching) and tries to run the Update operation (unsuccessfully; 1. because of the "sdk.GrantPrivOptions" not set 2. because of the incorrect logic). When there're already existing grants there are two ways to update `with_grant_option` which depends on what is set in Snowflake. It's better to show it with SQLs, so: ```sql -- imagine this is ran by Snowflake Terraform Plugin (with_grant_option is set to true in the config) grant truncate on table test_table to role test_role with grant option; -- this is ran by hand in the worksheet revoke truncate on table test_table from role test_role; grant truncate on table test_table to role test_role; -- now update tries to run the following grant truncate on table test_table to role test_role with grant option; -- this will successfully update with_grant_option to 'true' ``` ```sql -- imagine this is ran by Snowflake Terraform Plugin (with_grant_option is set to false in the config) grant truncate on table test_table to role test_role; -- this is ran by hand in the worksheet revoke truncate on table test_table from role test_role; grant truncate on table test_table to role test_role with grant option; -- now update tries to run the following grant truncate on table test_table to role test_role; -- this won't update the with_grant_option to false because Snowflake is not updating the value when the option is already set to true (you have to revoke it first) ``` The fix I opted to is to: - when with_grant_option is set to true in the config - proceed as it was (but now set option struct correctly with with_grant_option set to true) - when with_grant_option is set to false in the config - firstly revoke privileges we would like to add (just in case this issue happens; it won't fail even if the grant does not exist) - then proceed as it was (grant privileges we would like to add) todo other grant privileges to database role ## Test Plan  * [x] Acceptance tests that prove the issue has been fixed for every privilege-granting resource

sfc-gh-jcieslak · 2024-04-04T13:38:24Z

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.

joey-squid added the bug Used to mark issues with provider's incorrect behavior label Feb 2, 2024

sfc-gh-jcieslak self-assigned this Feb 6, 2024

joey-squid changed the title ~~grant_privileges_to_role doesn't update with_grant_option~~ grant_privileges_to_role doesn't recognize changes to with_grant_option that were made outside of Terraform Feb 7, 2024

sfc-gh-jcieslak mentioned this issue Feb 29, 2024

Imported privileges on SNOWFLAKE database not registered in state #1998

Closed

sfc-gh-jcieslak mentioned this issue Mar 8, 2024

fix: with_grant_option diff drift #2608

Merged

1 task

sfc-gh-jcieslak closed this as completed in #2608 Apr 3, 2024

sfc-gh-jcieslak reopened this Apr 4, 2024

sfc-gh-jcieslak added the category:grants label May 20, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

joey-squid commented Feb 2, 2024

sfc-gh-jcieslak commented Feb 6, 2024

joey-squid commented Feb 6, 2024

sfc-gh-jcieslak commented Feb 7, 2024

joey-squid commented Feb 7, 2024 •

edited

Loading

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

sfc-gh-jcieslak commented Feb 8, 2024

joey-squid commented Feb 8, 2024

chrisweis commented Feb 28, 2024 •

edited

Loading

chrisweis commented Feb 29, 2024

sfc-gh-jcieslak commented Feb 29, 2024

chrisweis commented Mar 4, 2024

sfc-gh-jcieslak commented Apr 4, 2024 •

edited

Loading

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

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

Comments

joey-squid commented Feb 2, 2024

Terraform CLI and Provider Versions

Terraform Configuration

Expected Behavior

Actual Behavior

Steps to Reproduce

How much impact is this issue causing?

Logs

Additional Information

sfc-gh-jcieslak commented Feb 6, 2024

joey-squid commented Feb 6, 2024

sfc-gh-jcieslak commented Feb 7, 2024

joey-squid commented Feb 7, 2024 • edited Loading

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

joey-squid commented Feb 7, 2024

sfc-gh-jcieslak commented Feb 8, 2024

joey-squid commented Feb 8, 2024

chrisweis commented Feb 28, 2024 • edited Loading

chrisweis commented Feb 29, 2024

sfc-gh-jcieslak commented Feb 29, 2024

chrisweis commented Mar 4, 2024

sfc-gh-jcieslak commented Apr 4, 2024 • edited Loading

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

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

joey-squid commented Feb 7, 2024 •

edited

Loading

chrisweis commented Feb 28, 2024 •

edited

Loading

sfc-gh-jcieslak commented Apr 4, 2024 •

edited

Loading