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
On Wednesday, June 17, 2020 at 5:59:50 PM UTC-7, 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.
>
> 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/c0741165-5fce-406a-aaee-802df9198387o%40googlegroups.com.
