Add deletion_protection for google_sql_database

zculek-fb commented 1 year ago

Community Note

Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
If you are interested in working on this issue or have submitted a pull request, please leave a comment. If the issue is assigned to the "modular-magician" user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If the issue is assigned to a user, that user is claiming responsibility for the issue. If the issue is assigned to "hashibot", a community member has claimed the issue already.

Description

At the moment, it is only possible to set deletion_protection for Cloud SQL instance (google_sql_database_instance), but not for the database itself. Even when deletion_protection is set for the instance, database still gets destroyed prior to Terraform trying and failing to destroy the instance itself; e.g.:

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes
module.mysql_db.google_sql_user.default[0]: Destroying... [id=csql//csql]
**module.mysql_db.google_sql_database.default[0]: Destroying... [id=projects/myproj-staging/instances/csql/databases/csql]**
module.mysql_db.google_sql_user.default[0]: Destruction complete after 1s
module.mysql_db.random_password.user-password: Destroying... [id=none]
module.mysql_db.google_sql_database_instance.replicas["csql-replica"]: Destroying... [id=csql-read-replica]
module.mysql_db.random_password.user-password: Destruction complete after 0s
**module.mysql_db.google_sql_database.default[0]: Destruction complete after 2s**
╷
│ **Error: Error, failed to delete instance because deletion_protection is set to true. Set it to false to proceed with instance deletion**
│
│

I think the database itself is much more worth to be protected from deletion than the instance - the instance can easily be recreated, in a matter of minutes, but if the database is deleted and it's hundreds of GBs big, restoring from a backup could take quite some time. It would be great if we would be able to protect our databases from being destroyed/deleted.

I know that there is a possibility of using prevent_destroy lifecycle option, but it requires literal values:

Literal Values Only The lifecycle settings all affect how Terraform constructs and traverses the dependency graph. As a result, only literal values can be used because the processing happens too early for arbitrary expression evaluation. (https://developer.hashicorp.com/terraform/language/meta-arguments/lifecycle#literal-values-only)

meaning there's no possibility to use a variable for prevent_destroy, and no way to protect the database if using a template or a module:

│ Error: Variables not allowed │ │ on ../../modules/mysql/main.tf line 175, in resource "google_sql_database" "default": │ 175: prevent_destroy = var.db_prevent_destroy │ │ Variables may not be used here. ╵

╷ │ Error: Unsuitable value type │ │ on ../../modules/mysql/main.tf line 175, in resource "google_sql_database" "default": │ 175: prevent_destroy = var.db_prevent_destroy │ │ Unsuitable value: value must be known

Adding an option to set deletion_protection for the google_sql_database would make a lot of lives easier. :)

New or Affected Resource(s)

google_sql_database

Potential Terraform Configuration

Add a new boolean argument deletion_protection for google_sql_database:

resource "google_sql_database" "database" {
  name     = "my-database"
  instance = google_sql_database_instance.instance.name

  deletion_protection = true
}

References

b/298498407

rileykarson commented 1 year ago

Note: This field does have https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database#deletion_policy as a mitigation, although ABANDON is the other possible state.

zculek-fb commented 1 year ago

From my understanding, deletion_policy = ABANDON is actually meant for Postgres and to resolve the issue of not being able to delete the database from the API if there are non-superusers with access to the database.

So, if deletion_policy = ABANDON is set for mysql database, does that mean that if someone removes the database resource from Terraform config, database wouldn't be deleted? I will try to test this out, maybe it would be enough of a solution after all. But, I'd rather like for that someone that removed the config and possibly wasn't aware that it will destroy/delete the database, to be informed that they would have caused database deletion, but it failed. :)

BTW, is there any issue of implementing deletion_protection for the google_sql_database resource, or it was never implemented because deletion_policy = ABANDON is a good enough workaround? I couldn't find an answer to that.

c2thorn commented 1 month ago

@zculek-fb

So, if deletion_policy = ABANDON is set for mysql database, does that mean that if someone removes the database resource from Terraform config, database wouldn't be deleted? I will try to test this out, maybe it would be enough of a solution after all.

The check in the deletion logic is independent of the database type, so deletion_policy = ABANDON works the same for mysql.

But, I'd rather like for that someone that removed the config and possibly wasn't aware that it will destroy/delete the database, to be informed that they would have caused database deletion, but it failed. :)

Fair point, this is a silent state drop instead of an error. This is the first difference from a true deletion_protection. The second difference is deletion_protection should be true by default, where deletion_policy is DELETE by default.

hashicorp / terraform-provider-google