Support Dual Passwords

[e-r-holt]

Terraform Version

Terraform v1.1.6

Affected Resource(s)

• mysql_user_password
• mysql_user

Expeted behavior

The terraform resources should support the MySQL Dual password feature introduced in MySQL 8.0

resource "mysql_user" "default" {
  user               = var.username
  host               = "localhost"
  plaintext_password = "password-latest"  
  old_plaintext_password = "password-old"
}

OR 

resource "mysql_user_password" "default" {
  user    = mysql_user.default.user
  pgp_key = "keybase:joestump"
  retain_old_password = true
}

then the two passwords could be accessed

output "latest_password" {
  value = mysql_user_password.default.encrypted_password
}
output "old_password" {
  value = mysql_user_password.default.old_encrypted_password
}

Actual Behavior

Existing functionality only supports one password

resource "mysql_user" "jdoe" {
  user = "jdoe"
}

resource "mysql_user_password" "jdoe" {
  user    = mysql_user.jdoe.user
  pgp_key = "keybase:joestump"
}

Important Factoids

Password rotation is paramount to good security, but automating this process poses a risk to services using a password at the time it gets rotated. To avoid breaking operations, I want two active passwords for a given user. This allows me to rotate the latest password frequently, and trust that a running server will be replaced by a upcoming soon^TM deployment, and pull the newest password.

Eventually, all servers will pull the latest password during a new deployment, and the old one will not be actively used by the time it is rotated in ~15-30 days

References

• WL#11540: Support 2 active passwords per user account
• Usage

[petoju]

@e-r-holt I think one could emulate that by having a differently specified user. Like 'user'@'%' can be also specified as 'user'@'0.0.0.0/0.0.0.0'. One would have to be careful about which user gets grants, but that would be it.

Any implementation with the new feature will have some issues as this change is stateful.

That said, I'm ok with implementation that would use this

  retain_old_password = true

as long as someone provides a patch. I am not planning to write it in the near future.