snowflake_pipe_grant on_all option

[alexandrewatercare]

Is your feature request related to a problem? Please describe.

We can't use the on_all parameter for the snowflake_pipe_grant. This parameter is for all other resource type but not for this one. We have the on_future but not on_all

Describe the solution you'd like

The on_all parameter is available

Describe alternatives you've considered

We can use the data sources snowflake_pipes and loop ourself on all existing pipe but it's harder to maintain.

[sfc-gh-swinkler]

Have you tried the snowflake_grant_privileges_to_role resource to accomplish this? Documentation link:
https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_role

Here would be an example use case:

resource "snowflake_grant_privileges_to_role" "g" {
  privileges = ["MONITOR", "OPERATE"]
  role_name  = snowflake_role.r.name
  on_schema_object {
    all {
      object_type_plural = "PIPES"
      in_schema          = "\"my_db\".\"my_schema\"" # note this is a fully qualified name!
    }
  }
}

[alexandrewatercare]

maybe we have a new release but last time I tried, it didn't work because we cannot do a grant ... on all pipes in ... to role ... in Snowflake, we can do a grant on future but not on all. So we have to do a loop. I think the snowflake_grant_privileges_to_role should have a condition if it's plural = "pipes", it should get the list of all pipes and grant individually or something like that.

[fabien-sarcel]

Hello @sfc-gh-swinkler

I got error Bulk grant on objects of type PIPE to ROLE is restricted when I try to use snowflake_grant_privileges_to_role with PIPES.

According to Snowflake documentation, it's not allowed:

Note that bulk grants on pipes are not allowed

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege#required-parameters

[sfc-gh-jcieslak]

Hey @alexandrewatercare @fabien-sarcel
Thanks for reporting / commenting on this issue. We're right now in the process of re-designing snowflake_grant_privileges_to_role. I'll make sure that the new validation for pipes in on_schema_object.all.object_type_plural will be included. As a workaround, have you tried using Terraform for_each syntax with on_schema_object { object_type + object_name }?

The config should look similar to this

resource "snowflake_grant_privileges_to_role" "g" {
  privileges = ["MONITOR", "OPERATE"]
  role_name  = snowflake_role.r.name
  for_each = toset(["pipe1", "pipe2", "pipe3"])
  on_schema_object {
    object_type = "PIPE"
    object_name = each.key
  }
}

[fabien-sarcel]

Hi @sfc-gh-jcieslak ,

I got a runtime error when I try your workaround.

Stack trace from the terraform-provider-snowflake_v0.70.1 plugin:

panic: runtime error: index out of range [1] with length 1

goroutine 201 [running]:
github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/sdk.NewSchemaObjectIdentifierFromFullyQualifiedName({0xc000a94dd0?, 0xc0008bb590?})
	github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/sdk/identifier_helpers.go:206 +0x46a
github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources.configureAccountRoleGrantPrivilegeOptions(0xc0007a2d00?, {0x0, 0x0, 0x0}, 0x1?, 0xc00112ae00)
	github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources/grant_privileges_to_role.go:743 +0xd85
github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources.CreateGrantPrivilegesToRole(0x0?, {0x2698700?, 0xc0007a2d00?})
	github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources/grant_privileges_to_role.go:453 +0x1ad
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).create(0x29ad780?, {0x29ad780?, 0xc000af9b00?}, 0xd?, {0x2698700?, 0xc0007a2d00?})
	github.com/hashicorp/terraform-plugin-sdk/v2@v2.26.1/helper/schema/resource.go:695 +0x178
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).Apply(0xc000552e00, {0x29ad780, 0xc000af9b00}, 0xc000641790, 0xc0005e2e80, {0x2698700, 0xc0007a2d00})
	github.com/hashicorp/terraform-plugin-sdk/v2@v2.26.1/helper/schema/resource.go:837 +0xa85
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*GRPCProviderServer).ApplyResourceChange(0xc000b8f998, {0x29ad780?, 0xc000af99e0?}, 0xc00012f180)
	github.com/hashicorp/terraform-plugin-sdk/v2@v2.26.1/helper/schema/grpc_provider.go:1021 +0xe8d
github.com/hashicorp/terraform-plugin-go/tfprotov5/tf5server.(*server).ApplyResourceChange(0xc0000bb180, {0x29ad780?, 0xc000af8090?}, 0xc000522540)
	github.com/hashicorp/terraform-plugin-go@v0.14.3/tfprotov5/tf5server/server.go:818 +0x574
github.com/hashicorp/terraform-plugin-go/tfprotov5/internal/tfplugin5._Provider_ApplyResourceChange_Handler({0x264be80?, 0xc0000bb180}, {0x29ad780, 0xc000af8090}, 0xc0005224d0, 0x0)
	github.com/hashicorp/terraform-plugin-go@v0.14.3/tfprotov5/internal/tfplugin5/tfplugin5_grpc.pb.go:385 +0x170
google.golang.org/grpc.(*Server).processUnaryRPC(0xc0004e0b40, {0x29bcfc0, 0xc0006829c0}, 0xc000eced80, 0xc000b9eba0, 0x35f0180, 0x0)
	google.golang.org/grpc@v1.53.0/server.go:1336 +0xd33
google.golang.org/grpc.(*Server).handleStream(0xc0004e0b40, {0x29bcfc0, 0xc0006829c0}, 0xc000eced80, 0x0)
	google.golang.org/grpc@v1.53.0/server.go:1704 +0xa36
google.golang.org/grpc.(*Server).serveStreams.func1.2()
	google.golang.org/grpc@v1.53.0/server.go:965 +0x98
created by google.golang.org/grpc.(*Server).serveStreams.func1
	google.golang.org/grpc@v1.53.0/server.go:963 +0x28a

Error: The terraform-provider-snowflake_v0.70.1 plugin crashed!

[sfc-gh-jcieslak]

@fabien-sarcel because I've given you an example usage, not a complete one that will work on your environment. You'll have to replace a source that you're giving to for_each field and this source should contain valid identifiers (it can be other things than toSet(), you could probably make a setup where those values would come from pipes data source). I'm aware that the error message is not clear, but it basically means that it requires a fully qualified name of the schema object, so "<schema_name>.<schema_object_name>".

resource "snowflake_grant_privileges_to_role" "g" {
  privileges = ["MONITOR", "OPERATE"]
  role_name  = snowflake_role.r.name
  for_each = toset(["<schema_name>.<schema_object_name>", "<schema_name>.<schema_object_name>"])
  on_schema_object {
    object_type = "PIPE"
    object_name = each.key
  }
}

[fabien-sarcel]

Thanks @sfc-gh-swinkler.
Indeed I was using name attribut of pipes data sources in my for_each, so I needed to adapt object_name value to represent a fully qualified name. Now this workaround is working well.

[sfc-gh-jcieslak]

@fabien-sarcel Great to hear! Is that ok to close this issue then?

[fabien-sarcel]

I didn't open it, but from my point of view I'll say yes.

[sfc-gh-jcieslak]

@alexandrewatercare Right now, we're working on designing / re-designing and we're considering the approach you've mentioned. For now, please use for_each, but we can leave the issue open and we'll get back to it once it is implemented or we'll have a decision about not implementing it.

[sfc-gh-jcieslak]

Hey 👋, working on_all pipes will be merged soon in #2477 and will be available in the next release. As soon as the latest version appears (next week), please let us know if it fixes the bug and the issue can be closed. Until that happens suggested for_each workaround should be used.

[sfc-gh-jcieslak]

Closing due to inactivity of the author (fix confirmed fabien-sarcel). If the issue persists, please create another ticket.