google_sql_database_instance: Allow root_password to set postgres user password upon instance creation

[BusiPlay]

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.

Allow root_password to set postgres user password

The root_password attribute currently shows the below documentation:
root_password - (Optional) Initial root password. Required for MS SQL Server, ignored by MySQL and PostgreSQL.
There is the following:
NOTE on google_sql_database_instance:

• Second-generation instances include a default 'root'@'%' user with no password. This user will be deleted by Terraform on instance creation. You should use google_sql_user to define a custom user with a restricted host and strong password.

This note only applies to MySQL 2nd Generation instances.

Postgres and SQL Server instances have no root user, but have postgres and sqlserver users respectively, that serve the same purpose. These users are not deleted by the provider, and while the sqlserver root_password can be managed by the provider, there is no mechanism to do so for the postgres user. This leads to an inconsistent and confusing experience depending on the database type.

Strictly speaking, there's no reason that the provider needs to delete the root user, the root_password attribute could be used here as well, but because the "root" user already has some management mechanism, this request should apply primarily to postgres.

• google_sql_database_instance

Potential Terraform Configuration

resource "google_sql_database_instance" "master" {
  name             = "master-instance"
  database_version = "POSTGRES_11"
  region           = "us-central1"
  
  root_password = "P@$$W0rd1"
  
  settings {
    tier = "db-f1-micro"
  }
}

References

• Enable root_password for postgres terraform-google-modules/terraform-google-sql-db#214

[c2thorn]

The API seems to let you specify rootPassword for Postgres without any complaints. Not sure why this was excluded in the first place, but it's possible there was a limitation during the initial implementation.

[asgoel]

Any update on this one?

[melinath]

It doesn't look like we're doing anything special here, just passing the value to the API. It looks like the API docs & TF docs have been updated and no longer say that this is limited to MS SQL. If there is any issue with the root password not being respected by the API, that would be an API issue, not a Terraform one.

[github-actions]

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 have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.