No support for password_validation_policy in cloudsql-instance

[lyricnz]

Describe the bug
There is no current support in the cloudsql-instance module for setting the password_validation_policy, which means the new database shows up in the console with some security warnings out of the box:

This is supported in the upstream module
https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance

And reapply the module (after manually making a change) will revert:

  ~ resource "google_sql_database_instance" "primary" {
        id                             = "sjr-db"
        name                           = "sjr-db"
        # (15 unchanged attributes hidden)

      ~ settings {
            # (15 unchanged attributes hidden)
          - password_validation_policy {
              - complexity                  = "COMPLEXITY_DEFAULT" -> null
              - disallow_username_substring = true -> null
              - enable_password_policy      = true -> null
              - min_length                  = 10 -> null
              - reuse_interval              = 0 -> null
            }

Environment

❯ terraform -version
OpenTofu v1.8.5
on darwin_amd64
+ provider registry.opentofu.org/hashicorp/google v6.12.0
+ provider registry.opentofu.org/hashicorp/google-beta v6.12.0
+ provider registry.opentofu.org/hashicorp/random v3.6.3
❯ git rev-parse --short HEAD
6127efb3

To Reproduce
Create a DB using any settings, go to console, see the three warnings above. (I'm going to try and deal with those next)

Result
Warnings showing up in GCP console

[lyricnz]

Happy to raise a PR if you can let me know how you'd like it done? Copying a bunch of password_validation_policy from the upstream provider feels like creating a bunch config that's a pain to maintain...

https://github.com/hashicorp/terraform-provider-google/blob/main/google/services/sql/resource_sql_database_instance.go#L650

[ludoo]

Happy to raise a PR if you can let me know how you'd like it done? Copying a bunch of password_validation_policy from the upstream provider feels like creating a bunch config that's a pain to maintain...

https://github.com/hashicorp/terraform-provider-google/blob/main/google/services/sql/resource_sql_database_instance.go#L650

We have a few pointers in our contributing guide, for something like this we typically

• bundle related attributes into a top-level variable
• strings that actually behave like enums (complexity?) are enforced via variable validation
• optional attributes have a default null value and we leave the provider/API infer a default when needed

If you have the time for a PR don't overthink it too much, we'll review and discuss things together. And thanks of course!

[ludoo]

@lyricnz I have added support for password policy settings to the module, but the resource/API documentation is not exhaustive, CloudSQL is not a product I typically use and I don't have the time to bring up a few instances and test this now.

Would you have time to check if

• the policy needs to be also be set on replicas
• all arguments really are mandatory (just try setting some of them as nulls)
• the default_complexity argument works (it's an enum in the API, but the provider docs don't explicitly say it)
• the policy generally works as you would expect

Once the above is done we can merge the change. Thanks!

[lyricnz]

Thanks man! We'll try that this week!

[lyricnz]

(sorry for the delay). Yes, applying the configuration applied the changes to the DB, but it's still showing the warning about no password policy. I think this refreshes periodically, so will check again in 24h

[ludoo]

Great, thanks for confirming! I'll wait before marking this close then. :)

[ludoo]

ach, it's closed already, my bad :) well then reopen please if it turns out we did not fix it

[lyricnz]

FYI: If I try and create a DB with

  #   root_password = null # generate password

  password_validation_policy = {
    enabled                     = true
    default_complexity          = true
    disallow_username_substring = true
    min_length                  = 10
    reuse_interval              = 5
  }

I get an error about root password doesn't meet password policy. I am forced to comment out the password_validation_policy block to create the DB, then enable it afterwards and (re) apply