Replacing snowflake_grant_privileges_to_role resources still fails at apply time with validation error

[mmyers5]

Terraform CLI and Provider Versions

Terraform v1.5.4
on linux_arm64

• provider registry.terraform.io/snowflake-labs/snowflake v0.87.2

Terraform Configuration

resource "snowflake_grant_privileges_to_account_role" "my_role_object_select" {
  account_role_name = "MY_ROLE"
  on_schema_object {
    object_type = "TABLE"
    object_name = "MY_DATABASE.MY_SCHEMA.MY_VIEW"
  }
  privileges = ["SELECT"]
  with_grant_option = false
}

resource "snowflake_grant_privileges_to_account_role" "my_role_object_select" {
  account_role_name = "MY_ROLE"
  on_schema_object {
    object_type = "VIEW"
    object_name = "MY_DATABASE.MY_SCHEMA.MY_VIEW"
  }
  privileges = ["SELECT"]
  with_grant_option = false
}

Expected Behavior

When I switch the object type from "TABLE" to "VIEW", it should replace the object. Here is the plan:

module.data_warehouse.snowflake_grant_privileges_to_account_role.my_role_object_select must be replaced
-/+ resource "snowflake_grant_privileges_to_account_role" "my_role_object_select" {
      ~ id                = "\"MY_ROLE\"|false|false|SELECT|OnSchemaObject|OnObject|TABLE|\"MY_DATABASE\".\"MY_SCHEMA\".\"MY_VIEW\"" -> (known after apply)
      ~ privileges        = [
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)

      ~ on_schema_object {
          ~ object_type = "TABLE" -> "VIEW" # forces replacement
            # (1 unchanged attribute hidden)
        }
    }

Actual Behavior

Here is the apply:

│ Error: An error occurred when revoking privileges from account role
│ 
│ Id:
│ "MY_ROLE"|false|false|SELECT|OnSchemaObject|OnObject|TABLE|"MY_DATABASE"."MY_SCHEMA"."MY_VIEW"
│ Account role name: "MY_ROLE"
│ Error: [grants_validations.go:44] exactly one of AccountRoleGrantPrivileges
│ fields [AllPrivileges GlobalPrivileges AccountObjectPrivileges
│ SchemaPrivileges SchemaObjectPrivileges] must be set

When I tried to reset the state by removing the resource block entirely, the apply always failed. I resolved by altering the statefile with terraform state rm.

I see that this issue was addressed recently and fixed as of provider version 0.87.1, but as indicated I was on 0.87.2.

Steps to Reproduce

1. terraform apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

This looks like a duplicate of #2068 and #2069

[sfc-gh-jcieslak]

Hey @mmyers5
Thanks for reporting an issue! I'll try to reproduce the error in the next week.

[sfc-gh-jcieslak]

Hey 👋
Sorry for taking so long. I will have more time this week, so I'll reproduce it and get back to you immediately.

[sfc-gh-jcieslak]

Hi again,
I finally had some time and reproduced the issue and I have a question because previously I didn't notice, but is there any particular reason you would like to reference a table in the object_name with the object_type = "VIEW" or vice-versa? Generally, I checked and it's possible in Snowflake, but in the provider, the privileges are being filtered out. Because of that the privileges in the state are empty [] and when it comes to the Delete operation we try to revoke an empty set of privileges which is not allowed. That being said, is there any advantage to making such changes or was it accidental, and right now you just cannot correct it?

[inesplc]

We are getting the same error on Terraform v1.7 and provider v0.89. It seems exactly the same pattern as #2069 and #2068.

Our plan looks like this:

  # module.create_roles_workspace_databases.snowflake_grant_privileges_to_account_role.grant_future_usage_privileges_to_db_ro_role["WKS_AE_INT_S_REIG_PROCEDURES"] will be destroyed
  # (because key ["WKS_AE_INT_S_REIG_PROCEDURES"] is not in for_each map)
  - resource "snowflake_grant_privileges_to_account_role" "grant_future_usage_privileges_to_db_ro_role" {
      - account_role_name = "\"WKS_AE_INT_RO_FULL_ROLE\"" -> null
      - all_privileges    = false -> null
      - always_apply      = false -> null
      - id                = "\"WKS_AE_INT_RO_FULL_ROLE\"|false|false|USAGE|OnSchemaObject|OnFuture|PROCEDURES|InSchema|\"WKS_AE_INT\".\"S_REIG\"" -> null
      - on_account        = false -> null
      - privileges        = [] -> null
      - with_grant_option = false -> null

      - on_schema_object {
          - future {
              - in_schema          = "\"WKS_AE_INT\".\"S_REIG\"" -> null
              - object_type_plural = "PROCEDURES" -> null
            }
        }
    }

which fails with:

│ Error: An error occurred when revoking privileges from account role
│ 
│ Id:
│ "WKS_AE_INT_RO_FULL_ROLE"|false|false|USAGE|OnSchemaObject|OnFuture|PROCEDURES|InSchema|"WKS_AE_INT"."S_REIG"
│ Account role name: "WKS_AE_INT_RO_FULL_ROLE"
│ Error: [grants_validations.go:166] exactly one of
│ AccountRoleGrantPrivileges fields [AllPrivileges GlobalPrivileges
│ AccountObjectPrivileges SchemaPrivileges SchemaObjectPrivileges] must be
│ set

I checked and it's possible in Snowflake, but in the provider, the privileges are being filtered out. Because of that the privileges in the state are empty [] and when it comes to the Delete operation we try to revoke an empty set of privileges which is not allowed

@sfc-gh-jcieslak In this case the privileges are only empty because the resource is being destroyed. Our existing resource has ["USAGE"] in the privileges set 🤔 I think it might be the same case for @mmyers5 given the id of the resource.

