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

[sfc-gh-jcieslak]

Hey @jonathanneo
As far as I remember this functionality (revoking on future) was postponed and is not supported yet. It is documented in the registry docs for grant ownership as a Warning. It is not complete because we were waiting for the confirmation from other team about the syntax. I can see that currently, the documentation was adjusted confirming that the revoke ownership can be used. This means we are able to close this feature gap. I'll discuss it with @sfc-gh-asawicki when we'll be able to tackle it.

[piyushbhojgude]

Hi team, I'm observing similar issue during terraform destroy of a row access policy ownership grant.
We are granting ownership of row access policy to SF_MAINTENANCE role.
But during destroy, provider is trying to grant it back to its parent role:

--During apply:
GRANT OWNERSHIP ON ROW ACCESS POLICY "MY_DB"."MY_SCHEMA"."MY_ROW_ACCESS_POLICY" TO ROLE "SF_MAINTENANCE" COPY CURRENT GRANTS --terraform_provider_usage_tracking {"json_schema_version":"1","version":"v1.0.1","resource":"snowflake_grant_ownership","operation":"create"}

-- During destroy
GRANT OWNERSHIP ON ROW ACCESS POLICY "MY_DB"."MY_SCHEMA"."MY_ROW_ACCESS_POLICY" TO ROLE "SF_SYSADMIN" COPY CURRENT GRANTS --terraform_provider_usage_tracking {"json_schema_version":"1","version":"v1.0.1","resource":"snowflake_grant_ownership","operation":"delete"}

Destroy is failing with below error:
SQL execution error: Ownership restriction violation in a managed access schema. Grantee need to be a subordinate role of the schema owner. How should this be handled ideally?

[sfc-gh-jcieslak]

Hey @piyushbhojgude
This is a separate issue from this one. Ideally, I would like you to create another ticket to continue the conversation there, but I'll answer you here. I don't have all the data to know what exactly happened in your case, but:

• Destroy behavior is correct (revoke ownership should be only called with on future option, in other cases it should grant ownership from the granted role to the currently used one)
• This may be happening because the currently used role by the provider is not privileged enough. I'm preparing a document about grant ownership that mentions this case, to grant the ownership in Snowflake you either have to own the object or be granted with MANAGE GRANTS privilege. The ACCOUNTADMIN has it builtin, so I'm assuming you are using custom role for the Terraform user. In this case, you have to grant it with MANAGE GRANTS and then proceed with the deletion. Currently, that is the only way, but we have some ideas how to improve it (more on that in the document that should be published next week here).

[piyushbhojgude]

Hey @sfc-gh-jcieslak , thanks for your reply. Yes you are correct we are using a custom role for the terraform user, but its equivalent to sysadmin user. I can confirm that this role has MANAGE GRANTS privilege. But still we face this issue. I believe it's got to do with managed schemas, and the way our role hierarchy is set up. Since SF_SYSADMIN is not a subordinate role of SF_MAINTENANCE, the the transfer of ownership is failing.

[sfc-gh-jcieslak]

@piyushbhojgude
Hmm yeah you're right. I tested this case with database, schema, and table created in the worksheet and the ownership transfer in the configuration. Let me know if that fixes your case. What I think was missing in your configuration was the snowflake_grant_account_role part which has to have explicit dependency set on the snowflake_grant_ownership otherwise the error happens. Here's the config:

provider "snowflake" {
  role = "SF_SYSADMIN" # the role has basic MANAGE GRANTS, CREATE DATABASE, and CREATE ROLE privileges
}

resource "snowflake_account_role" "test" {
  name = "TEST_ROLE"
}

resource "snowflake_grant_account_role" "test" {
  role_name = snowflake_account_role.test.name
  parent_role_name = "SF_SYSADMIN"
}

resource "snowflake_grant_ownership" "test" {
  depends_on = [ snowflake_grant_account_role.test ]

  account_role_name = snowflake_account_role.test.name
  on {
    object_type = "TABLE"
    # The objects were pre-created in the worksheet with the use of SF_SYSADMIN role
    object_name = "TEST_DATABASE.TEST_SCHEMA.TEST_TABLE"
  }
}