Getting updated credentials for connection pool after credential rotation

[mryoho]

I'm using asyncpg as the backend for encode/databases for a FastAPI application.

At application startup I create my database connection pools.

The database connection info is stored in a cloud provider's secrets manager and is rotated on a regular basis.

Right now, when that rotation happens, the application fails without an application restart after credential rotation because we don't currently employ a mechanism for pulling down new credentials in the event of a aysyncpg.exceptions.InvalidPasswordError.

I'm trying to figure out the best way for handling this credential rotation situation without needing to restart the application.

Is this where I would pass a setup coroutine to the create_pools() function?

[mryoho]

Looking more at the source code, it doesn't seem like passing the Pool object a different Connection class, a setup coroutine or an init coroutine will work.

For the time being, when ever FastApi is getting a database connection I'm iterating through the Pool's PoolConnectionHolder objects and if PoolConnectionHolder.is_connected() is false for all of them then I am trying to reconnect and if I get an asyncpg.exceptions.InvalidPasswordError then am getting the secret from secrets manager again, recreating the Pool and then reconnecting.

It still feels a little clunky, but it seems to be working.

If there are better suggestions out there, I'd love to hear them. Thanks!

EDIT: Thinking more about this, I think there is probably another case where the approach doesn't work: if some connections in the pool are connected and some are disconnected and then there is enough API activity to warrant the additional pools connecting (I'm sort of assuming that inactive connections that are closed are not re-connected unless they are needed, I haven't looked at the code that closely) then those connection attempts would probably fail. I think the API would then 500 until all connections in the pool were disconnected and then recover. Not ideal.

[elprans]

We should add a method to replace the connection parameters on the pool gracefully. Should be a relatively easy fix.

[mryoho]

@elprans Cool! Yeah, doesn't seem too terribly complex. Would you like me to create an issue? I can maybe attempt a PR too, though like I said I'm not too familiar with the code base.

[mryoho]

@elprans I still don't quite understand if there's a built in way that I can add some exception handling to every fetch operation to trigger getting the secret again in the case where the database credentials have changed. Can either a custom Connection class or setup coroutine do this?