Changing database credentials after engine creation

[Venkata Siva Naga Tatikonda]

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.

[Mike Bayer]

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.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ed50fe16-f759-4d14-a111-62457f4009c5o%40googlegroups.com.

[Venkata Siva Naga Tatikonda]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Venkata Siva Naga Tatikonda]

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

[Venkata Siva Naga Tatikonda]

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

[Mike Bayer]

On Wed, Jun 17, 2020, at 5:17 PM, 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 ?

yes, the Session is making use of an Engine that you created with create_engine.

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.

This recipe takes place at the level of the Engine so the creation of the Session is not relevant to how it works.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a74188cd-aeba-4b27-a9b4-fac4b368cfa4o%40googlegroups.com.

[Venkata Siva Naga Tatikonda]

Hello Mike,

latest update, I've tried to keep hook separately out of session but still not working when database credentials changed

def get_engine(args):

      # our code to create engine

      return engine

def get_session(args):

      db = get_engine(args)

      # create scoped_session using sessionmaker by binding the engine returned from above

      return session

from sqlalchemy.engine import Engine

@event.listens_for(Event, "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

Please help let me know if I'm doing anything wrong here.

Thanks,

Pavan

[Mike Bayer]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b08c2722-d1c0-4c65-85c6-61b4e756680bo%40googlegroups.com.

[Mike Bayer]

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.

[Venkata Siva Naga Tatikonda]

Thanks Mike, able to achieve the desired results.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b08c2722-d1c0-4c65-85c6-61b4e756680bo%40googlegroups.com.

[Mike Bayer]

OK great, I was just throwing out assorted debugging techniques to help you find the problem

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/95ab9371-73fc-4835-be86-28fe7ae5f93co%40googlegroups.com.

[Venkata Siva Naga Tatikonda]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/95ab9371-73fc-4835-be86-28fe7ae5f93co%40googlegroups.com.

[Venkata Siva Naga Tatikonda]

Nevermind, I'm able to test this as well, thanks!