OK great, I was just throwing out assorted debugging techniques to help you find the problem
On Fri, Jun 19, 2020, at 12:43 AM, Venkata Siva Naga Tatikonda wrote: > Thanks Mike, able to achieve the desired results. > > > On Thursday, June 18, 2020 at 12:36:15 PM UTC-7, 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 sqlal...@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/95ab9371-73fc-4835-be86-28fe7ae5f93co%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/95ab9371-73fc-4835-be86-28fe7ae5f93co%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/56875006-f8d2-4ddc-b590-ca6c61f4ab09%40www.fastmail.com.
