Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

RDS/Aurora IAM Database Authentication and Connection Pooling #800

Closed
daniel-munch-cko opened this issue Apr 16, 2020 · 11 comments · Fixed by #1060
Closed

RDS/Aurora IAM Database Authentication and Connection Pooling #800

daniel-munch-cko opened this issue Apr 16, 2020 · 11 comments · Fixed by #1060
Milestone

Comments

@daniel-munch-cko
Copy link

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
Copy link
Member

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
Copy link
Member

bgrainger commented Feb 6, 2021

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
Copy link
Contributor

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
Copy link
Member

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

@bgrainger
Copy link
Member

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

@bgrainger
Copy link
Member

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

Please upvote/downvote/comment with any feedback.

@bgrainger bgrainger added this to the 2.0 milestone Oct 16, 2021
@bgrainger
Copy link
Member

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.)

bgrainger added a commit to bgrainger/MySqlConnector that referenced this issue Oct 17, 2021
Allows the password to be provided dynamically on a per-connection basis; this is useful to preserve efficient connection pooling for Aurora RDS using IAM authentication.

Signed-off-by: Bradley Grainger <bgrainger@gmail.com>
@daniel-munch-cko
Copy link
Author

Nice, thanks a lot for this!

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.

This overload of GenerateAuthToken you link to requires an instance of AWSCredentials to be available - These may need to be obtained through network I/O, depending on the underlying AWS authentication mechanism chosen (e.g. the application might run on an EC2 instance, and access to Aurora is only granted to this EC2 instance, so the application needs to get temporary credentials through the AWS Metadata Service - The temporality of the Aurora password also comes through this).

The AWS.SDK hides away a lot of the complexity of credentials retrieval (e.g. Other overloads of GenerateAuthToken request it through some factory), and does a lot of caching, and might fire off a synchronous network call as well.

(An asynchronous callback (that performs network I/O) would raise the problem of how the request it makes is authorized 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.)

Maybe the explanation above already gives a bit of an answer - network I/O might need to happen during retrieval of new AWS credentials, which are required to generate a new auth token. However, the AWS.SDK AFAIK, as of today, doesn't provide a truly asynchronous code-path to retrieve these AWS credentials - There are some occurrences of GetCredentialsAsync in their code, but they all lead to a call to a synchronous method. (Yeah, it's not pretty).

So from my perspective, yes, there might be a case for an asynchronous callback, but it's not of much use for now at least for Aurora, since the AWS.SDK doesn't give us a fully asynchronous code path.

@bgrainger
Copy link
Member

Thanks for the details. I think I'll leave it as-is for now, since this feature is targeted at AWS, and most users would have to return Task.FromResult(authToken), defeating the purpose of an async-first API.

It perhaps could be "upgraded" in the future by adding another property, ProvidePasswordAsyncCallback, (which would take precedence).

@viveks7
Copy link

viveks7 commented Aug 23, 2023

@bgrainger Any update on the ProviderPasswordAsyncCallback? E.g., if we care using the Azure Managed Identity, it has both async and sync variants fetching the token.

@bgrainger
Copy link
Member

@viveks7 I recommend using MySqlDataSourceBuilder.UsePeriodicPasswordProvider for async password fetching support.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Development

Successfully merging a pull request may close this issue.

4 participants