Hello Mike, I've embedded the following code within the get_session function where the get_engine function returns the engine object. However, I'm still seeing that the rotated database credentials are not being used when creating new connections
def get_engine(args): # our code to create engine return engine def get_session(args): db = get_engine(args) @event.listens_for(db, "do_connect") def receive_do_connect(dialect, conn_rec, cargs, cparams): secret = get_new_secret() # used our custom code to get password from secrets manager cparams['password'] = secret # create scoped_session using sessionmaker by binding the engine returned from above return session We import get_session from the respective module and import it into other application related modules where it needs database communication. Here're the steps I followed for my testing: 1. Initial working database credentials in both database & AWS SecretsManager 2. Ran tests and all went good (created new connection & checked out from pool for this step) 3. Closed session (It returned the connection back to pool, did rollback-on-return) 4. Waited for 6 mins (pool_recycle is 5 mins) and also rotated credentials in both DB & AWS SecretsManager 5. Right after 6th minute, exceeded timeout; recycling and closed connection 6. Tried to create new connection but failed, "Error on connect(): (1045, \"Access denied for user '<username>'@'<host>' (using password: YES)\")" 7. It kept re-trying as we wait for 3 mins 8. We reverted the database credentials to old set of user/pass where tests worked in step#2 and its able to created new connection & checkout from pool 9. Ran tests again and succeeded this time. Would you be able to take a look at my scenario and provide some insight on the behavior ? Thanks, Pavan On Wednesday, June 17, 2020 at 3:10:41 PM UTC-7, Venkata Siva Naga Tatikonda wrote: > > Basically, within get_session function we call get_engine method and if an > engine already exists it skips the creation and uses it. If an engine > doesn't exist then it will create one and uses it. After that, we create a > sessionmaker object by binding that engine & some other arguments and then > create a scoped_session. > > Thanks, > Pavan > > On Wednesday, June 17, 2020 at 2:17:35 PM UTC-7, Venkata Siva Naga > Tatikonda wrote: >> >> Hello Mike, >> >> Thanks for your insight and response. >> >> Just want to let you know that, we are using scoped_session with a >> session_factory object to db connections. Does this approach still suitable >> for connections using session ? >> >> Also, we have custom module where we have separate functions for >> generating an engine and creating session object and we import this custom >> module in other application python files to create and close sessions >> accordingly. >> >> Thanks, >> Pavan. >> >> On Wednesday, June 17, 2020 at 5:35:59 AM UTC-7, Mike Bayer wrote: >>> >>> We're going to need an FAQ entry for this since this now comes up >>> regularly for everyone using AWS. >>> >>> There are two methods to manipulate the parameters sent to connect that >>> are independent of the URL. They are both described now at >>> https://docs.sqlalchemy.org/en/13/core/engines.html#custom-dbapi-args >>> and you probably want to use the "do_connect" event. >>> >>> so you have the pool_recycle, that's good. the next part is the event >>> is like this: >>> >>> from sqlalchemy import event >>> >>> db = create_engine('mysql://<aws url>') >>> >>> @event.listens_for(db, "do_connect") >>> def receive_do_connect(dialect, conn_rec, cargs, cparams): >>> secret = get_new_secret() >>> cparams['password'] = secret >>> >>> Above assumes you are setting just the password, but "cargs, cparams" >>> are the literal arguments passed to mysqldb.connect(), so you can put >>> whatever you need into either of those collections (modify the list and/or >>> dictionary in place). I'm assuming you have some function that can >>> retrieve the latest credentials. >>> >>> On Wed, Jun 17, 2020, at 1:28 AM, Venkata Siva Naga Tatikonda wrote: >>> >>> Hello Everyone, >>> >>> Need some suggestion/insight on some use case we have: >>> >>> We have python django web application which uses sqlalchemy v1.3.13 >>> (mysqldb) to communicate with AWS Aurora (RDS). This application uses AWS >>> Secrets Manager for managing database credentials and utilizing sqlalchemy >>> (w/ mysqldb & queuepool) to read user/password during application start-up >>> via settings.py/manage.py. >>> >>> For security reasons, we have to rotate database credentials frequently >>> and for that we are using AWS Lambda to update in the Aurora DB & secrets >>> manager. We are using pool_recycle w/ 5 mins and also MYSQL database issues >>> a disconnect if there is any connection is open & idle for more than 8 >>> hours, so when this happens and pool creates a new connection then it fails >>> the authentication. We don't see anyway for engine object to >>> reload/refresh/re-read updated credentials other than re-deploying or >>> restarting our services for this issue. >>> >>> Is there any documentation on how we could re-create/reload engine or >>> other mechanisms to handle/address this situation ? >>> >>> >>> Thanks, >>> Pavan. >>> >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to sqlal...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/ed50fe16-f759-4d14-a111-62457f4009c5o%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/ed50fe16-f759-4d14-a111-62457f4009c5o%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> >>> -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b08c2722-d1c0-4c65-85c6-61b4e756680bo%40googlegroups.com.