Failed to grant privileges to COMPUTE POOL using 'snowflake_grant_privileges_to_account_role' resource

[scizorman]

Terraform CLI and Provider Versions

• Terraform: 1.8.0
• Snowflake Provider: 0.88.0

Terraform Configuration

resource "snowflake_role" "dev_zucks_dashboard_pool_usage_access_role" {
  provider = snowflake.useradmin

  name = "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"
}

resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role" {
  provider = snowflake.securityadmin

  account_role_name = snowflake_role.dev_zucks_dashboard_pool_usage_access_role.name
  privileges        = ["USAGE"]

  on_account_object {
    object_type = "COMPUTE POOL"
    object_name = "DEV_ZUCKS_MANAGEMENT_POOL"
  }
}

Expected Behavior

The following SQL is executed and the grant of USAGE privileges to COMPUTE POOL succeeds.

GRANT USAGE ON COMPUTE POOL DEV_ZUCKS_MANAGEMENT_POOL TO ROLE DEV_ZUCKS_MANAGEMENT_POOL_USAGE;

Actual Behavior

It appears that before executing the GRANT statement, an attempt is being made to check the existing GRANTS by running SHOW GRANTS ON .... However, the statement SHOW GRANTS ON COMPUTE POOL <object_name> does not exist in Snowflake.

As a result, the query that actually gets executed is SHOW GRANTS ON, which leads to a syntax error, causing the terraform apply to fail.

The console log is below.

...
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_this_usage_role will be created
  + resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role" {
      + account_role_name = "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"
      + all_privileges    = false
      + always_apply      = false
      + id                = (known after apply)
      + on_account        = false
      + privileges        = [
          + "USAGE",
        ]
      + with_grant_option = false

      + on_account_object {
          + object_name = "DEV_ZUCKS_MANAGEMENT_POOL"
          + object_type = "COMPUTE POOL"
        }
    }

  # snowflake_role.dev_zucks_dashboard_pool_usage_access_role will be created
  + resource "snowflake_role" "dev_zucks_dashboard_pool_usage_access_role" {
      + id   = (known after apply)
      + name = "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"
    }

Plan: 2 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

snowflake_role.dev_zucks_dashboard_pool_usage_access_role: Creating...
snowflake_role.dev_zucks_dashboard_pool_usage_access_role: Creation complete after 0s [id=DEV_ZUCKS_MANAGEMENT_POOL_USAGE]
snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_this_usage_role: Creating...
╷
│ Error: Failed to retrieve grants
│
│   with snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_this_usage_role,
│   on compute_pool.tf line 7, in resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role":
│    7: resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role" {
│
│ Id: "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"|false|false|USAGE|OnAccountObject||
│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 14 unexpected '<EOF>'.

Steps to Reproduce

1. terraform apply: The error described in Actual behaviors occurs.
2. terraform plan: The error described below occurs. And the Terraform state is broken.

...
Planning failed. Terraform encountered an error while generating this plan.

╷
│ Error: Failed to retrieve grants
│
│   with snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_this_usage_role,
│   on compute_pool.tf line 7, in resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role":
│    7: resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_this_usage_role" {
│
│ Id: "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"|false|false|USAGE|OnAccountObject||
│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 14 unexpected '<EOF>'.

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

No response

[sfc-gh-asawicki]

Hey @scizorman. Thanks for reporting the issue.

This looks like a bug/missing feature in Snowflake.

From the provider side, we have to be able to run read (SHOW GRANTS ON) to be able to calculate the difference and plan the terraform actions accordingly. We will check if the COMPUTE POOL grants are visible in any other SHOW GRANTS commands. If so, we will consider introducing a workaround. We will also raise this issue on Snowflake side but it would help if you could file this as a bug too (i.e. not being able to run SHOW GRANTS ON COMPUTE POOL <object>).

[sfc-gh-asawicki]

cc: @sfc-gh-jcieslak

[scizorman]

@sfc-gh-asawicki
Thanks for your reply.

We will also raise this issue on Snowflake side but it would help if you could file this as a bug too (i.e. not being able to run SHOW GRANTS ON COMPUTE POOL <object>).

I submitted a request to the Snowflake support case, asking them to enable the execution of SHOW GRANTS ON COMPUTE POOL <object_name>. I have also informed them about this GitHub issue.

[awilliamsOM1]

I think this is a problem with the resource id stored in the terraform provider. I think the compute pool name is not getting added properly to the terraform resource id. In the above example's error message the id of the snowflake_grant_privileges_to_account_role is

│ Id: "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"|false|false|USAGE|OnAccountObject||

The compute pool name seems to be missing from the id. I would expect the snowflake_grant_privileges_to_account_role id to be "DEV_ZUCKS_MANAGEMENT_POOL_USAGE"|false|false|USAGE|OnAccountObject|DEV_ZUCKS_MANAGEMENT_POOL|

