Can't connect via cloudsqlproxy with new SQL instance without whitelisting IP

[AndreasM]

Hey there, 

After years of using CloudSQL for MySQL with the cloudsqlproxy with several SQL instances I created a new SQL instance and suddenly faced the problem that I can't connect to the instance with a running cloudsqlproxy.

After a while I found out that I can connect when I whitelist my IP, which I never needed to do before in other instances. The GCP console in SQL->Connections clearly states "an authorised network OR the Cloud SQL Proxy" (see image attached below).

How do I connect?

- Install cloudsqlproxy on OS X via brew (latest version)

- cloudsqlproxy --projects=... -dir=<localdir>

- connect with my MySQL tool of choice using the unix socket path

How does the issue show up?

- I connect to the cloudsqlproxy successfully and the relevant unix socket path is printed out by cloudsqlproxy

- when connecting to the instance via my MySQL tool of choice it states that I have a username/password issue

- the cloudsql proxy shows a connection attempt

- I authorize my IP either by adding it to authorized networks manually or by doing `gcloud sql connect`

- I repeat connecting via my MySQL tool without disconnecting the cloudsqlproxy -> connection works

My CloudSQL MySQL 8.4 instance network setup:

- private IP & public IP (with default network)

- public IP without authorised networks (when I add my IP here it works, but it shouldn't be necessary)

- SSL encryption disabled because I am only using CloudSQL Proxy to connect

- private path disabled

Did anyone also have that issue or does anyone know how I can fix it? The big problem I face is that there are users who need to use the instance via the cloudsqlproxy which don't have a static IP.

[ms4446]

The issue you're experiencing occurs because the Cloud SQL Auth proxy is connecting to your Cloud SQL instance via its public IP address, and your client machine's IP address isn't whitelisted in the Authorized Networks. This is why adding your IP to the Authorized Networks resolves the issue temporarily.

In previous instances, you might not have faced this problem because:

• Authorized Networks may have included 0.0.0.0/0, allowing connections from any IP.
• The default settings for new instances might have changed, tightening security by requiring explicit IP whitelisting.

To fix this issue and allow connections via the Cloud SQL Auth proxy without whitelisting IP addresses, follow these steps:

1. Ensure Cloud SQL Admin API is Enabled:

• Go to the APIs & Services Dashboard in the Google Cloud Console.
• Verify that the Cloud SQL Admin API is enabled for your project.

2. Create a Service Account with Proper Permissions:

• Navigate to the Service Accounts page.
• Click Create Service Account.
• Provide a name and ID for the service account.
• Assign the Cloud SQL Client role to the service account.
• Click Done.

3. Download the Service Account Key:

• Find the service account you just created.
• Click on the Actions menu (three vertical dots) and select Manage keys.
• Click Add Key > Create new key.
• Choose JSON and click Create.
• Save the JSON key file securely on your machine.

4. Set the GOOGLE_APPLICATION_CREDENTIALS Environment Variable:

• Open your terminal.
• Run the following command, replacing /path/to/key.json with the path to your downloaded key file:

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"

5. Start the Cloud SQL Auth Proxy with the Instance Connection Name:

• Use the instance connection name in the format project:region:instance.
• Run the proxy with the following command:

cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=unix:<LOCAL_SOCKET_PATH>

• Replace <INSTANCE_CONNECTION_NAME> and <LOCAL_SOCKET_PATH> accordingly.
• Example:

cloud_sql_proxy -instances=my-project:us-central1:my-instance=unix:/tmp/mysql.sock

6. Connect Using the Unix Socket Path:

• Configure your MySQL client to connect using the Unix socket at <LOCAL_SOCKET_PATH>.
• Ensure that the MySQL username and password are correct.

Explanation:

• Cloud SQL Auth Proxy Behavior: By default, the proxy attempts to connect via the public IP if it's available. If your client IP isn't whitelisted, the connection is rejected.
• Solution via Cloud SQL Admin API: By authenticating the proxy using a service account with the Cloud SQL Client role, it connects securely over the Cloud SQL Admin API. This method doesn't rely on public IP addresses or Authorized Networks.

Additional Notes:

• Updating the Proxy: Ensure you're using the latest version of the Cloud SQL Auth proxy. Install or update it via Homebrew:

brew install --cask cloud-sql-proxy

• Avoiding Public IP Dependencies: Consider removing the public IP address from your instance if not needed. Use private IP connections if your environment allows it (e.g., via VPN or VPC peering).
• For Users Without Static IPs: This method allows users to connect securely without requiring their IP addresses to be whitelisted.

[AndreasM]

Thank you for your detailed answer.

To connect via service account & cloudsqlproxy I am already using gcloud (gcloud auth application-default login). My user has owner rights in the project and I additional assigned the Cloud SQL Client role.

That's how I use it:

- brew install google-cloud-sdk

- gcloud components install cloud_sql_proxy

- cloudsqlproxy ...

Due to your post I figured out that the latest version of cloudsqlproxy via gcloud components is `1.27.0` even after updating

When installing via brew I get version `2.14.0` (I had to use `brew install cloud-sql-proxy` - no cask available anymore)

Sadly after updating and using the newest version of cloudsqlproxy the behavior is the same. It works for all other instances, not for the new one.

The cloud-sql-proxy logs are:

```cloud-sql-proxy --unix-socket /Users/<myuser>/cloudsql <project>:<region>:<instance-connection-name>
2024/10/25 08:19:49 Authorizing with Application Default Credentials
2024/10/25 08:19:50 [<project>:<region>:<instance-connection-name>] Listening on /Users/<myuser>/cloudsql/<project>:<region>:<instance-connection-name>
2024/10/25 08:19:50 The proxy has started successfully and is ready for new connections!```

When I try to connect I get:

```Access denied for user 'root'@'cloudsqlproxy~<myip>' (using password: YES) (1045)```

The proxy then has additional log output:

```2024/10/25 08:21:57 [<project>:<region>:<instance-connection-name>] Accepted connection from
2024/10/25 08:21:57 [<project>:<region>:<instance-connection-name>] instance closed the connection```

When I whitelist my IP connecting with the exact same credentials (user/pw) and with the same approach (using cloudsqlproxy) works.

In this case the cloudsqlproxy logs just shows the first statement without the instance closed the connection part:

```2024/10/25 08:34:38 [<project>:<region>:<instance-connection-name>] Accepted connection from```

>The default settings for new instances might have changed, tightening security by requiring explicit IP whitelisting.

That would be unexpected - I'm used that google mentions such changes in the docs/descriptions very early.

> Avoiding Public IP Dependencies

My production environment uses the private IP via VPC peering, but I still need the public IP for other reasons.

Key points:

- `gcloud components install cloud_sql_proxy` should be removed/fixed as it is out of date

- The issue has not been resolved by using your mentioned steps

[ms4446]

The issue is likely due to MySQL's host-based authentication, where the root user isn't permitted to connect from cloudsqlproxy~<your_ip>. By updating the user grants to allow connections from this host or creating a new user with the appropriate permissions, you should be able to connect via the Cloud SQL Auth Proxy without IP whitelisting.

Please try the following steps:

1. Check MySQL User Grants:

   • Connect to your Cloud SQL instance using a method that currently works (e.g., after whitelisting your IP).
   • Run the following query to see the hosts from which the root user is allowed to connect:

   SELECT User, Host FROM mysql.user WHERE User = 'root';

   • You may find entries like 'root'@'localhost' or 'root'@'127.0.0.1' but not 'root'@'cloudsqlproxy~%'.

2. Modify the root User to Allow Connections from the Proxy:

   • Option A: Add a Host Entry for root:

   CREATE USER 'root'@'cloudsqlproxy~%' IDENTIFIED BY 'your_root_password';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'cloudsqlproxy~%' WITH GRANT OPTION;

   • Option B: Allow root to Connect from Any Host (Less Secure):

   CREATE USER 'root'@'%' IDENTIFIED BY 'your_root_password';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

   • Note: Allowing root access from any host ('%') is not recommended for security reasons. Use this only if you understand the risks.

3. Create a Dedicated User for Proxy Connections (Recommended):

   • Instead of modifying the root user, create a new user with appropriate permissions:

   CREATE USER 'proxyuser'@'cloudsqlproxy~%' IDENTIFIED BY 'secure_password';
   GRANT ALL PRIVILEGES ON your_database.* TO 'proxyuser'@'cloudsqlproxy~%';

   • Replace your_database with the specific database the user should access.
   • This approach is more secure and aligns with the principle of least privilege.

4. Ensure the Authentication Plugin is Compatible:

   • MySQL 8.0 uses caching_sha2_password by default, which may not be supported by older MySQL clients.
   • If your client doesn't support caching_sha2_password, change the authentication method:

   ALTER USER 'proxyuser'@'cloudsqlproxy~%' IDENTIFIED WITH mysql_native_password BY 'secure_password';

   • Confirm that your MySQL client supports the authentication plugin in use.

5. Update Your Connection Settings:

   • Use the new user credentials when connecting through your MySQL client via the Cloud SQL Auth Proxy.
   • Ensure that your client is configured to connect via the Unix socket provided by the proxy.

6. Verify the Cloud SQL Auth Proxy Connection:

   • Start the proxy with verbose logging to see detailed output:

   cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=unix:<LOCAL_SOCKET_PATH> -verbose
   

   • Replace <INSTANCE_CONNECTION_NAME> and <LOCAL_SOCKET_PATH> accordingly.
   • Check the logs to confirm that the proxy is connecting via the Cloud SQL Admin API and not via the public IP.

7. Check Cloud SQL Auth Proxy Connection Method:

   • By default, the proxy uses the Cloud SQL Admin API if properly authenticated.
   • If the proxy connects via the public IP, it may require IP whitelisting.
   • Ensure that the Cloud SQL Admin API is enabled for your project.
   • Go to the APIs & Services Dashboard and verify that the Cloud SQL Admin API is enabled.

8. Ensure Proper Authentication for the Proxy:

   • Since you're using gcloud auth application-default login, make sure your environment variable is set:

   export GOOGLE_APPLICATION_CREDENTIALS="$HOME/.config/gcloud/application_default_credentials.json"
   

   • Alternatively, use a service account with the Cloud SQL Client role, as previously described.

[AndreasM]

Thank you for your help!

>1. Check MySQL User Grants:

When I run 

SELECT User, Host FROM mysql.user WHERE User = 'root';

I get the default values as I didn't touch the root user :

User, Host
root, %
root, 127.0.0.1
root, ::1
root, localhost

 >2. Modify the root User to Allow Connections from the Proxy:

ad A)+B) I tried this with a new user as my root user already had `%` (see 3).

