[Bug]: Error occurred when transferring ownership back to the original role

[jonathanneo]

Terraform CLI Version

1.9.0

Terraform Provider Version

1.0.1

Company Name

No response

Terraform Configuration

# create owner role
resource "snowflake_account_role" "owner_role" { 
  name = "MY__OWNER"
}

# grant owner role to deployment role
resource "snowflake_grant_account_role" "owner_role_to_deployment_role" {
  role_name        = snowflake_account_role.owner_role.name
  parent_role_name = "ACCOUNTADMIN"
}

# create database 
resource "snowflake_database" "database" {
  depends_on = [snowflake_grant_account_role.owner_role_to_deployment_role]
  name = "MY_DB"
}

# grant ownership of database to owner role
resource "snowflake_grant_ownership" "database_to_owner" {
  account_role_name = snowflake_account_role.owner_role.name
  outbound_privileges = "REVOKE"
  on {
    object_type = "DATABASE"
    object_name = snowflake_database.database.name
  }
}

# create schema 
resource "snowflake_schema" "my_schema" {
  name = "MY_SCHEMA"
  with_managed_access = true
  database = snowflake_database.database.name
}

# grant ownership of schema to owner role 
resource "snowflake_grant_ownership" "schema_to_owner" {
  account_role_name = snowflake_account_role.owner_role.name
  on {
    object_type = "SCHEMA"
    object_name = snowflake_schema.my_schema.fully_qualified_name
  }
  outbound_privileges = "REVOKE"
}

# grant ownership on all tables 
resource "snowflake_grant_ownership" "all_tables" {
  depends_on = [snowflake_grant_ownership.schema_to_owner]
  outbound_privileges = "REVOKE"
  account_role_name = snowflake_account_role.owner_role.name
  on {
    all {
      object_type_plural = "TABLES"
      in_schema          = snowflake_schema.my_schema.fully_qualified_name
    }
  }
}

# grant ownership on future tables 
resource "snowflake_grant_ownership" "all_tables" {
  depends_on = [snowflake_grant_ownership.schema_to_owner]
  outbound_privileges = "REVOKE"
  account_role_name = snowflake_account_role.owner_role.name
  on {
    future {
      object_type_plural = "TABLES"
      in_schema          = snowflake_schema.my_schema.fully_qualified_name
    }
  }
}

Category

category:grants

Object type(s)

No response

Expected Behavior

I expect to perform apply and then destroy without any issues.

Actual Behavior

The apply succeeds.

However, the destroy fails with the following error:

Error: An error occurred when transferring ownership back to the original role

SQL execution error: Ownership restriction violation in a managed access schema. Grantee need to be a subordinate role of the schema owner.

This is because the provider attempts to transfer ownership back to the deployment role (e.g. ACCOUNTADMIN) whilst the schema is still owned by the MY__OWNER role:

grant ownership on future tables in schema MY_DB.MY_SCHEMA to role accountadmin revoke current grants;

I understand that this provider behaviour is intended, as ownership was first transferred from ACCOUNTADMIN to MY__OWNER role during the apply, and the destroy is just doing the reverse.

However, during a destroy, this provider behaviour of transferring ownership back to ACCOUNTADMIN conflicts with Snowflake's behaviour for managed access schemas. With managed access schemas, Snowflake requires that ownership of child objects of a schema (e.g. tables and views) must belong to the role that currently owns the schema (i.e. MY__OWNER) or a child role under the role that owns the schema.

As a workaround, I had to use a custom snowflake_execute resource to perform a simple grant and revoke ownership, instead of the snowflake_grant_ownership resource which attempts to transfer ownership during a destroy:

resource "snowflake_execute" "ownership_future_tables_in_schema_from_owner" {
  depends_on = [snowflake_grant_ownership.schema_to_owner]
  execute = "GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA ${snowflake_schema.my_schema.fully_qualified_name} TO ROLE ${snowflake_account_role.owner_role.name} REVOKE CURRENT GRANTS"
  revert  = "REVOKE OWNERSHIP ON FUTURE TABLES IN SCHEMA ${snowflake_schema.my_schema.fully_qualified_name} FROM ROLE ${snowflake_account_role.owner_role.name}"
}

Would it be possible to add a flag to the snowflake_grant_ownership resource for something like transfer_ownership = false so that instead of transferring ownership back to the deployment role (e.g. accountadmin), it just does a simple revoke.

Steps to Reproduce

1. Do a terraform apply
2. Do a terraform destroy

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