[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 😎

[sfc-gh-asawicki]

Hey @jonathanneo. Thanks for reaching out to us.

We got back from the holiday break and we will verify the issue this week.

[sfc-gh-asawicki]

Hey @jonathanneo.

I am not an expert on RBAC in Snowflake but I have a few questions:

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.

This is not aligned, at least from my understanding, with the docs for managed schemas:

In managed schemas, the schema owner manages all privilege grants, including future grants,
on objects in the schema. Object owners retain the OWNERSHIP privileges on the objects;
however, only the schema owner can manage privilege grants on the objects.

As I understand it, these objects can still be owned by other roles (however, as you have shown, granting ownership to some role—in your case, ACCOUNTADMIN—fails with the referenced error).
The question here would be: do you need to transfer the ownership for ALL and FUTURE tables to the MY__OWNER role (asking it, because without it, we can avoid problems in the destroy in the presented case and maybe this ownership transfer is not needed - it can be needed too, I don't know what you want to accomplish).

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.

We will consider it. I also see another option to detect what kind of schema we are dealing with and dynamically adjust the statement run on destroy. However, it poses some challenges/questions (e.g., will we always see the schema kind?).

Nevertheless, I would like to wait for such an addition until the whole team is back from holidays and consult internally. Even if we don't decide to add it quickly, we will document this as a current limitation with potential workarounds.

cc: @sfc-gh-jcieslak

[sfc-gh-asawicki]

We will discuss it at the start of next week.

[jonathanneo]

Thanks for clarifying that in managed schemas, the schema owner manages all privilege grants, and object owners retain the OWNERSHIP privileges on the objects. I had misunderstood that point.

The question here would be: do you need to transfer the ownership for ALL and FUTURE tables to the MY__OWNER role

Yes, our RBAC model requires that we do so because we want to have a role scoped at the schema-level to own all objects underneath it e.g. tables and views.

Below is how we apply RBAC at my company.

namespace_level_role (owns all databases underneath a "namespace") 
|_ database_level_role (owns all schemas underneath a database) 
  |_ schema_level_role (owns all objects underneath a schema e.g. tables, views)