CloudSQL failed to login with root due to caching_sha2_password

Question

I've a CloudSQL private instance I access using CloudSQL Auth Proxy running in a container hosted to a bastion host and listening from 127.0.0.1. The RDBMS is MySql 8. The instance was provisioned by Terraform.

I usually connect to this using another container with mysql client this way:

gcloud compute ssh --zone "<bastion zone>" "<bastion name>" --project "<project id>" --tunnel-through-iap --command "sudo docker run --rm --network=host -it mysql mysql -u root -p -h 127.0.0.1"

But when trying to login as 'root' i've this error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

This was totally unexpected because CloudSQL has legacy athentication enabled. In facts, logging with another user created by Terraform (yes, this happens only with root):

mysql> select user,host,plugin from user;
+-------------------+-----------+-----------------------+
| user              | host      | plugin                |
+-------------------+-----------+-----------------------+
| <the other user>  |           | mysql_native_password |
| root              | 127.0.0.1 | mysql_native_password |
| root              | ::1       | mysql_native_password |
| root              | localhost | mysql_native_password |
+-------------------+-----------+-----------------------+

exactly as it should look.

I also added this flag to the instance terraform setup:

database_flags {
  name  = "default_authentication_plugin"
  value = "mysql_native_password"
} 

and reboot (replacing the instance is unfortunately not an option). No way: when trying to login as root, a sha2 connection is requested although it shouldn't.

i'm totally puzzled, this is a very unexpected behaviour. Any idea?

Answer 1

Although this didn't explain why a sha2 authentication was required with a legacy user, this is how I was able to login with the root user.

The point is that with MYSQL8 the behaviour of the root user was changed and Google disincourage to use it.

Btw, as @John suggested in the comment, I had to grant the root user to login from outside localhost. This is the command I used:

gcloud sql users set-password root --host=% --prompt-for-password --instance=<cloudsql instance name> --project=<project id>

(Indeed I used it to also refresh the password at the same time)

But, as said before, the root behaviour is changed and that user have actually no grants, making him useless.

Nonetheless the above command could be used to change the password for any user (assuming the caller has enough permissions to do that).