Modify the password for a connection pool

[terrisgit]

RDS authentication tokens expire. These tokens are passed to a connection pool via the password field. When a token expires, the connection pool becomes permanently unable to create new database connections. Thus the pool must be discarded and a new pool created which is a huge hassle because -- if you want your application to exit gracefully -- connection pools must be closed explicitly during shut-down.

This is no different from the case where a database user's password has been changed. However, while this is a rare occurrence, auth tokens are meant to expire.

Would it be possible to modify the password used by a connection pool? Or is this already possible?

[jettify]

We do not have explicit API for that, change of token usually requires restart of affected services in simples case. You can access private attribute in the pool and change password there, but after that all alive connections should be reconnected with new password.

[terrisgit]

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.html

An authentication token is a string of characters that you use instead of a password. Once you generate an authentication token, it's valid for 15 minutes before it expires. If you try to connect using an expired token, the connection request is denied.

I can't restart my app every 15 minutes. Existing connections are not terminated by the database server when an auth token expires. New connections are needed during an app's lifetime because (1) you told the pool to automatically remove idle connections (2) the database server was restarted (3) client lost network connection.

If you could let me know which private member to modify, that would be a sufficient workaround.

I'm an early adopter of RDS/IAM authentication tokens with aiomysql and I am very grateful this feature has been added. Eventually others will run into the expiration gotcha.

[jettify]

I not sure IAM auth is supposed to be used that way, from same doc:

We recommend the following:

Use IAM database authentication as a mechanism for temporary, personal access to databases. Don't use IAM database authentication if your application requires more than 20 new connections per second. Use IAM database authentication only for workloads that can be easily retried.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html

[jettify]

As for private attribute, password is sitting here: https://github.com/aio-libs/aiomysql/blob/e95df7407bd1ab4612c6307019ea08ab6171d1e8/aiomysql/pool.py#L44

This approach is not supported, and may be broken in next releases, I suggest not to do this.

[terrisgit]

Passwordless database access via SSL is our corporate security policy for all services. "Use IAM database authentication as a mechanism for temporary, personal access to databases" is funny because services aren't people and creating access tokens and using the cleartext plugin are far from human usable -- but this tech lends itself to automation really well. The other two are fine for us, in fact not creating connections all the time is why we are using a connection pool and retry is just something you do unless you're a new coder who hasn't learned the hard way how to write services that interact with database servers.

Regenerating auth tokens is a valid use case.

[terricain]

Using RDS tokens require TLS, and are signed but they don't to anything to stop replaying them :D. Would it not make sense to allow the password to be a callable, so something like this

def gen_pw():
    # Possibly do some if pw token is near expiry, generate a new one.
    return rds_client.generate_db_auth_token(...)

await create_pool(..., password=gen_pw)

Thoughts, as im happy to implement that as I'll need something similar in the near future.

[terrisgit]

that would be great. How about an auth failure callback too?

[terricain]

I'll need to look into that one, but yes a few times I've got the iam permissions wrong so the generated token does nothing :laughing:

[terrisgit]

Otherwise I can regenerate the token when I get an exception when running a sql command and retry. I already use the backoff module.

[jaygorrell]

Hitting this myself - would love a way to officially support RDS IAM.

[terrisgit]

I suggest getting above PR’s branch, merging in master, and building aiohttp locally to see if the PR works for you. If so that would further support merging in the PR. Otherwise a fork will be necessary. As I said a year ago, I drop the pool and create a new one when this happens. I wrote my own function that dispenses connections. It’s always an unpleasant surprise when this unexpected seemingly random error happens.