create_default_roles is not working

[bmacauley]

When create_default_roles = true, I get the following error

│ Error: An error occurred when granting privileges to database role │ │ with module.snowflake_default_role["transformer"].snowflake_grant_privileges_to_database_role.schema_grants["ALL_SCHEMAS_CUSTOM_1"], │ on .terraform/modules/snowflake_default_role/main.tf line 82, in resource "snowflake_grant_privileges_to_database_role" "schema_grants": │ 82: resource "snowflake_grant_privileges_to_database_role" "schema_grants" { │ │ Id: "BMACAULEY_TEST_DB"."TRANSFORMER"|false|false|CREATE TASK,CREATE │ STREAM,CREATE EXTERNAL TABLE,CREATE PROCEDURE,CREATE FILE FORMAT,CREATE │ TABLE,CREATE VIEW,CREATE TAG,CREATE STAGE,CREATE PIPE,CREATE │ SEQUENCE,CREATE TEMPORARY TABLE,USAGE,CREATE MATERIALIZED VIEW,CREATE │ DYNAMIC TABLE,CREATE │ FUNCTION|OnSchema|OnAllSchemasInDatabase|"BMACAULEY_TEST_DB" │ Database role name: {BMACAULEY_TEST_DB TRANSFORMER} │ Error: 391811 (0A000): Unsupported feature GRANT/REVOKE CREATE MATERIALIZED │ VIEW ON SCHEMA'. ╵

Quick search on Perplexity
https://www.perplexity.ai/search/i-am-using-this-module-https-g-lOlsFtR6S9y1f73lEQKE.g

Why This Error Happens
Enterprise Edition Required: Creating materialized views and managing their privileges is only supported in Snowflake's Enterprise Edition and above. Lower editions will throw unsupported feature errors when attempting to use materialized views or related grants.

Privilege Grant Limitation: Snowflake's SQL syntax for granting privileges does not always include materialized view privileges in schema-level grants (future or all privileges); attempts to do so via Terraform modules or direct SQL will result in errors.

[dgniewek]

Hello, thank you for your feedback.

It seems that provider 1.x+ introduced that behavior, but to be totally honest, we have not tested that codebase on "standard" snowflake version.

There is a simple workaround to that, as you can overwrite all roles definitions even so called default_roles - see this snippet based on ./examples/complete (as it should resolve your issue):

module "project_database" {
  source = "../../"

  context_templates = var.context_templates

  name = "analytics"
  name_scheme = {
    context_template_name = "snowflake-project-database"
    extra_values = {
      project = "project"
    }
    uppercase = false
  }


  comment                     = "test database"
  data_retention_time_in_days = 1
  is_transient                = false

  create_default_roles     = true
  database_ownership_grant = snowflake_account_role.admin_role.name

  schemas = {
    test = {
      roles = {
        transformer = {
          schema_grants = [{
            privileges = [
              "CREATE TEMPORARY TABLE",
              "CREATE TAG",
              "CREATE PIPE",
              "CREATE PROCEDURE",
              "USAGE",
              "CREATE TABLE",
              "CREATE FILE FORMAT",
              "CREATE STAGE",
              "CREATE TASK",
              "CREATE FUNCTION",
              "CREATE EXTERNAL TABLE",
              "CREATE SEQUENCE",
              "CREATE VIEW",
              "CREATE STREAM"
            ]
          }]
          schema_objects_grants = {
            "MATERIALIZED VIEW" = []
          }
        }
      }
    }
  }
}

From my side, I would like to minimize the amount of new flags added to the module (as it already is complicated).