Changing snowflake_grant_privileges_to_role resource involving ownership fails due to dependent grants

[dmfay]

trafficstars

Provider 0.68.1

Here's my snowflake_grant_privileges_to_role resource:

resource "snowflake_grant_privileges_to_role" "admin_schema" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name])
  role_name         = local.role_admin
  privileges        = ["OWNERSHIP", "CREATE TABLE", "USAGE"]
  with_grant_option = false

  on_schema {
    # has to be qualified
    schema_name = "${snowflakedb.db.name}.${each.value}"
  }
}

This applied successfully once, but then I needed to modify the schema list in the for_each (specifically, I hardcoded PUBLIC as you see here instead of defining it as a resource). The plan shows all additions even though each grant already exists and is already managed by terraform. For example, here's one of the three schemata referenced in the resource declaration:

  # module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"] will be created
+ resource "snowflake_grant_privileges_to_role" "admin_schema" {
+       all_privileges    = false
+       id                = (known after apply)
+       on_account        = false
+       privileges        = [
+           "CREATE TABLE",
+           "OWNERSHIP",
+           "USAGE",
        ]
+       role_name         = "ADMIN_PRODUCTION"
+       with_grant_option = false

+       on_schema {
+           schema_name = "PROD.PUBLIC"
        }
    }

Application then fails due to dependent grant errors even though ownership has not actually been changed:

╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'USAGE' on securable 'PROD.SECURITY' to role 'READER_PRODUCTION' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵

(note a different role READER_PRODUCTION is affected in the second error!)

[dmfay]

I'm seeing related problems on clean applies as well when the ownership grant comes later in the DAG sort:

