Nevermind, I'm able to test this as well, thanks!
On Friday, June 19, 2020 at 5:39:34 PM UTC-7, Venkata Siva Naga Tatikonda wrote: > > Hello Mike, > > Need another suggestion similar to this: > > Instead of fetching password from AWS SecretsManager every time, we want > to establish the connection using the cached credentials and it that > doesn't work then only we would make AWS API call for secrets. > > @event.listens_for(db, "do_connect") > def receive_do_connect(dialect, conn_rec, cargs, cparams): > try: > connection = <required method for mysql dialect> > return connection > except: > secret = get_new_secret() > cparams['password'] = secret > return None > > However, I'm not quite sure what DBAPI or sqlalchemy function to use which > can return the connection similar to how mysql dialect normally does. > > We are creating engine like this, mysql://user:password@host/database. If > we don't provide in dialect+driver format, what does sqlalchemy uses by > default to establish DBAPI connections ? > > Also, need some more clarity about these statements here: > > "Return a DBAPI connection to halt further events from invoking; the > returned connection will be used." > Does above return raw/low-level DBAPI connection here for that particular > request/operation or this connection still be integrated with QueuePool > provider ? > > > "Return None to allow control to pass to the next event handler and > ultimately to allow the dialect to connect normally, given the updated > arguments." > How does this work in above case where we just used mysql:// when creating > engine? Which backend library it uses to communicate with mysql server for > DBAPI connection? Do we have any documentation or link I could use to > understand this better? I'm more interested in understanding 'ultimately > to allow the dialect to connect normally' this in details on how exactly > (like what functions it uses) it works. > > Note:- I've looked at the code for base.py/mysqldb.py in > sqlalchemy.dialects.mysql but couldn't quite get it. > > Thanks for your help in advance. > > Regards, > Pavan. > > > > > On Friday, June 19, 2020 at 6:37:51 AM UTC-7, Mike Bayer wrote: >> >> 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 sqlal...@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/13fda2bc-7806-4281-a9ec-d4c62ed69d08o%40googlegroups.com.
