Cloud SQL DB access denied unless also logged in to Cloud SQL Studio

[pod5123]

I am experiencing the following, reproducible, problem:

I have a MySQL 8.4 (Cloud SQL) database and a PHP8.3 GAE webapp. I am using mysqli for the PHP.

I have granted the appropriate privileges to my database user.

When I am trying to connect to the google cloud instance and database using PHP MySQLi, Google says that the access is denied with the password.

Unless I simultaneously log in on their web interface into Google SQL Studio with the same database user. At that moment my PHP can also connect.

I'll be happy to post more info as needed.

[mokit]

Hi, @pod5123.

When your GAE web app attempts to connect to Google Cloud SQL, it's usually necessary to use the Cloud SQL Proxy to securely handle the connection between your app and the Cloud SQL instance. Ensure that your PHP application (running on App Engine) is properly configured to use the Cloud SQL Proxy to establish the database connection. While this is typically managed automatically within the Google Cloud environment, you may need to confirm that the instance connection name is correctly specified.

Sometimes, the issue might be with the network configuration (such as firewall rules or IP whitelisting) for Cloud SQL and App Engine. The firewall rules might be preventing direct access unless a web interface session is active. Check the firewall settings of your Cloud SQL instance. You need to ensure that App Engine is allowed to access the Cloud SQL instance.

Double-check your password to ensure it's correct. If it contains special characters, verify that your MySQL client library handles them properly. Additionally, confirm whether you're using username/password authentication or IAM-based authentication.

Regards,
Mokit

[pod5123]

Thank you, I'll look into these tomorrow.

[pod5123]

Dear @mokit,

I have checked the setup as much as I could.

The Database is within the app in question and the description says that it should be authorised to connect to the DB instance (this is a help message in that area of the web interface and not actual data). And yes, connecting to the instance is fine, it is the MYSQL database that cannot be accessed.

Built-in authentication is used for the actual mysql user.

The password with only the minus sign as special character also does not work - but the system uses the google default setup for the PHP anyway, as far as I know.

When the password of the user is removed, the connection works OK, but I am not sure if this is a good idea.

The connection is set up for "public IP" but no networks are authorised. Private IP is not set up.

[mokit]

It indicates that IAM-based authentication is being used, likely with automatic password rotation.

[pod5123]

Thank you, I have a hunch where the problem might originate. I'll investigate further in the coming days.

[pod5123]

Well the only way I could get things to work was to switch to a MySQL 8.0 instance rather than the 8.4 I originally had in mind. Thank you for the help @mokit !

[simon-peek-poke]

Hello. Sorry to jump in but I'm having a very similar issue trying to update a MySQL 5.7 server to 8.4. Any new users can't login from App Engine (using PDO MySQL driver and the CloudSQL socket, service account added in IAM) unless I've logged in with them myself using PHPmyAdmin via direct IP connection. Once I've done that App Engine will connect fine.

Our product requires automating creating database users for data segregation so we need a solution that doesn't involve manual intervention.

You mention IAM authentication being at fault. Are there any settings I should be aware of that might cause this? The problem only occurs when using the new default auth plugin caching_sha2_password. The only workaround I've found so far is to force a different mysql auth plugin to be used but that introduces other restrictions.

Any guidance appreciated.

[KalleDelorean]

Just to add some more information to this:

If you run a SQL query calling "FLUSH PRIVILEGES;" to the server, then all the accounts are put into this non-working state where you have to manually connect with each one to revive them.

Also, and more worryingly, whenever there is maintenance done from Googles end, you achieve the same effect.

In essence, your solutions become ticking time bombs.

Does anyone know any workaround other than downgrading to MySQL 8.0?