>3. Create a Dedicated User for Proxy Connections (Recommended)

My new user with allowed host

cloudsqlproxy~%

 also faces the same issue.

>4.Ensure the Authentication Plugin is Compatible:

- My mysql tool of choice supports caching_sha2_password as it always works when I allow per IP connection.

- MySQL 8.4 doesn't have mysql_native_password anymore so I can't change the identification plugin

>5. Update Your Connection Settings

I did, as said above facing the same issue

>6. Verify the Cloud SQL Auth Proxy Connection:

The newest version of cloudsqlproxy installed via brew doesn't have the flag -verbose, but `--debug-logs` instead, which I set.

The logs state Dialing <mypublicip:3307> which, as you described, seems to be the actual problem. The proxy should use the cloud sql admin api instead.

>7. Check Cloud SQL Auth Proxy Connection Method:

- The CloudSQL Aadmin API is enabled in my project

- There is a flag in the cloudsqlproxy `--sqladmin-api-endpoint` which points to https://sqladmin.googleapis.com per default - I tried to set it to some invalid url and then manually to the default to check if it is used and it seems it is used.

My logs still show `Dialing <mypublicip:3307>`. They also show `Authorizing with gcloud user credentials` -> this confirms that this is the used method.

Summary:

The issue is still not resolved by trying what you suggested, but with your answer I at least found out that it seems like the cloudsqlproxy tries to connect via public IP in some step which probably leads to the issue at hand.

