[Bug]: Unexpected grant in state file

[dlouseiro]

Terraform CLI Version

1.5.7

Terraform Provider Version

0.92.0

Terraform Configuration

resource "snowflake_grant_privileges_to_account_role" "snowflake_imported_privileges_to_snowflake_monitoring" {
  account_role_name = snowflake_role.snowflake_monitoring.name
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
}

Category

category:grants

Object type(s)

resource:grant_privileges_to_account_role

Expected Behavior

For the resource to be registered in the state file as something like this:

    {
      "mode": "managed",
      "type": "snowflake_grant_privileges_to_account_role",
      "name": "snowflake_imported_privileges_to_snowflake_monitoring",
      "provider": "provider[\"registry.terraform.io/snowflake-labs/snowflake\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "account_role_name": "SNOWFLAKE_MONITORING",
            "all_privileges": false,
            "always_apply": false,
            "always_apply_trigger": null,
            "id": "\"SNOWFLAKE_MONITORING\"|false|false|IMPORTED PRIVILEGES|OnAccountObject|DATABASE|\"SNOWFLAKE\"",
            "on_account": false,
            "on_account_object": [
              {
                "object_name": "SNOWFLAKE",
                "object_type": "DATABASE"
              }
            ],
            "on_schema": [],
            "on_schema_object": [],
            "privileges": [
              "IMPORTED PRIVILEGES"
            ],
            "with_grant_option": false
          },
          "sensitive_attributes": [],
          "private": "bnVsbA==",
          "dependencies": [
            "snowflake_role.snowflake_monitoring"
          ]
        }
      ]
    }

Actual Behavior

Resource is stored in the state file as:

    {
      "mode": "managed",
      "type": "snowflake_grant_privileges_to_account_role",
      "name": "snowflake_imported_privileges_to_snowflake_monitoring",
      "provider": "provider[\"registry.terraform.io/snowflake-labs/snowflake\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "account_role_name": "SNOWFLAKE_MONITORING",
            "all_privileges": false,
            "always_apply": false,
            "always_apply_trigger": null,
            "id": "\"SNOWFLAKE_MONITORING\"|false|false|IMPORTED PRIVILEGES|OnAccountObject|DATABASE|\"SNOWFLAKE\"",
            "on_account": false,
            "on_account_object": [
              {
                "object_name": "SNOWFLAKE",
                "object_type": "DATABASE"
              }
            ],
            "on_schema": [],
            "on_schema_object": [],
            "privileges": [
              "IMPORTED PRIVILEGES",
              "USAGE"
            ],
            "with_grant_option": false
          },
          "sensitive_attributes": [],
          "private": "bnVsbA==",
          "dependencies": [
            "snowflake_role.snowflake_monitoring"
          ]
        }
      ]
    }

Steps to Reproduce

1. Remove resource from the state with terraform state rm 'snowflake_grant_privileges_to_account_role.snowflake_imported_privileges_to_snowflake_monitoring'
2. Run terraform apply (grant is reapplied)
3. Run terraform apply again

Result received is:

  # snowflake_grant_privileges_to_account_role.snowflake_imported_privileges_to_snowflake_monitoring will be updated in-place
  ~ resource "snowflake_grant_privileges_to_account_role" "snowflake_imported_privileges_to_snowflake_monitoring" {
        id                = "\"SNOWFLAKE_MONITORING\"|false|false|IMPORTED PRIVILEGES|OnAccountObject|DATABASE|\"SNOWFLAKE\""
      ~ privileges        = [
          - "USAGE",
            # (1 unchanged element hidden)
        ]
        # (5 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

Terraform considers that the USAGE grant should be removed, which makes sense as it was never declared in the first place, but somehow terraform stored it in the state.

4. Reply yes to the terraform apply. Result: command fails as it's not possible to revoke USAGE privilege on the SNOWFLAKE database.

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Fun fact here is that this only happens in our production account. If I run the exact same steps mentioned above in one of our test accounts, everything goes well and the second terraform apply (point 3) does not detect any changes in the state.

Workaround applied:

Added a lifecyle in the resource to ignore changes in privileges:

  lifecycle {
    ignore_changes = [
      privileges
    ]
  }

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-asawicki]

