snowflake_procedure execute_as=CALLER update does not work on existing resource

[krishreddy56]

Terraform CLI and Provider Versions

Terraform: 1.7.2 Provider: 0.87

Terraform Configuration

resource "snowflake_procedure" "test_procedure" {
  database = "TEST"
  schema   = "TEST_SCHEMA
  name     = "TEST_PROCEDURE"
  arguments {
    name = "TEST_DATE"
    type = "DATE"
  }
  arguments {
    name = "NUMBER_OF_DAYS"
    type = "NUMBER"
  }
  language    = "SQL"
  comment     = "sample procedure"
  return_type = "TABLE (ROWS_UNLOADED NUMBER, INPUT_BYTES NUMBER, OUTPUT_BYTES NUMBER)"
  execute_as = "CALLER"
  statement = templatefile("${path.module}/test.sql", {
    bucket_name              = "test-bucket",
    storage_integration_name = "TEST_STORAGE_INTEGRATION,
  })
}

Expected Behavior

I have a snowflake procedure created with execute_as=OWNER through terraform. Modifying the resource to update execute_as=CALLER should just allow the procedure to be updated. Tried modifying the procedure in the snowflake console and it works.

Actual Behavior

terraform apply fails with below error

│ Error: 001003 (42000): SQL compilation error: │ syntax error line 1 at position 93 unexpected 'CALLER'.

Steps to Reproduce

1. Runterraform apply with execute_as=OWNER
2. Procedure is created
3. Update the resource with execute_as=CALLER to modify the procedure
4. Run terraform applyand it fails

How much impact is this issue causing?

High

Logs

No response

Additional Information

We had to destroy the procedure and then recreate it using execute_as=CALLER as a workaround

[sfc-gh-asawicki]

Hey @krishreddy56. Thanks for reaching out to us.

Please run the terraform with TF_LOG=DEBUG flag enabled. You should be able to see the ALTER statement run for execute_as update (it should be close above to the error you have shared).

[krishreddy56]

Re ran the terraform apply by enabling the debug log. I see below in the logs

2024-03-21T14:30:07.082-0600 [INFO]  Starting apply for module.sql_compliance.snowflake_procedure.test_procedure
2024-03-21T14:30:07.084-0600 [DEBUG] module.sql_compliance.snowflake_procedure.test_procedure: applying the planned Update change
2024-03-21T14:30:07.353-0600 [DEBUG] provider.terraform-provider-snowflake_v0.87.2: 2024/03/21 14:30:07 [DEBUG] sql-conn-exec: [query ALTER PROCEDURE "TEST"."TEST_SCHEMA"."TEST_PROCEDURE" (DATE, NUMBER) CALLER err 001003 (42000): SQL compilation error:
2024-03-21T14:30:07.353-0600 [DEBUG] provider.terraform-provider-snowflake_v0.87.2: syntax error line 1 at position 93 unexpected 'CALLER'. duration 267.033583ms args {}] (SENDGRID)
2024-03-21T14:30:07.353-0600 [DEBUG] provider.terraform-provider-snowflake_v0.87.2: 2024/03/21 14:30:07 [DEBUG] err: 001003 (42000): SQL compilation error:
2024-03-21T14:30:07.353-0600 [DEBUG] provider.terraform-provider-snowflake_v0.87.2: syntax error line 1 at position 93 unexpected 'CALLER'.
2024-03-21T14:30:07.353-0600 [ERROR] provider.terraform-provider-snowflake_v0.87.2: Response contains error diagnostic: @caller=github.com/hashicorp/[email protected]/tfprotov6/internal/diag/diagnostics.go:62 @module=sdk.proto diagnostic_severity=ERROR tf_proto_version=6.4 tf_provider_addr=registry.terraform.io/Snowflake-Labs/snowflake tf_rpc=ApplyResourceChange diagnostic_detail="" tf_req_id=5a6a0c29-41b6-fac2-678b-e19ced386ce6 tf_resource_type=snowflake_procedure
  diagnostic_summary=
  | 001003 (42000): SQL compilation error:
  | syntax error line 1 at position 93 unexpected 'CALLER'.
   timestamp=2024-03-21T14:30:07.353-0600

[sfc-gh-asawicki]

Thanks, I see where the problem is. It should be an easy fix, we should be able to have it with the next week's release.

[krishreddy56]

Thank you for looking into this, appreciate it

[sfc-gh-asawicki]

Hey @krishreddy56/ Fix released in v0.88.0.

Please check and close the issue if the problem is solved for you.

[sfc-gh-asawicki]

Closing as fixed in v.0.88.0. Please open a new issue if the problem persists in the newest provider version.