Is CREATE MODEL privileged allowed with resource snowflake_grant_privileges_to_role in version 0.86.0?

[alvarosaavedrau]

Terraform CLI and Provider Versions

Terraform version 1.0.0
Provider version 0.55.1

Use Cases or Problem Statement

With the version 0.55.1 we can't use the CREATE MODEL privilege, we get the following error:

Error: expected privilege to be one of [CREATE MATERIALIZED VIEW CREATE STAGE CREATE TAG CREATE TASK CREATE VIEW USAGE CREATE EXTERNAL TABLE CREATE FUNCTION CREATE SEQUENCE CREATE STREAM OWNERSHIP ADD SEARCH OPTIMIZATION CREATE MASKING POLICY MODIFY MONITOR CREATE FILE FORMAT CREATE TEMPORARY TABLE CREATE ROW ACCESS POLICY CREATE SESSION POLICY CREATE TABLE CREATE PIPE CREATE PROCEDURE], got CREATE MODEL
09:40:58  
with module.snowflake-schema["*******"].snowflake_schema_grant.mySchemaGrant["CREATE_MODEL"],
on schema.tf line 28, in resource "snowflake_schema_grant" "mySchemaGrant":
28:   privilege           = replace ( each.key , "_" , " ")

Proposal

We want to use the CREATE MODEL privilege with the version 0.55.1, we know this is not possible, so we are trying the 0.86.0 version (latest at 27/02/2024). But we get another errores of resources deprecated.
We need to know if in this last version the CREATE MODEL privilege is usable. So we would update all the other resources that change with this version, compared with 0.55.1 version.

How much impact is this issue causing?

High

Additional Information

No response

[sfc-gh-asawicki]

Hey @alvarosaavedrau. Thanks for reaching out to us.

We do not support this privilege in 0.86.0 version. It was probably added to the docs just recently. We will add this in one of the nearest releases.

[alvarosaavedrau]

We look forward to this new change, thank you very much!

[sfc-gh-jcieslak]

Hey @alvarosaavedrau. We have released the fix as part of v0.87.0 release. Please follow the migration guide during the update. Please confirm that the issue is resolved in the newest version. Thanks!

[alvarosaavedrau]

Hi, I am using this version 0.87.0 and 1.4.1 of terraform, now this CREATE MODEL privilege error happens now with snowflake_schema_grant resource:

│ Error: expected privilege to be one of ["CREATE TEMPORARY TABLE" "CREATE VIEW" "OWNERSHIP" "USAGE" "ALL PRIVILEGES" "MONITOR" "ADD SEARCH OPTIMIZATION" "CREATE FUNCTION" "CREATE ROW ACCESS POLICY" "CREATE MASKING POLICY" "CREATE PIPE" "CREATE SEQUENCE" "CREATE STREAMLIT" "CREATE EXTERNAL TABLE" "CREATE PROCEDURE" "CREATE SESSION POLICY" "CREATE TAG" "CREATE DYNAMIC TABLE" "CREATE FILE FORMAT" "CREATE TABLE" "MODIFY" "CREATE MATERIALIZED VIEW" "CREATE STAGE" "CREATE STREAM" "CREATE TASK"], got CREATE MODEL
│
│   with module.snowflake-schema["********"].snowflake_schema_grant.mySchemaGrant["CREATE_MODEL"],
│   on ../modules/snowflake-schema/32-schema.tf line 28, in resource "snowflake_schema_grant" "mySchemaGrant":
│   28:   privilege           = replace ( each.key , "_" , " ")
│

Is it possible to use this privilege for the resource snowflake_schema_grant?

[sfc-gh-jcieslak]

I'm not sure about snowflake_schema_grant, because it's deprecated and we're not supporting deprecated resources. Right now, we have new grant resources to grant privilege to an account role please use snowflake_grant_privileges_to_account_role. We'll try to improve our docs in terraform registry to be clear with what is deprecated and what is not.

[sfc-gh-jcieslak]

Hey, for migration those can be helpful resources:

• https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/MIGRATION_GUIDE.md
• https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/docs/technical-documentation/resource_migration.md
  Could you confirm after migration that CREATE MODEL works with snowflake_grant_privileges_to_account_role? I would really appreciate it, Thanks :)

[alvarosaavedrau]

Hello @sfc-gh-jcieslak, I confirm that the CREATE MODEL privilege can be used with the snowflake_grant_privileges_to_account_role resource.

Before with the resource schema_grants_list and its value "roles" allowed this value to be a list of roles, now it does not allow it, account_role_name required a string, not list string is allowed. If you want to go through a list of privileges you must use the privilege as each.key and a list of roles as values to go through:

resource "snowflake_grant_privileges_to_account_role" "schema" {

 for_each = var.schema_grants_list

 account_role_name = each.key
 privileges        = each.value.privileges
 with_grant_option = each.value.with_grant_option
 on_schema {
   schema_name = "\"${var.db_name}\".\"${var.schema_name}\""
 }
}

tfvars file

schema_grants_list = {
      ROLE1 = {
            privileges        = ["CREATE MODEL", "USAGE", "MONITOR", "CREATE FUNCTION"]
            with_grant_option = false
      },

      ROLE2 = {
            privileges = ["USAGE"]
            with_grant_option = false
      },
}

OWNERSHIP privilege not allowed

To add more information about this resource, the OWNERSHIP privilege can no longer be used with the snowflake_grant_privileges_to_account_role resource, the following must be used snowflake_user_ownership_grant snowflake_role_ownership_grant. In my case snowflake_role_ownership_grant

Error: Unsupported privilege 'OWNERSHIP'

  with module.snowflake-schema["*********"].snowflake_grant_privileges_to_account_role.schema["roleX"],
  on schema.tf line 61, in resource "snowflake_grant_privileges_to_account_role" "schema":
  61:  privileges        = each.value.privileges

Granting ownership is only allowed in dedicated resources
(snowflake_user_ownership_grant, snowflake_role_ownership_grant)

[sfc-gh-jcieslak]

Yes, with new grant resources, only one grant call to a role can be made (1 resource = 1 grant privilege to role call). It was intentional to make the resource simple and as you pointed out terraform has for_each which is the same thing as the role list. OWNERSHIP granting resource is in progress and should be available soon. For now, there's no good alternative other than the resources you mentioned or snowflake_unsafe_execute.

[alvarosaavedrau]

I take this opportunity to ask.
I can see in the markdown docs/resources/schema_grant.md that there is a title indicating that this resource (snowflake_schema_grant) is deprecated (commit by @sfc-gh-asawicki), but in the terraform registry does not appear that message of deprecated resource (https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/schema_grant).
Is this because the release containing the message is not deployed? or what is the reason why this message does not appear in the terraform registry?

[sfc-gh-asawicki]

@alvarosaavedrau yes, this is not yet merged. After the merge, we also have to release it (probably tomorrow). The resource was deprecated a long time ago but the Terraform docs plugin is not using this information out-of-the-box, so we had to do some gymnastics to add it there. All deprecated resources will be marked accordingly after #2581.

[sfc-gh-jcieslak]

Closing as the CREATE MODEL privilege is confirmed to be working in the snowflake_grant_privileges_to_account_role resource.
Thanks for the reported issue :)