Hey @dlouseiro. Thanks for reaching out to us.

Could you please describe how did you migrate the production accounts (was it just v0.91.0 -> v0.92.0 or a bigger version bump? which option did you pick to migrate the old grant resources or did you not have to migrate them? etc. every detail that you can share will be potentially helpful here because we also cannot reproduce such a situation on our test accounts, so probably we need a better setup - the one closer to what you did on your prod account).

[dlouseiro]

Ok, in the meantime I figured out the issue, even though it's a weird one.

Basically, we have a role terraform (at the same level as accountadmin) that is used to execute terraform stuff that needs accountadmin privileges.

So when I was looking at the results of show grants on database snowflake (using accountadmin role manually in a console), I was seeing two duplicated grant records, one granted manually a long time ago by the accountadmin role (before we were even using terraform) and one granted by terraform when we terraformed our instance (example below):

[
  {
    "created_on": "2024-09-10 12:57:46.307 +00:00",
    "privilege": "USAGE",
    "granted_on": "APPLICATION",
    "name": "SNOWFLAKE",
    "granted_to": "ROLE",
    "grantee_name": "SNOWFLAKE_MONITORING",
    "grant_option": "false",
    "granted_by": "",
    "granted_by_role_type": ""
  },
  {
    "created_on": "2024-09-10 12:57:45.914 +00:00",
    "privilege": "USAGE",
    "granted_on": "APPLICATION",
    "name": "SNOWFLAKE",
    "granted_to": "ROLE",
    "grantee_name": "USAGE_MONITORING",
    "grant_option": "false",
    "granted_by": "",
    "granted_by_role_type": ""
  },
  {
    "created_on": "some-old-timestamp",
    "privilege": "USAGE",
    "granted_on": "APPLICATION",
    "name": "SNOWFLAKE",
    "granted_to": "ROLE",
    "grantee_name": "SNOWFLAKE_MONITORING",
    "grant_option": "false",
    "granted_by": "ACCOUNTADMIN",
    "granted_by_role_type": ""
  },
  {
    "created_on": "some-old-timestamp",
    "privilege": "USAGE",
    "granted_on": "APPLICATION",
    "name": "SNOWFLAKE",
    "granted_to": "ROLE",
    "grantee_name": "USAGE_MONITORING",
    "grant_option": "false",
    "granted_by": "ACCOUNTADMIN",
    "granted_by_role_type": ""
  }
]

When I looked at the permission in my test account this was not the case as these accounts are meant simply to run terraform so everything there is applied by terraform (never manually by accountadmin).

So, what did I do to make sure these permissions were properly registered in the state?

Using an accountadmin role, I had to run the following commands twice:

revoke imported privileges on database snowflake from role snowflake_monitoring;
revoke imported privileges on database snowflake from role usage_monitoring;

So the first time I ran the revokes, the result of show grants on database snowflake started to be 2 record instead of 4 (just the old grants done manually) and after the second revoke, no record to these two roles existed.

After these two revokes, I removed these properties from the state and re-ran terraform twice. The first run applied the missing permissions (which I had just revoked), the second run finally returned the expected No changes. Your infrastructure matches the configuration.!

[sfc-gh-jcieslak]

Hey 👋
I tried to reproduce the original issue but couldn't (it worked every time). The behavior in different environments also seems strange. Maybe you have different Terraform versions or different Snowflake versions/settings?

Regarding the second comment, I'm not really sure what you mean. I also tried to reproduce this behavior, but couldn't. Calling revoke twice when after the first one you have 2 grants, and after the second one none seems like a Snowflake error (stacking grants?).

Please try to create clearer steps to reproduce. The first step already removes the resource from the state, which is confusing. Create a step-by-step guide from no configuration on Snowflake and Terraform to the occurring issue.

[sfc-gh-jcieslak]

Hey @dlouseiro , please try to give us more information (comment above), otherwise I will close the ticket due to long inactivity.

[sfc-gh-jcieslak]

Closing due to long inactivity. Please create another ticket if the issue still persists.