Add deletion_protection for google_sql_database

[zculek-fb]

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

[codyoss]

@bhshkh is this still wanted/needed?

[bhshkh]

@bhshkh is this still wanted/needed?

Yes