RDS/Aurora IAM Database Authentication and Connection Pooling

[daniel-munch-cko]

Hello,
I've run into connection pooling issue when authenticating to MySQL using IAM authentication.

When using IAM authentication, my service created a new authentication token for each database connection, which becomes part of the connection string as the password. These tokens contain a timestamp with a precision of seconds. This basically results in a unique connection string for every second.

During a load-test, I've quickly hit the maximum number of connections on my Aurora instance. I've been tuning the maxpoolsize parameter, but to no avail - Only then I realised that connection pools are defined through connection strings and since they were changing every second, I've had plenty of connection pools with only a few connections pooled.

As a work-around I'm now caching the authentication token - they're valid for 15 minutes. This works but isn't ideal - After 15 minutes, the first connection pool gets drained while a new connection pool gets populated. Effectively this means that I can only use half of the available maximum connection per instance as maxpoolsize.

I was thus wondering if there's a better way to handle this? Looking into the code one way of doing this would be to exclude the password from the normalizedConnectionString around here https://github.com/mysql-net/MySqlConnector/blob/master/src/MySqlConnector/Core/ConnectionPool.cs#L394 - But I'm not sure about the implications. Potentially this might be configurable?

Looking forward to having your thoughts on this!

Thanks a lot for the great library by the way.
Daniel

[bgrainger]

I assume that open connections aren't closed after 15 minutes; it's just that the authentication token can't be used to open new connections?

I do note that the documentation says:

We recommend the following when using the MySQL engine:

• Use IAM database authentication as a mechanism for temporary, personal access to databases.

That makes me think it's not recommended for long-lived (i.e., connection pooling) service accounts. However, I haven't found any documentation specifically proscribing it.

It looks like at least one other user has used IAM authentication in the past, and just decided to rotate auth tokens (and thus connection pools) every 10 minutes: #268 (comment)

Looking into the code one way of doing this would be to exclude the password from the normalizedConnectionString around here

I think this would be a very simple change (to change to connectionStringBuilder.GetConnectionString(includePassword: false)). However, the obvious downside is that it would let code obtain a connection from the pool without actually knowing the password. (There would be no way to determine that the new password in the connection string is valid until a new connection needs to be opened.) This defeats the protections provided by PersistSecurityInfo.

For safety, we could opt into the new behaviour, e.g., by adding a new connection string option IgnorePasswordForPooling=true, or by prefixing the password with something like IamToken: that would indicate to the driver that it's a rotating token that should be ignored (at the risk of colliding with a password that actually starts with that string).

[bgrainger]

From PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1322 (comment):

Is there any callback mechanism available when a new connection is needed?

That seems like it could be the most straightforward way to solve this problem, e.g., add Func<string> MySqlConnectionStringBuilder.PasswordCallback { get; set; } that's called by MySqlConnector whenever a new connection is opened and a password is needed.

That actually wouldn't work in practice: it would need to be a constructor parameter that's passed to MySqlConnection (along with the connection string). This would likely require coordination with Pomelo, so that it can expose an API for the user to provide a password callback to Pomelo, which would be passed into any MySqlConnection objects it creates.

Or, MySqlConnection could expose a public static Func<string, Task<string>> GetPasswordAsync callback that's given a connection string and returns the password. Users could set that to inject dynamic passwords, bypassing any intermediate library (Pomelo or any ORM). Perhaps it would be required for the Password to be set to *dynamic* or something empty in order for the callback to be invoked, since this is a global callback.

[mguinness]

A global GetPasswordAsync callback would be a nice solution. Rather than using a magic string like "dynamic" could you only invoke the callback (if registered) if password / pwd is either empty or missing in the connection string? The callback user should probably either use UserID or ApplicationName to determine if they need to inject password into the returned connection string.

[bgrainger]

Invoking the callback for an empty password is a much better idea.

[bgrainger]

Might want to use an event so multiple clients in the same process can add their own handler to the chain?

[bgrainger]

I've written up an API design for this here: #1056

Please upvote/downvote/comment with any feedback.

[bgrainger]

Do you know if there's any need for this callback to be async? Based on the docs and the code, it looks like the password callback can be completely synchronous.

(An asynchronous callback (that performs network I/O) would raise the problem of how the request it makes is authorised to begin with; that seems like it would introduce more problems, so I suspect it's not a use case that needs to be solved.)