Database switching when using authentication proxy results in authentication method error

[adamkirkton]

Software versions
MySqlConnector version: 2.3.7
Server type: MySQL 8.0.35 (using Google Cloud SQL service)
.NET version: 8.0

Describe the bug
The connection to my MySQL instance goes through Google's cloud-sql-proxy which is a little app that allows you to authenticate to the database using IAM authentication as well as manage SSL for you. All I provide via the connection string to MySqlConnector is a username and when I connect to the proxy and it then actually connects to MySQL, it does so with an OAuth token that it automatically rotates.

I am not specifying a database in the connection string because I have a multi-tenant setup with multiple databases and so want to switch the database at runtime. The problem becomes when I call ChangeDatabase or ChangeDatabaseAsync this will cause the else statement in TryResetConnectionAsync to be executed which ends up with an attempt to re-authenticate the user which ultimately results in an exception being thrown in SwitchAuthenticationAsync.

Exception
MySqlConnector.MySqlException (0x80004005): Authentication method 'mysql_clear_password' requires a secure connection. at MySqlConnector.Core.ServerSession.SwitchAuthenticationAsync(ConnectionSettings cs, String password, PayloadData payload, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 670 at MySqlConnector.Core.ServerSession.TryResetConnectionAsync(ConnectionSettings cs, MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 620 at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int64 startingTimestamp, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 72

Expected behavior
I was wondering if the re-authentication upon switching databases is strictly necessary or if it's rather done as a precaution to know when you are trying to access something you can't and letting you know immediately. If it is the latter case, and there was a configuration option somewhere to disable that functionality, I'd be all set.

Additional context
The primary reason I am trying to do this is get to a unified connection pool for connections to my server so I can effectively manage a pool. Otherwise, I end up with a pool per client database.

Thanks for the consideration!

[bgrainger]

MySqlConnector.MySqlException (0x80004005): Authentication method 'mysql_clear_password' requires a secure connection.

The Google Cloud SQL Auth Proxy is listening on a local port, right? (e.g., 127.0.0.1:1234) If so, this is probably something MySqlConnector should detect and allow, because we don't need to worry about the connection being intercepted in this scenario.

[bgrainger]

I was wondering if the re-authentication upon switching databases is strictly necessary

No, it's not. It's done because "Reset Connection" doesn't reset the database back to the one that was specified in the original connection string (or to "no database"), but reauthenticating does easily allow the database from the connection string to be selected (so that the connection is in a well-known state, whether or not it was filled from the pool or newly opened).

[adamkirkton]

The Google Cloud SQL Auth Proxy is listening on a local port, right? (e.g., 127.0.0.1:1234) If so, this is probably something MySqlConnector should detect and allow, because we don't need to worry about the connection being intercepted in this scenario.

That's correct about listening on the loopback address on a local port.

No, it's not. It's done because "Reset Connection" doesn't reset the database back to the one that was specified in the original connection string (or to "no database"), but reauthenticating does easily allow the database from the connection string to be selected (so that the connection is in a well-known state, whether or not it was filled from the pool or newly opened).

Ah, ok, that makes sense then. Thanks for the explanation!

[jkuek]

@bgrainger I think I have run into a similar issue.

My app is connecting to an AWS Aurora MySQL cluster using MySqlConnector 2.4.0. I was previously using 6 connection strings to 6 databases on the same server (identical connection strings apart from the database).

I have refactored to use a single connection with ChangeDatabaseAsync(). At the same time I switched from password in the connection string to IAM authentication, generating a token and passing it with dataSourceBuilder.UsePeriodicPasswordProvider().

After these changes there is a very high number of IAM authentication requests as seen on the Cloudwatch metrics, an order of magnitude higher than the simultaneous connections.

Is this because each call to ChangeDatabaseAsync() cause a re-auth request against the server?

[bgrainger]

Is this because each call to ChangeDatabaseAsync() cause a re-auth request against the server?

@jkuek Yes, that's likely the cause.

[jkuek]

Thanks for the clarification @bgrainger.

So dataSource.OpenConnectionAsync() returns an existing connection from the pool, but ChangeDatabaseAsync() still causes a re-auth request.

Is there any configuration to prevent the re-auth? There are some limitations on AWS IAM DB authentication.

[bgrainger]

Is there any configuration to prevent the re-auth?

#1534 (comment)

If you set Connection Reset=false, then the re-auth won't occur. But additionally, server variables won't be cleared, temp tables won't be deleted, etc.; your connection will have a "dirty" state. (I don't recommend this, but it is an option.)

[jkuek]

Thanks, I will try this. It might be my best approach is to revert to password auth instead of IAM to get around AWS restrictions on IAM auth requests. I had not factored in that every ChangeDatabaseAsync() would be a new authentication.

[bgrainger]

Perhaps the "reset connection" pathway in MySqlConnector could be updated to "ResetConnection(); ChangeDatabase();" (if the database has been changed) instead of "Reauthenticate();", which would preserve the benefits of resetting the connection (and possibly even be fewer roundtrips).

[bgrainger]

A problem with this suggestion is that the protocol provides no way (that I know of) to deselect the current database other than reauthenticating and specifying no default database to be used (in that auth packet); that's part of the reason behind the current implementation.

[jkuek]

@bgrainger Apologies if this is a dumb question, but why does ResetConnection() need to deselect the current database?

The MySQL API documentation explicitly says it does not reauthenticate on connection reset, so I'm assuming the current database isn't deselected or there is another mechanism: https://dev.mysql.com/doc/c-api/8.0/en/mysql-reset-connection.html

[bgrainger]

MySqlConnector does it because it wants MySqlConnection.Open() to put the connection in a known good state. If a new connection is opened, it won't have a default database (if one isn't specified in the connection string). If an existing session is retrieved from the pool, it also needs to have no default database.

This is done so that the details of connection pooling don't leak through to the calling application. (Even though, in many cases, it might not matter to the application what database--if any--is selected when an open connection is returned.)