How can users manage Snowflake Tasks /w Terraform w/ different owner roles?

[liamjamesfoley]

👋
Hello!

I'm having trouble with manage tasks in Terraform b/c of the Task ownership model.

• Snowflake Tasks execute as the role that owns them
• Our Snowflake Terraform provider is set up with a highly privileged role
• we don't want our task to execute as that role

in order to follow the principle of least privilege, we want tasks to execute as (and therefore be owned by) different roles.

How can we accomplish this given the current tasks API?

Since its impossible to create a task owned by a role other than the current role, upon creation, all our task are owned by TERRAFORM_ROLE (set up in our provider).

We can transfer the ownership using snowflake_grant_ownership

But when we try to add downstream dependent tasks, the TF apply fails:

│ Error: error creating task "OPERATION_DB"."TASKS"."TASK_B" err = 091405 (22000): Task TASK_B cannot have the given predecessor since they do not share the same owner role.
│ 
│   with module.TASK_B.snowflake_task.task,
│   on ../../stack/snowflake/task/main.tf line 25, in resource "snowflake_task" "task":
│   25: resource "snowflake_task" "task" {

Basically it's impossible to run:
CREATE TASK_A;
GRANT OWNERSHIP ON TASK_A TO ROLE MY_ROLE;
CREATE TASK B AFTER TASK_A; -- This fails
GRANT OWNERSHIP ON TASK_B TO ROLE MY_ROLE;

Full Example (TASK_B was added in a subsequent PR):

module "TASK_A" {
  source          = "./stack/snowflake/task"
  task_name       = "TASK_A"
  schedule        = "USING CRON 40 * * * * America/Los_Angeles"
  sql_statement   = <<EOT
    SELECT 4 as val
  EOT  
  warehouse_name  = module.ingestion_s_wh_warehouse.warehouse_name
  task_owner_role = "ANALYST"
}

module "TASK_B" {
  source          = "./stack/snowflake/task"
  task_name       = "TASK_B"
  after           = ["TASK_A"]
  sql_statement   = <<EOT
    SELECT 2 as val
  EOT
  warehouse_name  = module.ingestion_s_wh_warehouse.warehouse_name
  task_owner_role = "ANALYST"
}

Module:

locals {
  session_parameters = merge(var.session_parameters, { "QUERY_TAG" : "${var.database}_${var.schema}_${var.task_name}" })
  comment            = trimspace(join(" ", ["Managed by Terraform.", var.comment]))
}

resource "snowflake_task" "task" {
  comment = local.comment

  database  = var.database
  schema    = var.schema
  warehouse = var.warehouse_name

  name          = var.task_name
  schedule      = var.schedule
  sql_statement = var.sql_statement

  session_parameters = local.session_parameters
  after              = var.after
  when               = var.when
  enabled            = var.enabled

  user_task_timeout_ms = var.task_timeout
  # Can only be set on root task
  suspend_task_after_num_failures = var.after == null ? var.suspend_task_after_num_failures : null

  lifecycle {
    ignore_changes = [enabled]
  }
}

resource "snowflake_grant_ownership" "task_owner_grant" {
  account_role_name = var.task_owner_role
  on {
    object_type = "TASK"
    object_name = "\"${var.database}\".\"${var.schema}\".\"${snowflake_task.task.name}\""
  }
  outbound_privileges = "COPY"
}

Module definition:

locals {
  session_parameters = merge(var.session_parameters, { "QUERY_TAG" : "${var.database}_${var.schema}_${var.task_name}" })
  comment            = trimspace(join(" ", ["Managed by Terraform.", var.comment]))
}

resource "snowflake_task" "task" {
  comment = local.comment

  database  = var.database
  schema    = var.schema
  warehouse = var.warehouse_name

  name          = var.task_name
  schedule      = var.schedule
  sql_statement = var.sql_statement

  session_parameters = local.session_parameters
  after              = var.after
  when               = var.when
  enabled            = var.enabled

  user_task_timeout_ms = var.task_timeout
  # Can only be set on root task
  suspend_task_after_num_failures = var.after == null ? var.suspend_task_after_num_failures : null

  lifecycle {
    ignore_changes = [enabled]
  }
}

resource "snowflake_grant_ownership" "task_owner_grant" {
  account_role_name = var.task_owner_role
  on {
    object_type = "TASK"
    object_name = "\"${var.database}\".\"${var.schema}\".\"${snowflake_task.task.name}\""
  }
  outbound_privileges = "COPY"
}

Module variables:

variable "after" {
  description = "Use this when chaining tasks together, cannot be used with schedule"
  type        = list(string) # [""]
  default     = null
}

variable "schedule" {
  description = "cannot be used with after"
  type        = string
  default     = null
}

variable "comment" {
  type    = string
  default = ""
}

variable "enabled" {
  type    = bool
  default = false
}

variable "sql_statement" {
  type = string
}

variable "task_name" {
  type = string
}

variable "task_owner_role" {
  type = string
}

variable "task_timeout" {
  description = "Specifies the time limit on a single run of the task before it times out (in milliseconds)"
  type        = number
  default     = 900000 # 15 minutes
}

variable "warehouse_name" {
  type = string
}

variable "when" {
  type    = string
  default = ""
}

variable "database" {
  type    = string
  default = "OPERATION_DB"
}

variable "schema" {
  type    = string
  default = "TASKS"
}

variable "session_parameters" {
  type    = map(string)
  default = {}
}

variable "suspend_task_after_num_failures" {
  type    = number
  default = 10
}

variable "user_task_timeout_ms" {
  type    = number
  default = 3600000
}

[sfc-gh-asawicki]

Hey @liamjamesfoley.

In the Snowflake docs, there is a good extract on changing the ownership of DAGs: https://docs.snowflake.com/en/user-guide/tasks-graphs#manage-task-graph-ownership. Please check it out.

I have pushed an automatic test showing a small working sample for a similar setup

terraform-provider-snowflake/pkg/resources/grant_ownership_acceptance_test.go

Line 1055 in f5c9954

func TestAcc_GrantOwnership_OnTask_Discussion2877(t *testing.T) {

and the second solution from https://docs.snowflake.com/en/user-guide/tasks-graphs#manage-task-graph-ownership.

In the first step

terraform-provider-snowflake/pkg/resources/testdata/TestAcc_GrantOwnership/OnTask_Discussion2877/1/test.tf

Line 1 in f5c9954

resource "snowflake_role" "test" {

we have just a task with already changed ownership.

In the second step

terraform-provider-snowflake/pkg/resources/testdata/TestAcc_GrantOwnership/OnTask_Discussion2877/2/test.tf

Line 1 in f5c9954

resource "snowflake_role" "test" {

it tries to add a new task with the dependency to the previous task, resulting in the same error:

terraform-provider-snowflake/pkg/resources/grant_ownership_acceptance_test.go

Line 1096 in f5c9954

ExpectError: regexp.MustCompile("cannot have the given predecessor since they do not share the same owner role"),

.

Then, the third step is the first step to try to follow the second solution in https://docs.snowflake.com/en/user-guide/tasks-graphs#manage-task-graph-ownership, so transferring the ownership to all the tasks inside a schema by first revoking the ownership back to the terraform user role:

terraform-provider-snowflake/pkg/resources/testdata/TestAcc_GrantOwnership/OnTask_Discussion2877/3/test.tf

Line 1 in f5c9954

resource "snowflake_role" "test" {

.

The fourth step succeeds with a config:

terraform-provider-snowflake/pkg/resources/testdata/TestAcc_GrantOwnership/OnTask_Discussion2877/4/test.tf

Line 1 in f5c9954

resource "snowflake_role" "test" {

.

This is one of the possible ways. The two more I could think of (but did not verify them in practice) are:

• splitting your tf config into multiple deployments, one dedicated solely to managing task dags (it should use a different user with "lower" privileges; no ownership changes should be done here: the same user could be used for running/creating these DAGs)
• adding a second provider block in your TF config, that uses the user similar to the above proposal ("lower" privileges) and the you can mark in the resource level which provider should be used to manage given object (essentially, these two proposals are almost the same because they both involve a secondary user to be used from the terraform).

[liamjamesfoley]

Thanks for the quick reply @sfc-gh-asawicki,

I've considered the extra provider solution, but would like to not have N number of providers, where N is the number of roles that could own tasks.

As for the demo you provided, it seems like the usage of depends_on = [snowflake_task.child], necessitates that the whole the task graph (parent and any children) are added at at the same time, is that accurate? If so, that won't be super friendly for our end users developing multiple task graphs.

f5c9954#diff-205fe850fe5a9779651b1b4cdb2dd1d86106b427f0854f9072e52d8e5b7bb8dbR23

Thanks again!

[sfc-gh-asawicki]

Hey. You have linked the second step, which shows the same error behavior that you encountered. The right steps to accomplish the desired state are 3 and 4. The use of depends_on is not necessary to show the error, though, but allows us to tell terraform in what order the resources should be created (but again, it does not matter in this case).

Users can follow steps 1 -> 3 -> 4 to accomplish the result you want. Also, this follows the https://docs.snowflake.com/en/user-guide/tasks-graphs#manage-task-graph-ownership, so it's not provider-specific.