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

scizorman commented 4 months ago

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

terraform apply: The error described in Actual behaviors occurs.
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 commented 4 months ago

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 commented 4 months ago

cc: @sfc-gh-jcieslak

scizorman commented 4 months ago

@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.

Snowflake-Labs / terraform-provider-snowflake