│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'STAGINGTEST.RAW' to role 'ADMIN_STAGING' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│ 
│   with module.snowflake_staging_env.snowflake_grant_privileges_to_role.admin_schema["RAW"],
│   on .terraform/modules/snowflake_staging_env/modules/snowflake_database/main.tf line 157, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  157: resource "snowflake_grant_privileges_to_role" "admin_schema" {

[emancu]

I'm having issues as well, but the plugin doesn't respond.

Versions

terragrunt version v0.35.13`

Terraform v1.4.6
on darwin_arm64
+ provider registry.terraform.io/hashicorp/aws v4.65.0
+ provider registry.terraform.io/hashicorp/random v3.5.1
+ provider registry.terraform.io/hashicorp/tls v4.0.4
+ provider registry.terraform.io/snowflake-labs/snowflake v0.68.1

Terraform

locals {
  consolidated_schema_grants = {
    "ownership" = {
      privileges         = ["OWNERSHIP"]
      role               = "SYSADMIN"
    }
    "read_only" = {
      privileges         = ["USAGE", "MONITOR"]
      role               = local.access_roles.read_only.name
    }
  }

resource "snowflake_grant_privileges_to_role" "consolidated_cdc__schema_grants" {
  for_each = local.consolidated_schema_grants

  privileges = each.value.privileges
  role_name  = each.value.role
  on_schema {
    schema_name = snowflake_schema.consolidated_cdc.name
  }

  depends_on = [ snowflake_role.access_role ]
}

Output

│ Error: Plugin did not respond
│
│   with module.raw_ingested_data.snowflake_grant_privileges_to_role.consolidated_cdc__schema_grants["read_write"],
│   on databases/raw_ingested_data/schema-consolidated_cdc.tf line 43, in resource "snowflake_grant_privileges_to_role" "consolidated_cdc__schema_grants":
│   43: resource "snowflake_grant_privileges_to_role" "consolidated_cdc__schema_grants" {
│
│ The plugin encountered an error, and failed to respond to the
│ plugin.(*GRPCProvider).ApplyResourceChange call. The plugin logs may
│ contain more details.
╵
╷
│ Error: Plugin did not respond
│
│   with module.raw_ingested_data.snowflake_grant_privileges_to_role.consolidated_cdc__schema_grants["stream_consumer"],
│   on databases/raw_ingested_data/schema-consolidated_cdc.tf line 43, in resource "snowflake_grant_privileges_to_role" "consolidated_cdc__schema_grants":
│   43: resource "snowflake_grant_privileges_to_role" "consolidated_cdc__schema_grants" {
│
│ The plugin encountered an error, and failed to respond to the
│ plugin.(*GRPCProvider).ApplyResourceChange call. The plugin logs may
│ contain more details.

Stack trace

  Stack trace from the terraform-provider-snowflake_v0.68.1 plugin:
  
  panic: runtime error: index out of range [1] with length 1
  
  goroutine 150 [running]:
  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/sdk.NewSchemaIdentifierFromFullyQualifiedName({0x14000755050?, 0x14000d3f950?})
	  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/sdk/identifier_helpers.go:149 +0xb8
  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources.configureAccountRoleGrantPrivilegeOptions(0x14000d38fc0?, {0x14000d4eaf0, 0x5, 0x5}, 0xa0?, 0x140006ecd98)
	  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources/grant_privileges_to_role.go:776 +0x11ec
  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources.CreateGrantPrivilegesToRole(0x0?, {0x105acb080?, 0x140008e51e0})
	  github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources/grant_privileges_to_role.go:450 +0x144
  github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).create(0x105af8940?, {0x105af8940?, 0x14000d2e720?}, 0xd?, {0x105acb080?, 0x140008e51e0?})
	  github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/resource.go:695 +0x134
  github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).Apply(0x140007af260, {0x105af8940, 0x14000d2e720}, 0x14000d3a000, 0x14000c25180, {0x105acb080, 0x140008e51e0})
	  github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/resource.go:837 +0x86c
  github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*GRPCProviderServer).ApplyResourceChange(0x140007e63c0, {0x105af8940?, 0x14000d2e600?}, 0x14000a6c050)
	  github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/grpc_provider.go:1021 +0xb70
  github.com/hashicorp/terraform-plugin-go/tfprotov5/tf5server.(*server).ApplyResourceChange(0x14000098000, {0x105af8940?, 0x14000c1d7a0?}, 0x14000238770)
	  github.com/hashicorp/[email protected]/tfprotov5/tf5server/server.go:818 +0x3b8
  github.com/hashicorp/terraform-plugin-go/tfprotov5/internal/tfplugin5._Provider_ApplyResourceChange_Handler({0x105a8ddc0?, 0x14000098000}, {0x105af8940, 0x14000c1d7a0}, 0x14000238700, 0x0)
	  github.com/hashicorp/[email protected]/tfprotov5/internal/tfplugin5/tfplugin5_grpc.pb.go:385 +0x170
  google.golang.org/grpc.(*Server).processUnaryRPC(0x1400017e960, {0x105aff520, 0x14000582d00}, 0x14000af8240, 0x14000891320, 0x106451240, 0x0)
	  google.golang.org/[email protected]/server.go:1336 +0xb7c
  google.golang.org/grpc.(*Server).handleStream(0x1400017e960, {0x105aff520, 0x14000582d00}, 0x14000af8240, 0x0)
	  google.golang.org/[email protected]/server.go:1704 +0x82c
  google.golang.org/grpc.(*Server).serveStreams.func1.2()
	  google.golang.org/[email protected]/server.go:965 +0x84
  created by google.golang.org/grpc.(*Server).serveStreams.func1
	  google.golang.org/[email protected]/server.go:963 +0x290
  
  Error: The terraform-provider-snowflake_v0.68.1 plugin crashed!
  
  This is always indicative of a bug within the plugin. It would be immensely
  helpful if you could report the crash with the plugin's maintainers so that it
  can be fixed. The output above should help diagnose the issue.
  
  ERRO[0856] 1 error occurred:
	  * exit status 1

[dmfay]

@emancu I'm not pronouncing it a solution yet but initial results have been promising -- I've split up the grant_privileges_to_role resources so a dependent grant (e.g. usage + create table) depends_on the ownership grant.

The specific problem you're having though is because the on_schema schema_name has to be fully qualified now: ${snowflake_database.db.name}.${snowflake_schema.schema.name}

[emancu]

@dmfay Thanks for your hint! How did you infer that from the error? 🤔

[dmfay]

@dmfay Thanks for your hint! How did you infer that from the error? 🤔

the exact same thing happened to me earlier this week after I upgraded to 0.68 and missed updating in a couple of places 😄

[ceik]

I ran into the same issue, but it seems to have been caused by a mistake of mine: I applied grants to a database without first removing all grants granted by the old snowflake_database_grant. I had applied exactly the same permission to another database before and did not have this issue. I assume it was because I first removed all permissions to that database.

Ownership and usage grant were already separate resources and adding the depends_on did not work in my case. I ended up dropping the database and recreating it. That "solved" it for me.

[dmfay]

Splitting the schema grants works well for setup, but teardown fails to unroll them during execution:

resource "snowflake_grant_privileges_to_role" "admin_schema_ownership" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name, snowflake_schema.mart.name])
  role_name         = local.role_admin
  privileges        = ["OWNERSHIP"]
  with_grant_option = false

  on_schema { schema_name = "${snowflake_database.db.name}.${each.value}" }
  lifecycle { ignore_changes = [ privileges ] }
}

resource "snowflake_grant_privileges_to_role" "admin_schema" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name]) # no mart here!
  role_name         = local.role_admin
  privileges        = ["CREATE TABLE", "USAGE"]
  with_grant_option = false

  depends_on = [snowflake_grant_privileges_to_role.admin_schema_ownership]

  on_schema { schema_name = "${snowflake_database.db.name}.${each.value}" }
  lifecycle { ignore_changes = [ privileges ] }
}

module.test_environment.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.PUBLIC|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["RAW"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.RAW|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["RAW"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["SECURITY"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.SECURITY|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["SECURITY"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["MART"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.MART|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["PUBLIC"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.PUBLIC|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["RAW"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.RAW|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["SECURITY"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.SECURITY|false|]

│ Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set
│ Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set
│ Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set
│ Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set

terraform planned things in the correct order -- wind down the usage and create-table grants in the public, raw, and security schemata, then destroy the ownership grants the former depend on in those three and the additional mart schema. The first step is successful and drops the three dependent grants. The second, dropping the four ownership grants, fails (including for mart which did not have a dependent grant!).

Other grants are removed correctly: all-tables-in-schema, future-tables-in-schema, database usage. Most of those do have explicit depends_on set, but not to grants of the same type.

The last thing I see in the query log is REVOKE ALL PRIVILEGES ON DATABASE "STAGING_TEST_DB" FROM ROLE "ADMIN_STAGING". Since the "exactly one of" message is from the provider code, I suspect that the REVOKE ALL PRIVILEGES is coming from the revocation of the dependent grants, leaving no valid targets for the revocation of the ownership grant.

I did upgrade to 0.68.2 on seeing #1953, but while that should fix the privileges churn I had to set ignore directives for, the teardown problem still occurs.

[sfc-gh-swinkler]

This snowflake_grant_privileges_to_role resource wasn't intended to work with the special ownership privilege because ownership cannot be revoked in the same way that grant privileges can; ownership can only assigned to a different role. We intend to add a new snowflake_grant_ownership resource to handle ownership permissions. In the meantime, I recommend splitting up the resource into two resources, one which manages ownership and one which manages all other permissions, as @dmfay suggests.

[jasonicarter]

Is there a workaround here I'm not seeing? I'm not setting ownership but I am using all and future. During any destroy action, I'm always getting stuck with the error below. I then have to run terraform state rm against the grants, then terraform apply -destroy will work so I can then terraform apply to retest. I'm running this test in a new Snowflake account so there's no pre-existing resources such as grants causing problems.

@sfc-gh-swinkler you comment seems to imply that using what @dmfay mentioned would fix the ownership issue but @dmfay mentioned in the last paragraph the the teardown problem still occurs.

Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set

[trabianmatt]

I'm getting the same teardown error and am only using select privilege on a table.

[chrisweis]

I'm so confused by these errors on "destroy" and wish this Snowflake Terraform provider was easier to use 😰

[sfc-gh-jcieslak]

Hey @dmfay @emancu @ceik @jasonicarter @trabianmatt @chrisweis This seems like an issue with the OWNERSHIP privilege. In the newest snowflake_grant_privileges_to_account_role resource (which is now recommended instead of snowflake_grant_privileges_to_role) the OWNERSHIP privilege is not allowed for that reason. Luckily as you can see there's an open pr (probably 1st out of two) to introduce ownership-granting resource. That resource should resolve this issue, so stay tuned :)

[sfc-gh-jcieslak]

Hey 👋 Closing, as the issue was about the deprecated resources. Recently, we released a new grant resource which is capable of granting ownership. Please, give it a try. If there will be any issues with it, create another GitHub issue. Also, please check our technical documentation section where you can find a migration guide that can help you with upgrading to the latest grant resources and our newly added design decision doc (regarding new grant resources).

[pauldraper]

New source has the same issue

[sfc-gh-jcieslak]

Hey @pauldraper If you see any issues with the existing resources, please create a new bug report with the whole description.