Unable to use snowflake_grant_privileges_to_role for functions and procedures

[danu165]

Provider Version

0.69.0

Terraform Version

1.0.8

Describe the bug

Terraform creates an incorrect SQL statement where data types are needed for objects like procedures and grants

Expected behavior

Terraform would create a correct SQL statement.

Code samples and commands

I've tried 2 different methods, one where I do not specify any double quotes (see resource ID functions) and one where I escape the double quotes (see resource ID procedures).

resource "snowflake_grant_privileges_to_role" "functions" {
  privileges = ["USAGE"]
  role_name  = "DEV_90467_DATA_ANALYST_FR"
  on_schema_object {
    object_type = "FUNCTION"
    object_name = "DEV_90467_DB.DT_LOCAL_PAVE_FUNCTION_PROCS.EXAMPLE(FLOAT)"
  }
}
resource "snowflake_grant_privileges_to_role" "procedures" {
  privileges = ["USAGE"]
  role_name  = "DEV_90467_DATA_ANALYST_FR"
  on_schema_object {
    object_type = "PROCEDURE"
    object_name = "\"DEV_90467_DB\".\"DT_LOCAL_PAVE_FUNCTION_PROCS\".\"EXAMPLE\"(VARCHAR)"
  }
}

The errors returned are:

╷
│ Error: error granting privileges to account role: 090208 (42601): Argument types of function 'EXAMPLE(FLOAT)' must be specified.
│
│   with snowflake_grant_privileges_to_role.functions,
│   on main.tf line 79, in resource "snowflake_grant_privileges_to_role" "functions":
│   79: resource "snowflake_grant_privileges_to_role" "functions" {
│
╵
╷
│ Error: error granting privileges to account role: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 89 unexpected '" TO ROLE "'.
│ syntax error line 1 at position 100 unexpected 'DEV_90467_DATA_ANALYST_FR'.
│
│   with snowflake_grant_privileges_to_role.procedures,
│   on main.tf line 87, in resource "snowflake_grant_privileges_to_role" "procedures":
│   87: resource "snowflake_grant_privileges_to_role" "procedures" {
│

In query history, I see the functions statement resolve to this:

GRANT USAGE ON FUNCTION "DEV_90467_DB"."DT_LOCAL_PAVE_FUNCTION_PROCS"."EXAMPLE(FLOAT)" TO ROLE "DEV_90467_DATA_ANALYST_FR"

The problem here is that (FLOAT) should not be included in the double quotes.

The procedures statement resolves to this:

GRANT USAGE ON PROCEDURE "DEV_90467_DB"."DT_LOCAL_PAVE_FUNCTION_PROCS"."EXAMPLE"(VARCHAR)" TO ROLE "DEV_90467_DATA_ANALYST_FR"

The problem here is that there is an extra quote after (VARCHAR).

The goal would be to resolve to one of these 2 statements:

GRANT USAGE ON PROCEDURE "DEV_90467_DB"."DT_LOCAL_PAVE_FUNCTION_PROCS"."EXAMPLE"(VARCHAR) TO ROLE "DEV_90467_DATA_ANALYST_FR";  -- option 1
GRANT USAGE ON PROCEDURE DEV_90467_DB.DT_LOCAL_PAVE_FUNCTION_PROCS.EXAMPLE(VARCHAR) TO ROLE "DEV_90467_DATA_ANALYST_FR";  -- option 2

Additional context

Ideally I'm looking for a syntactical solution here. Please let me know what we can do to get around these errors.

[PedroMartinSteenstrup]

@sfc-gh-swinkler I see you merged a fix but my problem fits completely the Terraform creates an incorrect SQL statement where data types are needed for objects like procedures and grants so I'll chip in.

• snowflake-labs/snowflake v0.70.1
• Terraform v1.4.2

I have a procedure: STAGE.PUBLIC.REFRESH_WAREHOUSE_DETAILS() that takes no arguments and I am piping it like this to the resource (and it still has the parenthesis at that point):

  "STAGE.PUBLIC.REFRESH_WAREHOUSE_DETAILS()|TRANSFORMER|false|false" = [
    "USAGE",
  ]

It results in

  # snowflake_grant_privileges_to_role.on_this_procedure["STAGE.PUBLIC.REFRESH_WAREHOUSE_DETAILS|TRANSFORMER|false|false"] will be created
  + resource "snowflake_grant_privileges_to_role" "on_this_procedure" {
      + all_privileges    = false
      + id                = (known after apply)
      + on_account        = false
      + privileges        = [
          + "USAGE",
        ]
      + role_name         = "TRANSFORMER"
      + with_grant_option = false

      + on_schema_object {
          + object_name = "STAGE.PUBLIC.REFRESH_WAREHOUSE_DETAILS"
          + object_type = "PROCEDURE"
        }
    }

and when applying

╷
│ Error: error granting privileges to account role: 090208 (42601): Argument types of function 'REFRESH_WAREHOUSE_DETAILS' must be specified.
│ 
│   with snowflake_grant_privileges_to_role.on_this_procedure["STAGE.PUBLIC.REFRESH_WAREHOUSE_DETAILS|TRANSFORMER|false|false"],
│   on main.tf line 321, in resource "snowflake_grant_privileges_to_role" "on_this_procedure":
│  321: resource "snowflake_grant_privileges_to_role" "on_this_procedure" {
│ 
╵

All my procedures that do contain arguments have no problem for grants

[abarabash-sift]

I also faced this issue. The grants for the functions/procedures with the arguments work fine as is:

resource "snowflake_grant_privileges_to_role" "test" {
  privileges = [
    "USAGE",
  ]
  role_name = "MY_ROLE"

  on_schema_object {
    object_name = "DB.MY_SCHEMA.ARGUMENT_PROCEDURE(VARCHAR, VARCHAR)"
    object_type = "PROCEDURE"
  }
}

But for the function/procedure without arguments you still have to include parentheses, just escape them like this: MY_FUNCTION(\\):

resource "snowflake_grant_privileges_to_role" "test" {
  privileges = [
    "USAGE",
  ]
  role_name = "MY_ROLE"

  on_schema_object {
    object_name = "DB.MY_SCHEMA.NO_ARGUMENTS_PROCEDURE(\\)"
    object_type = "PROCEDURE"
  }
}

[maxnsdia]

This method does not seem to work (anymore) with the new "snowflake_grant_privileges_to_account_role".

resource "snowflake_grant_privileges_to_account_role" "object_procedure" {
  on_schema_object {
    object_type = "PROCEDURE"
    object_name = "MYDB.MYSCHEMA.MYPROC(\\)"
  }
  account_role_name  = var.role
  privileges = var.privileges

  with_grant_option = false
}

The error I get then is:

│ Error: Failed to retrieve grants
│ 
│   with module.sf_autorisation["MYROLE"].module.single_procedure["MYROLE-MYDB-MYSCHEMA-MYPROC(\\\\)"].snowflake_grant_privileges_to_account_role.object_procedure,

It goes from \\ to \\\\ somehow. It also "corrupts" the state file because subsequent plans will fail looking for MYROLE-MYDB-MYSCHEMA-MYPROC(\\\\)which is not there.

Not putting anything between the brackets also still does not work:

Error: 090208 (42601): Argument types of function 'MYPROC'
│ must be specified.

Edit: just to confirm, I can create the exact same successful result as above comment using the old resource "snowflake_grant_privileges_to_role" so this really is something that has to to with the new "snowflake_grant_privileges_to_account_role"

[sfc-gh-jcieslak]

Hey @danu165 @PedroMartinSteenstrup @abarabash-sift @maxnsdia 👋 This seems like a typical issue that we would like to resolve soon with the identifiers refactor, we mentioned on our roadmap. It's connected with the way identifiers are represented and quoted internally. Right now, there's no good internal identifier to represent identifiers of functions/procedures, but we should start working on it soon. For now, I'll try to check if there's any workaround you could use instead.

[maxnsdia]

Thank you for the response @sfc-gh-jcieslak, good to know it's being worked on as part of a larger refactoring. The workaround now is using the method @abarabash-sift mentions above, which works fine as long as you use snowflake_grant_privileges_to_role and not upgrade yet to snowflake_grant_privileges_to_account_role.

A minor inconvenience with using that using the 'old' resource is that this message turns up in the pipeline.

╷
│ Warning: Deprecated Resource
│ 
│   with module.MYMODULE,
│   on MYREPO\myfile.tf line 1, in resource "snowflake_grant_privileges_to_role" "object_procedure":
│    1: resource "snowflake_grant_privileges_to_role" "object_procedure" {
│ 
│ This resource is deprecated and will be removed in a future major version
│ release. Please use snowflake_grant_privileges_to_account_role instead.
│ 
│ (and one more similar warning elsewhere)
╵

[maxnsdia]

Hi @sfc-gh-jcieslak , From 0.93 onwards the older resource (grant_privileges_to_role) is phased out and I still can't get the new resource (grant_privileges_to_account_role) to work with a single procedure that has no argument types: myproc(). If there is at least one argument type it does work. What would the fully qualified object_name have to be? The below snippet does not work.

resource "snowflake_grant_privileges_to_account_role" "object_procedure" {
  on_schema_object {
    object_type = "PROCEDURE"
    object_name = \"MYDB\".\"MYSCHEMA\".\"MYPROC\"()"
  }
  account_role_name  = var.role
  privileges = var.privileges

  with_grant_option = false
}

 Id:
│ "MYROLE"|false|false|USAGE|OnSchemaObject|OnObject|PROCEDURE|MYDB"."MYSCHEMA"."MYPROC"
│ Account role name: {MYROLE}
│ Error: 090208 (42601): Argument types of function
│ 'MYPROC' must be specified.

When there is an argument type, the fully qualified name works: \"MYDB\".\"MYSCHEMA\".\"MYPROC\"(VARCHAR)"

[sfc-gh-jmichalak]

Hi @maxnsdia 👋 We're now fixing this issue - it should be improved in the next release.