I think the provider is failing to create a valid terraform id in state for the ``snowflake_grant_privileges_to_account_rolewhen the account object type isCOMPUTE POOL`.

This looks to be a provider issue to me instead of a snowflake issue.

SHOW GRANTS ON COMPUTE POOL <object> is a valid snowflake command that I have confirmed.

[sfc-gh-asawicki]

Hey @awilliamsOM1 @scizorman. We've released a new v0.98.0 version (release, migration guide) containing the fix for the COMPUTE POOL grants. Please check if this solves the problem for you.

[scizorman]

@sfc-gh-asawicki
Thank you for the heads-up on v0.98.0. I've tested it, and I can confirm that the snowflake_grant_privileges_to_account_role operation is now working successfully with COMPUTE POOL grants. The fix is working as expected.

terraform {
  required_version = "1.9.8"

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

provider "snowflake" {}

provider "snowflake" {
  alias = "accountadmin"

  role = "ACCOUNTADMIN"
}

provider "snowflake" {
  alias = "securityadmin"

  role = "SECURITYADMIN"
}

provider "snowflake" {
  alias = "useradmin"

  role = "USERADMIN"
}

resource "snowflake_unsafe_execute" "test_compute_pool" {
  provider = snowflake.accountadmin

  execute = <<SQL
CREATE COMPUTE POOL TEST_POOL
  INSTANCE_FAMILY = CPU_X64_XS
  MIN_NODES = 1
  MAX_NODES = 1
  INITIALLY_SUSPENDED = TRUE
  AUTO_SUSPEND_SECS = 3600
SQL
  revert  = "DROP COMPUTE POOL TEST_POOL"
}

resource "snowflake_account_role" "test_compute_pool_usage_access_role" {
  provider = snowflake.useradmin

  name    = "TEST_POOL_USAGE"
  comment = "Access role that has USAGE privilege on the compute pool TEST_POOL"
}

resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_test_compute_pool_usage_access_role" {
  depends_on = [snowflake_unsafe_execute.test_compute_pool]
  provider   = snowflake.securityadmin

  account_role_name = snowflake_account_role.test_compute_pool_usage_access_role.name
  privileges        = ["USAGE"]

  on_account_object {
    object_type = "COMPUTE POOL"
    object_name = "TEST_POOL"
  }
}

...

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # snowflake_account_role.test_compute_pool_usage_access_role will be created
  + resource "snowflake_account_role" "test_compute_pool_usage_access_role" {
      + comment              = "Access role that has USAGE privilege on the compute pool TEST_POOL"
      + fully_qualified_name = (known after apply)
      + id                   = (known after apply)
      + name                 = "TEST_POOL_USAGE"
      + show_output          = (known after apply)
    }

  # snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_test_compute_pool_usage_access_role will be created
  + resource "snowflake_grant_privileges_to_account_role" "grant_usage_privilege_to_test_compute_pool_usage_access_role" {
      + account_role_name = "TEST_POOL_USAGE"
      + all_privileges    = false
      + always_apply      = false
      + id                = (known after apply)
      + on_account        = false
      + privileges        = [
          + "USAGE",
        ]
      + with_grant_option = false

      + on_account_object {
          + object_name = "TEST_POOL"
          + object_type = "COMPUTE POOL"
        }
    }

  # snowflake_unsafe_execute.test_compute_pool will be created
  + resource "snowflake_unsafe_execute" "test_compute_pool" {
      + execute       = <<-EOT
            CREATE COMPUTE POOL TEST_POOL
              INSTANCE_FAMILY = CPU_X64_XS
              MIN_NODES = 1
              MAX_NODES = 1
              INITIALLY_SUSPENDED = TRUE
              AUTO_SUSPEND_SECS = 3600
        EOT
      + id            = (known after apply)
      + query_results = (known after apply)
      + revert        = "DROP COMPUTE POOL TEST_POOL"
    }

Plan: 3 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

snowflake_account_role.test_compute_pool_usage_access_role: Creating...
snowflake_account_role.test_compute_pool_usage_access_role: Creation complete after 0s [id=TEST_POOL_USAGE]
snowflake_unsafe_execute.test_compute_pool: Creating...
snowflake_unsafe_execute.test_compute_pool: Creation complete after 0s [id=5707da03-5296-3ce8-7c79-6c6292b0f719]
snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_test_compute_pool_usage_access_role: Creating...
snowflake_grant_privileges_to_account_role.grant_usage_privilege_to_test_compute_pool_usage_access_role: Creation complete after 0s [id="TEST_POOL_USAGE"|false|false|USAGE|OnAccountObject|COMPUTE POOL|"TEST_POOL"]

Apply complete! Resources: 3 added, 0 changed, 0 destroyed.

[sfc-gh-asawicki]

Glad to hear that @scizorman! I will close the issue then.