UNIX socket connection fails with access denied when caching_sha2_password is set.

[hulto]

In which file did you encounter the issue?

cloudsql/mysql/database-sql/connect_unix.go

Did you change the file? If so, how?

No files have been changed.

Describe the issue

Following this tutorial: https://cloud.google.com/sql/docs/mysql/connect-instance-cloud-run#go
With two changes:

• Enabling the caching_sha2_password database flag
• Authenticating over unix socket instead of with cloud connector by removing the INSTANCE_CONNECTION_NAME env var.

I run into an error where the application is unable to authenticate to the database using the Unix socket.

Steps to reproduce:

gcloud sql instances create quickstart-instance \
--database-version=MYSQL_8_0 \
--cpu=1 \
--memory=4GB \
--region=us-central1 \
--database-flags=default_authentication_plugin=caching_sha2_password \
--root-password=DB_ROOT_PASSWORD

gcloud sql databases create quickstart-db --instance=quickstart-instance

gcloud sql users create quickstart-user \
--instance=quickstart-instance \
--password=PASSWORD123@

gcloud iam service-accounts list

gcloud projects add-iam-policy-binding ccdc-red-team-infra \
  --member="serviceAccount:[email protected]" \
  --role="roles/cloudsql.client"

gcloud run deploy run-sql --image gcr.io/ccdc-red-team-infra/run-sql \
  --add-cloudsql-instances ccdc-red-team-infra:us-central1:quickstart-instance \
  --set-env-vars INSTANCE_UNIX_SOCKET="/cloudsql/ccdc-red-team-infra:us-central1:quickstart-instance" \
  --set-env-vars DB_NAME="quickstart-db" \
  --set-env-vars DB_USER="quickstart-user" \
  --set-env-vars DB_PASS="PASSWORD123@" \
  --region us-central1

https://run-sql-rtqmb25lsq-uc.a.run.app

Navigating to the app it's unable to load

Checking the logs we see Access denied

Workaround
A work around I found to this (aside from not using caching_sha2_password) is to manually authenticate to the SQL database using the cloud sql connector.
https://cloud.google.com/sql/docs/mysql/connect-auth-proxy

$ ./cloud-sql-proxy ccdc-red-team-infra:us-central1:quickstart-instance &

$ mysql --get-server-public-key -h 127.0.0.1 -u quickstart-user -p
Enter password: 

mysql>

I would like to be able to use the UNIX socket auth with caching_sha2_password because of the security and flexibility that option provides.
If there is a better place to post this issue happy to move it.

[jackwotherspoon]

@hulto Hi there,

This is currently a known limitation of the https://github.com/GoogleCloudPlatform/cloud-sql-proxy, the unix socket mode does not currently support caching_sha2_password plugin at the moment.

This is documented a little on https://cloud.google.com/sql/docs/mysql/connect-auth-proxy#connect-unix but I can probably add the warning to a few more spots to make it even more clear.

I created a tracking issue on the Proxy repo itself for this: GoogleCloudPlatform/cloud-sql-proxy#2317

[hulto]

Thanks Jack!
I had read that warning three times I swear but missed the last sentence 😅

Did UNIX socket auth with caching used to work?
I had a project use it successfully ~3months ago.

[jackwotherspoon]

Did UNIX socket auth with caching used to work?
I had a project use it successfully ~3months ago.

@hulto From my understanding it has never been supported/worked, so that is strange about your project...

If you had logged in via another method with the same DB user (Connector, direct TCP connection etc.) then it could be that the result was cached and you were then able to login via UNIX socket with the cached entry from the DB.

[hulto]

Thanks for your help Jack!