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 [email protected].
> 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 [email protected].
> 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 [email protected] <javascript:>.
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/eb65fbdd-a59e-447d-9345-5d83704ce0edo%40googlegroups.com.
