Can't create roles

[aleenprd]

Terraform CLI Version

OpenTofu v1.8.1

Terraform Provider Version

• provider registry.opentofu.org/snowflake-labs/snowflake v0.94.1

Terraform Configuration

##### PROVIDERS #####
# Here we declare the provider block. By default, 
# the connection will be made using the SYSADMIN role
# The user is a custom TF service user, which authenticates using
# secret keys, generated once and then stored in the environment
# https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest
terraform {
  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "0.94.1"
    }
  }
  backend "kubernetes" {
    config_path       = "~/.kube/config" # Defaults to this authentication
    # in_cluster_config = true # Used to authenticate to the cluster from inside a pod.
    secret_suffix     = "state" # Will be called tfstate-{workspace}-{secret_suffix}
    namespace         = "products-airflow-scheduler"
  }
}

# We declare two providers, one for the SYSADMIN role and one for the USERADMIN role
# They use JSON Web Tokens (JWT) for authentication
provider "snowflake" {
  authenticator = "JWT"
  role          = "SYSADMIN"
  user          = "TF_SVC_USER"
  account       = local.provider_account
}

provider "snowflake" {
  authenticator = "JWT"
  alias         = "useradmin"
  role          = "USERADMIN"
  user          = "TF_SVC_USER"
  account       = local.provider_account
}

provider "snowflake" {
  authenticator = "JWT"
  alias         = "securityadmin"
  role          = "SECURITYADMIN"
  user          = "TF_SVC_USER"
  account       = local.provider_account
}


##### RESOURCES #####
#####################

### WAREHOUSES ### 
resource "snowflake_warehouse" "tf_demo_wh" {
  name                  = "tf_demo_wh"
  comment               = "This is the warehouse that is assigned to the TF service user."
  warehouse_size        = "XSMALL"
  max_concurrency_level = 1
}


### DATABASES ###
resource "snowflake_database" "tf_demo_db" {
  name    = "tf_demo_db"
  comment = "This is the database that we will be using for the demo."
}


### SCHEMAS ###
resource "snowflake_schema" "tf_demo_schema" {
  name         = "tf_demo_schema"
  comment      = "This is the schema that we will be using for the demo."
  database     = snowflake_database.tf_demo_db.name
  is_transient = true
}


### TABLES ###
resource "snowflake_table" "tf_demo_table" {
  name     = "tf_demo_table"
  comment  = "This is the table that we will be using for the demo."
  database = snowflake_database.tf_demo_db.name
  schema   = snowflake_schema.tf_demo_schema.name

  column {
    name    = "ID"
    type    = "NUMBER"
    comment = "This is the primary key of the table."
  }

  column {
    name    = "NAME"
    type    = "STRING"
    comment = "This is the name of the person."
  }
}


### ROLES ###
resource "snowflake_role" "tf_demo_role" {
  name     = "tf_demo_role"
  comment  = "This is the database TF role that we will be using for the TF demo."
}

Category

category:resource

Object type(s)

resource:role

Expected Behavior

I expect it to create the role

Actual Behavior

Using the configuration below, I get the following error:

snowflake_role.tf_demo_role: Creating...
╷
│ Warning: Deprecated Resource
│ 
│   with snowflake_role.tf_demo_role,
│   on main.tf line 107, in resource "snowflake_role" "tf_demo_role":
│  107: resource "snowflake_role" "tf_demo_role" {
│ 
│ This resource is deprecated and will be removed in a future major version release. Please use snowflake_account_role instead.
╵
╷
│ Error: Failed to create account role
│ 
│   with snowflake_role.tf_demo_role,
│   on main.tf line 107, in resource "snowflake_role" "tf_demo_role":
│  107: resource "snowflake_role" "tf_demo_role" {
│ 
│ Account role name: tf_demo_role, err: 003001 (42501): SQL access control error:
│ Insufficient privileges to operate on account 

PS: the same happens if I use account_role

Steps to Reproduce

Copy configuration (use own account), plan and apply.

##### LOCAL VARIABLES #####
# Here we declare the dependencies of our main module
# We are using the Snowflake provider from Snowflake-Labs
locals {
  provider_account = "redacted account"
}

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

[sfc-gh-jcieslak]

Hey @aleenprd 👋
That's intended behavior because you use a role with insufficient privileges. In your default Snowflake provider configuration, you have SYSADMIN as the default role which is not able to create roles in Snowflake. For a guide on access control in Snowflake I recommend this document, you can find there the information on default roles (like SYSADMIN) and what they can do. There's also a hierarchy of default roles, so it's clear what is the minimum access control for a given action you would like to perform or what roles are inheriting other role's privileges.

tldr; To create a role you have to have at least the USERADMIN role (or the ones that are higher in the hierarchy like SECURITYADMIN or ACCOUNTADMIN).

[aleenprd]

But the Snowflake user I am connected with has securityadmin, sysadmin and useradmin and I declared these 3 providers in main.tf. is there something else I need to do? Terraform is not able to use the appropriate role?

[sfc-gh-jcieslak]

No, you have to specify which provider should be used for every resource (or in this case, you don't specify it and I believe, Terraform takes the default one without an alias). You created 3 providers, the default one (with SYSADMIN role) and two other ones with aliases. To use the correct provider you have to use the provider meta argument on the role resource with securityadmin alias to use that provider configuration.

[aleenprd]

@sfc-gh-jcieslak thank you so much, I was not aware of that. I thought the snowflake-labs somehow handled this logic in the backend :)

[sfc-gh-jcieslak]

Closing as the issue was resolved. If you still have any issues regarding this particular configuration, go ahead and ask; for any other problem, please create another gh issue.