[Bug]: Destroying schema ownership grant fails unless the owning role is assigned to the provider's role

[QuintenBruynseraede]

Terraform CLI Version

1.3.10

Terraform Provider Version

0.92.0

Terraform Configuration

provider "snowflake" {
  account       = <redacted>
  user          = <redacted>
  role          = "ACCOUNT_ADMIN"
  authenticator = "JWT"
  private_key   = <redacted>
}

resource "snowflake_database" "db" {
  name    = "TEST_DB"
}

resource "snowflake_role" "db_owner" {
  name    = "DB_OWNER"
}

resource "snowflake_grant_ownership" "grant_schema_ownership" {
  account_role_name   = snowflake_role.db_owner.name
  outbound_privileges = "COPY"
  on {
    all {
      object_type_plural = "SCHEMAS"
      in_database        = snowflake_database.db.name
    }
  }
}

resource "snowflake_schema" "schema" {
  database            = snowflake_database.db.name
  name                = "TEST_SCHEMA"
  is_managed          = true
}

resource "snowflake_grant_ownership" "table_ownership" {
  account_role_name   = <some other role>
  outbound_privileges = "COPY"
  on {
    all {
      object_type_plural = "TABLES"
      in_schema          = "TEST_DB.TEST_SCHEMA"
    }
  }
}

Category

category:resource

Object type(s)

No response

Expected Behavior

We should be able to create and destroy these resources without errors.

Actual Behavior

When destroying the table ownership resource, the provider will reassign ownership of the schema's objects (tables, streams, ...) to the ACCOUNT_ADMIN role (which is the role used to init the provider). Because in this setup, the original owner (DB_OWNER) is not granted to the ACCOUNT_ADMIN role, Snowflake rejects this.

╷
│ Error: An error occurred when transferring ownership back to the original role
│
│ Id: ToAccountRole|"DB_OWNER"|COPY|OnAll|TABLES|InSchema|"TEST_DB"."TEST_SCHEMA"
│ Error: 003514 (23001): SQL execution error: Ownership restriction violation in a managed access schema. Grantee need to be a subordinate role of the schema owner.
╵

Steps to Reproduce

1. Apply the configuration
2. Destroy the configuration

How much impact is this issue causing?

Low

Logs

No response

Additional Information

In previous versions of the provider, we granted ownership on schema objects (e.g. tables) using snowflake_schema_grant, which had an attribute revert_ownership_to_role_name. That attribute allowed you to specify a role to which ownership would be granted on delete. The new snowflake_grant_ownership resource no longer supports this, but instead always sets ownership to the role passed at provider initialization.

For this use case, the addition of a similar attribute would be a good solution.

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @QuintenBruynseraede 👋
I believe it fails due to one of the points in (grant ownership usage notes):

A role that has the MANAGE GRANTS privilege can transfer ownership of an object to any role; in contrast, a role that does not have the MANAGE GRANTS privilege can only transfer ownership from itself to a child role within the role hierarchy.

If you use ACCOUNTADMIN or a custom role with MANAGE GRANTS granted, it should work properly. That's certainly something that should be included in the documentation for the new snowflake_grant_ownership. The revert_ownership_to_role_name parameter in the new resource is also something that we will consider adding in the later versions of the provider. It was already in our design document, but for simplicity, it wasn't added to the initial version of grant ownership resource. Currently, we mostly focus on providing features needed for V1. For now, please try to use a more privileged role for ownership management, and I'll try to update the documentation in the meantime. Will that work for you?

You also checked the Would you like to implement a fix checkbox. Before making a pull request, we strongly recommend reading our contributing guidelines and discussing the implementation details beforehand in the issue, so we'll be more aligned on what is the preferred solution/approach.