[AndreasM]

I just found out that `Dialing <mypublicip:3307>` also shows up for instances where I don't have my IP whitelisted when connecting via the proxy. So that's not the issue and the proxy is very likely not using the public IP to connect.

[ms4446]

It appears that your connection issue with Cloud SQL Proxy stems from MySQL’s require_secure_transport setting. In MySQL 8.0 and above, this setting is often enabled by default, mandating all client connections to use SSL/TLS. When you whitelist your IP, the connection succeeds because your client automatically uses SSL in that direct scenario. However, without whitelisting, the Cloud SQL Proxy encrypts the connection between your machine and the Cloud SQL instance, but MySQL still expects the client itself to initiate SSL/TLS. This mismatch triggers the "Access denied" error when require_secure_transport is enforced.

To resolve this, you can configure your MySQL client to use SSL/TLS when connecting via the Cloud SQL Proxy. By specifying SSL in your connection settings, you satisfy MySQL’s security requirements without needing to whitelist your IP.

Here’s a quick approach to set up SSL in your MySQL client:

Command-Line Connection: For a terminal-based client, use:

mysql --socket=/Users/<myuser>/cloudsql/<project>:<region>:<instance-connection-name> --ssl-mode=REQUIRED -u root -p

Graphical Client Settings: In GUI clients, go to the connection settings and enable SSL/TLS, or set the SSL mode to “Required.” Many clients offer a “Skip certificate validation” option which can be helpful when connecting through the proxy.