[sfc-gh-jcieslak]

Hey @inesplc 👋
If the privileges are empty (or there are less privileges in the state in comparison to what you have in config), then it means there is either a bug in the Read operation for a given resource or it's a case of misuse. I'm not sure what is causing problems in your case because I would have to have your configuration to be able to reproduce the issue, but in the case of @mmyers5 (from the configuration in the issue description) it seems like a misuse of the provider to set the object_type to VIEW when you are referring to the table identifier in the object_name field. I would be grateful if you could create a new GitHub issue with the description and steps to reproduce (and if possible debug logs), so I could dig deeper into your issue and tell you more about why this case is also failing.

[mmyers5]

Yeah the issue in this case was the view was mis-labeled as a table. It didn't throw an exception on applying when the resource had the wrong label, but it did when we tried to fix the label.

[sfc-gh-jcieslak]

Yeah, it didn't throw, because that behavior is allowed by Snowflake. Because right now we're mainly focusing on GA objects refactoring, we have to move the discussions around it for now. The solution for now is to use table object type with table identifier and the same for views.

[simonepm]

I see a similar behavior when destroying/replacing SELECT grant on an EVENT TABLE.

snowflake_grant_privileges_to_account_role.GRANT_SELECT_ON_EVENT_TABLE_TO_ROLE_<ROLE_NAME> will be destroyed:
- resource "snowflake_grant_privileges_to_account_role" "GRANT_SELECT_ON_EVENT_TABLE_TO_ROLE_<ROLE_NAME>" {
  - account_role_name = "<ROLE_NAME>" -> null
  - all_privileges = false -> null
  - always_apply = false -> null
  - id = "\"<ROLE_NAME>\"|false|false|SELECT|OnSchemaObject|OnObject|TABLE|\"<DATABASE_NAME>\".\"PUBLIC\".\"EVENTS\"" -> null
  - on_account = false -> null
  - privileges = [] -> null
  - with_grant_option = false -> null
  - on_schema_object {
    - object_name = "<DATABASE_NAME>.PUBLIC.EVENTS" -> null
    - object_type = "TABLE" -> null
  }
}

The following error occurs:

Error: An error occurred when revoking privileges from account role
Error: [grants_validations.go:166] exactly one of
AccountRoleGrantPrivileges fields [AllPrivileges GlobalPrivileges
AccountObjectPrivileges SchemaPrivileges SchemaObjectPrivileges] must be set

Using:

Terraform 1.6.6
Provider 0.92.0

[jarach]

I'm getting the same error as @simonepm. It happens only while revoking privileges from EVENT TABLE. Other tables/schemes/databases are not affected.

  # module.main.snowflake_grant_privileges_to_account_role.tb_grant["DEV_HME_ADMIN__DEV_ADMIN_DB.EVENT_LOGS_SC.EVENT_TABLE"] will be destroyed        
  - resource "snowflake_grant_privileges_to_account_role" "tb_grant" {
      - account_role_name = "DEV_HME_ADMIN" -> null
      - all_privileges    = false -> null
      - always_apply      = false -> null
      - id                = "\"DEV_HME_ADMIN\"|false|false|SELECT|OnSchemaObject|OnObject|TABLE|\"DEV_ADMIN_DB\".\"EVENT_LOGS_SC\".\"EVENT_TABLE\"" -> null
      - on_account        = false -> null
      - privileges        = [] -> null
      - with_grant_option = false -> null

      - on_schema_object {
          - object_name = "DEV_ADMIN_DB.EVENT_LOGS_SC.EVENT_TABLE" -> null
          - object_type = "TABLE" -> null
        }
    }

Error: An error occurred when revoking privileges from account role

Id: "DEV_HME_ADMIN"|false|false|SELECT|OnSchemaObject|OnObject|TABLE|"DEV_ADMIN_DB"."EVENT_LOGS_SC"."EVENT_TABLE"
Account role name: "DEV_HME_ADMIN"
Error: [grants_validations.go:166] exactly one of AccountRoleGrantPrivileges fields [AllPrivileges GlobalPrivileges AccountObjectPrivileges
SchemaPrivileges SchemaObjectPrivileges] must be set

Using:
Terraform 1.8.5
Provider 0.92.0

[sfc-gh-jcieslak]

Hey @simonepm @jarach 👋
It should be "EVENT TABLE" for event tables, not "TABLE". In the SHOW GRANTS response for event tables, the object type is "EVENT TABLE" and we're using this field to filter out the output. That's why after the read operation the privileges are empty and causing infinite diff for privileges. Incorrect object type will cause difficulties when removing the objects (that's why it's always better to check what object type is returned in SHOW GRANTS for this object). The only way to fix this is to remove this resource from the state (with terraform state rm) and revoke the grant manually (calling revoke grant). If you would like to fix the resource you can just remove it from the state, fix the object type to be "EVENT TABLE", and apply. That should create grant resource for event table.

[jarach]

Hi @sfc-gh-jcieslak ,
It's working as you wrote.

Full note for others having the same or similar problem:
SHOW GRANTS returns "EVENT_TABLE" but in terraform one should use "EVENT TABLE" (without underscore). All according to documentation.

resource "snowflake_grant_privileges_to_account_role" "evtb_grant" {
  for_each = ....

  provider = snowflake.sys_admin
  privileges         = each.value.privileges
  account_role_name  = each.value.role_env_name
  on_schema_object  {
    object_type = "EVENT TABLE"
    object_name = each.value.tb_env_name
  }
}

Big thanks!

[sfc-gh-jcieslak]

Closing as the issue has been resolved