On Wed, Jun 17, 2020, at 8:59 PM, Venkata Siva Naga Tatikonda wrote: > 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.
wherever the create_engine() is being called, that's where this event must be set up. there must be only exactly one event established on this Engine object and it must be before the engine has been used to make any connections. that is, this event listener must be independent of any logic that is related to per-request / per-session / etc. must be global to the Engine. > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/b08c2722-d1c0-4c65-85c6-61b4e756680bo%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/9724c94b-c36f-4341-8605-fae57623dfcc%40www.fastmail.com.