Alternatively, you could disable require_secure_transport, though it’s not recommended in production due to security concerns. To disable, navigate to the instance settings in the Google Cloud Console, add require_secure_transport=off under Database Flags, and restart the instance. This adjustment will permit non-SSL connections through the Cloud SQL Proxy, but only use this approach if SSL configuration changes aren’t viable.

[AndreasM]

Thank you for your answer.

I did execute `show variables` in my running instance and it shows 

`require_secure_transport` = `OFF`.

In GCP CloudSQL this flag is not exposed - so I can't change it anyways.

Seems like the flag is not at fault here, at least in my case.

[ms4446]

Thanks for verifying the require_secure_transport setting. Since it's OFF, we can rule out SSL/TLS as the issue. Here are some additional steps that might help identify the root cause:

Check Authentication Configuration:

Sometimes, specific user@host configurations impact Cloud SQL Proxy connections. Since root@% already exists, ensure that any additional users are configured as user@cloudsqlproxy~% or user@%. Try creating a new user with user@cloudsqlproxy~% and verify its permissions.

Service Account Authentication:

If you're using gcloud auth application-default login, try switching to a dedicated service account. Create one with the Cloud SQL Client role, set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to the service account JSON file, and restart the proxy using:

cloud-sql-proxy --unix-socket /Users/<myuser>/cloudsql --auto-ip <project>:<region>:<instance>

Use the --auto-ip Flag:

Adding --auto-ip forces the Cloud SQL Proxy to connect through the Cloud SQL Admin API rather than public IP. This option can ensure secure API tunneling without IP dependencies:

cloud-sql-proxy --unix-socket /Users/<myuser>/cloudsql --auto-ip <project>:<region>:<instance> 

Cloud SQL Proxy Version:

Ensure you're using the latest Cloud SQL Proxy version (2.x series or higher), as updates often include better handling of IP and authentication requirements.

Verbose Logging for Debugging:

Use --debug-logs to see detailed logging:

cloud-sql-proxy --unix-socket /Users/<myuser>/cloudsql --auto-ip <project>:<region>:<instance> --debug-logs

This should show if it's truly using the Cloud SQL Admin API path or attempting a direct IP connection.

[AndreasM]

Thank you for your answer!

TLDR:

I tried everything you suggested, but still have the same problem as before.

Details:

> Check Authentication Configuration

I tried creating a new user with `cloudsqlproxy~%` and `%` already, didn't make any difference.

> Service Account Authentication

Same problem by using a service account only for that purpose. I verified that I am using the SA and not my glcoud credentials by adding other SQL instances to the cloudsql proxy command where I got 403 NOT_AUTHORIZED.

> Use the --auto-ip Flag:

I am already using the auto-ip flag in my command.

> Cloud SQL Proxy Version

- The latest version is 2.14.0 (https://github.com/GoogleCloudPlatform/cloud-sql-proxy/releases)

- I'm using 2.14.0 (cloud-sql-proxy --version gives 2.14.0)

[binrip]

Hi,
Im also having the same issue, can connect to old mysql instances without any issue, current documentation states we have to set "--get-server-public-key" flag, to connect using the Cloud SQL Auth Proxy and the caching_sha2_password plugin over a TCP socket connection. But Im having trouble setting this flag, i can't see this option.
 Documentation Link 

Appreciate your help, Thanks

[goviedo]

I have the same issue as you... I'm declining using the Cloud SQL Java Connector