terraform-provider-snowflake: Changing snowflake_grant_privileges_to_role resource involving ownership fails due to dependent grants
Provider 0.68.1
Here’s my snowflake_grant_privileges_to_role resource:
resource "snowflake_grant_privileges_to_role" "admin_schema" {
for_each = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name])
role_name = local.role_admin
privileges = ["OWNERSHIP", "CREATE TABLE", "USAGE"]
with_grant_option = false
on_schema {
# has to be qualified
schema_name = "${snowflakedb.db.name}.${each.value}"
}
}
This applied successfully once, but then I needed to modify the schema list in the for_each (specifically, I hardcoded PUBLIC as you see here instead of defining it as a resource). The plan shows all additions even though each grant already exists and is already managed by terraform. For example, here’s one of the three schemata referenced in the resource declaration:
# module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"] will be created
+ resource "snowflake_grant_privileges_to_role" "admin_schema" {
+ all_privileges = false
+ id = (known after apply)
+ on_account = false
+ privileges = [
+ "CREATE TABLE",
+ "OWNERSHIP",
+ "USAGE",
]
+ role_name = "ADMIN_PRODUCTION"
+ with_grant_option = false
+ on_schema {
+ schema_name = "PROD.PUBLIC"
}
}
Application then fails due to dependent grant errors even though ownership has not actually been changed:
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│
│ with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"],
│ on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│ 165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'USAGE' on securable 'PROD.SECURITY' to role 'READER_PRODUCTION' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│
│ with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│ on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│ 165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.
│
│ with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│ on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│ 165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
(note a different role READER_PRODUCTION is affected in the second error!)
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Reactions: 2
- Comments: 15 (3 by maintainers)
Commits related to this issue
- feat: Add snowflake grant ownership resource (#2604) The first part of the implementation of the `snowflake_grant_ownership` resource. This is a "basic" version of this resource providing baseline ... — committed to snowflakedb/terraform-provider-snowflake by sfc-gh-jcieslak 3 years ago
- chore(main): release 0.87.3-pre (#2605) :robot: I have created a release *beep* *boop* --- ## [0.87.3-pre](https://github.com/Snowflake-Labs/terraform-provider-snowflake/compare/v0.87.2...v0.8... — committed to snowflakedb/terraform-provider-snowflake by snowflake-release-please[bot] 3 years ago
- feat: Grant ownership follow up (#2628) A follow-up for #2604. Done in this pr: - Add setId("") in Read (when ownership is not found on the target object) and forcefully grant ownership in Crea... — committed to snowflakedb/terraform-provider-snowflake by sfc-gh-jcieslak 3 years ago
- feat: grant ownership follow-up (#2658) A follow-up for https://github.com/Snowflake-Labs/terraform-provider-snowflake/pull/2604. Done in this pr: - All of the edge cases handled and tested (exc... — committed to snowflakedb/terraform-provider-snowflake by sfc-gh-jcieslak 3 years ago
Is there a workaround here I’m not seeing? I’m not setting
ownershipbut I am usingallandfuture. During any destroy action, I’m always getting stuck with the error below. I then have to runterraform state rmagainst the grants, thenterraform apply -destroywill work so I can thenterraform applyto retest. I’m running this test in a new Snowflake account so there’s no pre-existing resources such as grants causing problems.@sfc-gh-swinkler you comment seems to imply that using what @dmfay mentioned would fix the ownership issue but @dmfay mentioned in the last paragraph the
the teardown problem still occurs.Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be setThis
snowflake_grant_privileges_to_roleresource wasn’t intended to work with the special ownership privilege because ownership cannot be revoked in the same way that grant privileges can; ownership can only assigned to a different role. We intend to add a newsnowflake_grant_ownershipresource to handle ownership permissions. In the meantime, I recommend splitting up the resource into two resources, one which manages ownership and one which manages all other permissions, as @dmfay suggests.Hey @dmfay @emancu @ceik @jasonicarter @trabianmatt @chrisweis This seems like an issue with the
OWNERSHIPprivilege. In the newestsnowflake_grant_privileges_to_account_roleresource (which is now recommended instead ofsnowflake_grant_privileges_to_role) theOWNERSHIPprivilege is not allowed for that reason. Luckily as you can see there’s an open pr (probably 1st out of two) to introduce ownership-granting resource. That resource should resolve this issue, so stay tuned 😃Hey 👋 Closing, as the issue was about the deprecated resources. Recently, we released a new grant resource which is capable of granting ownership. Please, give it a try. If there will be any issues with it, create another GitHub issue. Also, please check our technical documentation section where you can find a migration guide that can help you with upgrading to the latest grant resources and our newly added design decision doc (regarding new grant resources).
I’m getting the same teardown error and am only using
selectprivilege on a table.Splitting the schema grants works well for setup, but teardown fails to unroll them during execution:
terraform planned things in the correct order – wind down the usage and create-table grants in the
public,raw, andsecurityschemata, then destroy the ownership grants the former depend on in those three and the additionalmartschema. The first step is successful and drops the three dependent grants. The second, dropping the four ownership grants, fails (including formartwhich did not have a dependent grant!).Other grants are removed correctly: all-tables-in-schema, future-tables-in-schema, database usage. Most of those do have explicit
depends_onset, but not to grants of the same type.The last thing I see in the query log is
REVOKE ALL PRIVILEGES ON DATABASE "STAGING_TEST_DB" FROM ROLE "ADMIN_STAGING". Since the “exactly one of” message is from the provider code, I suspect that theREVOKE ALL PRIVILEGESis coming from the revocation of the dependent grants, leaving no valid targets for the revocation of the ownership grant.I did upgrade to 0.68.2 on seeing #1953, but while that should fix the
privilegeschurn I had to set ignore directives for, the teardown problem still occurs.the exact same thing happened to me earlier this week after I upgraded to 0.68 and missed updating in a couple of places 😄