Additionally, enable echo_pool=True on your create_engine which will log when connections are being made. Put logging and/or print statements inside your event handler to ensure it's being invoked when it is expected. run some standalone tests with a short script first to make sure it does what's expected before integrating into the bigger application.
On Thu, Jun 18, 2020, at 3:35 PM, Mike Bayer wrote: > > > 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/37495435-21e4-451f-97a1-258e2b299505%40www.fastmail.com.
