[Bug]: snowflake_oauth_integration_for_custom_clients - blocked_roles_list should be optional

[imre-kerr-sb1]

Terraform CLI Version

1.9.8

Terraform Provider Version

0.97.0

Terraform Configuration

resource "snowflake_oauth_integration_for_custom_clients" "example" {
  name               = "example"
  enabled            = true
  oauth_client_type  = "CONFIDENTIAL"
  oauth_redirect_uri = "https://example.com/complete/snowflake"
  blocked_roles_list = ["ACCOUNTADMIN", "SECURITYADMIN"] # Fails if ORGADMIN is present in the account
  # blocked_roles_list = ["ACCOUNTADMIN", "ORGADMIN", "SECURITYADMIN"] # Fails if ORGADMIN is *not* present in the account
}

Category

category:resource

Object type(s)

resource:oauth_integration

Expected Behavior

blocked_roles_list should be optional, matching the syntax of CREATE SECURITY_INTEGRATION (https://docs.snowflake.com/en/sql-reference/sql/create-security-integration-oauth-snowflake)

Actual Behavior

blocked_roles_list is mandatory. This is annoying, since I have to check if I'm operating on an orgadmin account or not to give the correct list. Bit of a niche case, but could be avoided entirely by just making the field optional.

Error message when missing orgadmin in an orgadmin account:

╷
│ Error: 003629 (42501): Roles [ACCOUNTADMIN, ORGADMIN, SECURITYADMIN] are blocked since parameter OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST is enabled.
│ 
│   with snowflake_oauth_integration_for_custom_clients.example,
│   on main.tf line 1, in resource "snowflake_oauth_integration_for_custom_clients" "example":
│    1: resource "snowflake_oauth_integration_for_custom_clients" "example" {
│ 
╵

Error message when including orgadmin in a non-orgadmin account:

╷
│ Error: 001008 (22023): SQL compilation error:
│ invalid value [ORGADMIN] for parameter 'BLOCKED_ROLES_LIST'
│ 
│   with snowflake_oauth_integration_for_custom_clients.example,
│   on main.tf line 1, in resource "snowflake_oauth_integration_for_custom_clients" "example":
│    1: resource "snowflake_oauth_integration_for_custom_clients" "example" {
│ 
╵

Steps to Reproduce

Apply the configuration above to two accounts, one orgadmin, one not. It will fail on at least one.

How much impact is this issue causing?

Low

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[imre-kerr-sb1]

Current workaround:

data "snowflake_roles" "orgadmin" {
  like = "ORGADMIN"
}
locals {
  maybe_orgadmin_role = length(data.snowflake_roles.orgadmin.roles) > 0 ? "ORGADMIN" : null
}

resource "snowflake_oauth_integration_for_custom_clients" "example" {
  name               = "example"
  enabled            = true
  oauth_client_type  = "CONFIDENTIAL"
  oauth_redirect_uri = "https://example.com/complete/snowflake"

  blocked_roles_list = compact(["ACCOUNTADMIN", "SECURITYADMIN", local.maybe_orgadmin_role])
}

[sfc-gh-jmichalak]

Hi @imre-kerr-sb1 👋

It's true that this field should be optional to match Snowflake. We marked this field as required during the rework to handle default roles properly (to handle permadiff in Terraform). However, a similar external_oauth_blocked_roles_list field in snowflake_external_oauth_integration is handled differently and is optional. We'll take a look and see if the behavior is similar in both of these resources. If it is, we can make this field optional with a custom diff suppression function.

[sfc-gh-jmichalak]

Hi @imre-kerr-sb1 👋

We've just released v0.100.0 (release, migration guide) in which we made blocked_roles_list optional. Presence of the privileged roles can be controlled by OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST. Please upgrade to this version and let us know if you have any issues.