Skip revokes

[vlucas-caylent]

Hi there, @aidanmelen

Question:

I want to know if there is a way to skip the revokes, and just create the grants as necessary. My environment is rlly big and i'm trying to use lifecycles to skip the delete.statements. But its not working..

I dont want to have revokes for a moment in the environment during the deploys. Usually takes 4/5 min to recreate the grants when we add a new role or something. For our team this is a issue. I just want to add new grants and not takes this downtime during this time.

Tf-Code

resource "snowsql_exec" "sch_read_mat_view" {
  name = "${local.name_grants}_READ_ALL_SCHEMAS"

  create {
    statements = <<-EOT
    ${local.read_grant_all_sch_mat_view}
    EOT
  }

  delete {
    statements = <<-EOT
    ${local.delete_grant_all_sch_mat_view}
    EOT
  }

  lifecycle {
    ignore_changes = [delete[0].statements]
  }
}

[aidanmelen]

Let me see if I understand your use case correctly. Are you declaring a snowsql resource with many create statements? Are you finding that Terraform wants to perform a destroy before create lifecycle management on all of the statements when you update any of create.statements? So you are exploring lifecycle.ignore_changes to prevent these undesired changes?

[vlucas-caylent]

Let me see if I understand your use case correctly. Are you declaring a snowsql resource with many create statements? Are you finding that Terraform wants to perform a destroy before create lifecycle management on all of the statements when you update any of create.statements? So you are exploring lifecycle.ignore_changes to prevent these undesired changes?

For me is more about to not use delete.statements. I dont want to execute revokes when i have a new role for example. We have a gap during the deployments like the revokes are executed for 5min and then create the grants again, this is causing incident in our snowflake.

[aidanmelen]

I applied the simple example once, then added a contrived statement to the create.statements without changing delete.

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = <<-EOT
    GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    GRANT SELECT PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements = <<-EOT
    REVOKE ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    REVOKE ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};
    EOT
  }
}

Here is the resulting plan:

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement

Terraform will perform the following actions:

  # snowsql_exec.dcl must be replaced
-/+ resource "snowsql_exec" "dcl" {
      ~ id   = "SIMPLE_EXAMPLE_ELEGANT_ANTEATER" -> (known after apply)
        name = "SIMPLE_EXAMPLE_ELEGANT_ANTEATER"

      ~ create {
          ~ statements           = <<-EOT # forces replacement
                GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
                GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
              + GRANT SELECT PRIVILEGES ON ALL TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
            EOT
            # (1 unchanged attribute hidden)
        }

        # (1 unchanged block hidden)
    }

Plan: 1 to add, 0 to change, 1 to destroy.

Changes to Outputs:
  ~ snowsql_create_stmts = <<-EOT
        GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
        GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
      + GRANT SELECT ON ALL TABLES IN DATABASE SIMPLE_EXAMPLE_ELEGANT_ANTEATER TO ROLE SIMPLE_EXAMPLE_ELEGANT_ANTEATER;
    EOT

This is expected behavior. The forces replacement lifecycle manage is part of Terraform core and cannot be "skipped". If you change any statement in the create.statements then the terraform will want to replace all the statements as they are passed to the resource as a single value. If you want lifecycle isolation then you must have dedicated resources for each statement. In this way, when you create a new statement or update an existing statement; it will not have any effect on the others that came before it. For example:

  name = local.name

  create {
    statements = "GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
  }

  delete {
    statements = "REVOKE ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
  }
}

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = "GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
  }

  delete {
    statements = "REVOKE ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
  }
}

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = "GRANT SELECT ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
  }

  delete {
    statements = "REVOKE SELECT ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
  }
}

This will work, but isn't very DRY. We can improve on this solution with for_each (or count if you require unknown values). For example:

locals {
  snowsql_statements = {
    first = {
      create = "GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
      delete = "REVOKE ALL PRIVILEGES ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
    },
    second = {
      create = "GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
      delete = "REVOKE ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
    },
    third = {
      create = "GRANT SELECT ON ALL TABLES IN DATABASE ${snowflake_database.database.name} TO ROLE ${snowflake_role.role.name};"
      delete = "REVOKE SELECT ON ALL TABLES IN DATABASE ${snowflake_database.database.name} FROM ROLE ${snowflake_role.role.name};"
    }
  }
}

resource "snowsql_exec" "dcl" {
  for_each = local.snowsql_statements
  name     = each.key

  create { statements = each.value.create }
  delete { statements = each.value.delete }
}

[vlucas-caylent]

I think your last example will work good. But i have to think how i will export each value from the lists and then execute like your example. Thanksss