Please add a deletion_protection field for google_sql_database_instance

[david-wb]

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

Today a changed the database_version field on a google_sql_database_instance resource from

database_version = "POSTGRES_11"

to

database_version = "POSTGRES_12"

This caused the database to be completely deleted, including the backups! Luckily this was a dev database, but there needs to be a better way to prevent this type of thing from happening.

I've considered changing the IAM role permissions under which terraform is being run so that deletion is prevented. Unfortunately, that solution isn't quite good enough. Other engineers on the project might run terraform locally and with different permissions and run into the same issue. I strongly believe both terraform-provider-google and GCP itself should provide mechanisms to prevent accidental sql instance deletion.

New or Affected Resource(s)

• google_sql_database_instance

Potential Terraform Configuration

# Propose what you think the configuration to take advantage of this feature should look like.
# We may not use it verbatim, but it's helpful in understanding your intent.

resource "google_sql_database_instance" "db_instance" {
  database_version = "POSTGRES_12"
  name             = "db"
  settings {
    deletion_protection = true

    tier = "db-f1-micro"

    ip_configuration {
      ipv4_enabled    = true
      private_network = "projects/project-${var.env}/global/networks/default"
    }

    backup_configuration {
      enabled = true
    }
  }

  region = "us-west3"
}

References

• #0000

[Chupaka]

@david-wb what was the plan? Was it "update in-place" or "destroy and create a new instance"?

Because looks like "lifecycle { prevent_destroy = true }" is out-of-the-box solution for such cases for any resource.

[david-wb]

@Chupaka True, I've started using lifecycle { prevent_destroy = true } which might suffice for this. What really bothers me is that if the database does get destroyed, the backups are destroyed as well, but that is a GCP problem not a Terraform problem.

[rileykarson]

@Chupaka: after setting a precedent for a deletion_protection field in Bigtable, we've decided it's something we'd like for databases generally within the provider. I think 3.45.0 will has the same for google_spanner_database, for example.

The difference between deletion_protection and lifecycle.prevent_destroy is that it's an opt-out field instead of opt-in. One of the most common operational errors users have is accidentally deleting resources, and databases are extremely difficult to recover! deletion_protection provides a much safer default behaviour, and one where users don't need to remember to apply the lifecycle block.

It also works in the case that the config for the resource is no longer present, unlike lifecycle. Check out this talk for an example that deletion_protection could have stopped!

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!