tf-snowflake-s3-backups-task.tf

resource "snowflake_database_grant" "usage_sandbox_database" {
  provider = snowflake.security_admin
  database_name = snowflake_database.sandbox.name
  privilege     = "USAGE"

  roles = [
    snowflake_role.sandbox_rw.name,
    snowflake_role.task_admin.name # UPDATE
  ]
}

resource "snowflake_schema_grant" "usage_sandbox_tools" {
  provider = snowflake.security_admin
  database_name = snowflake_database.sandbox.name
  schema_name   = snowflake_schema.sandbox_tools.name
  privilege     = "USAGE"

  roles = [
    snowflake_role.sandbox_rw.name,
    snowflake_role.task_admin.name # UPDATE
  ]
}

resource "snowflake_warehouse_grant" "usage_warehouse" {
  provider = snowflake.security_admin
  warehouse_name = snowflake_warehouse.warehouse.name

  privilege = "USAGE"

  roles = [
    snowflake_role.sandbox_rw.name,
    snowflake_role.task_admin.name # UPDATE
  ]
}

resource "snowflake_role" "task_admin" {
  provider = snowflake.security_admin
  name     = "TASKADMIN"
}

resource "snowflake_role_grants" "task_admin_grants" {
  provider = snowflake.security_admin
  role_name = snowflake_role.task_admin.name

  roles = [
    local.sys_admin_role
  ]

  users = [
    local.snowflake_user
  ]
}

resource "snowflake_account_grant" "execute_task" {
  provider  = snowflake.account_admin
  roles     = [
    snowflake_role.task_admin.name
  ]
  privilege = "EXECUTE TASK"
}

resource "snowflake_account_grant" "execute_managed_task" {
  provider  = snowflake.account_admin
  roles     = [
    snowflake_role.task_admin.name
  ]
  privilege = "EXECUTE MANAGED TASK"
}

resource "snowflake_task" "backup_sandbox_database" {
  provider  = snowflake.account_admin # owner role needs to have execute task privilege
  database  = snowflake_database.sandbox.name
  schema    = snowflake_schema.sandbox_tools.name
  enabled   = true
  name      = upper("task_call_${snowflake_procedure.backup_database.name}")
  warehouse = snowflake_warehouse.warehouse.name

  schedule      = "USING CRON 0 2 */30 * * UTC" # Every 30 days at 2am UTC/9pm Eastern
  sql_statement = "CALL ${snowflake_procedure.backup_database.name}('${snowflake_database.sandbox.name}')"
}

resource "snowflake_task_grant" "operate_task_backup_database" {
  provider      = snowflake.account_admin
  database_name = snowflake_database.sandbox.name
  schema_name   = snowflake_schema.sandbox_tools.name
  task_name     = snowflake_task.backup_sandbox_database.name

  privilege = "OPERATE"

  roles = [
    snowflake_role.task_admin.name
  ]
}