password rotation after engine created

[Srinu Chp]

Hello Everyone,

We have application, while start up we initialize prepare engine args, configure orm. Once engine created we keep in session. Recently peer team introduced password rotation and for now as work around we are manually restarting the docker container to fetch latest password from secret client. I am trying to automate the process and implemented  @event.listens_for(engine, "do_connect"). I have impression that every db connection will be fetched from pool and if pool exhausted or expired then we fetch new connection. But every time do_connect listener is called and it is taking 3-5 secs. For complex tasks it is taking long time. Can you please suggest best approach for password rotation? I tried below code, before password change I get this error "RecursionError: maximum recursion depth exceeded while calling a Python object"

@event.listens_for(engine, "do_connect")

try:
log.info("receive_do_connect success")
return engine.connect()
except SQLAlchemyError as err:
log.info("error receive_do_connect ")

<call secert client for latest password>

Regards,

Srinu

[Mike Bayer]

we have a recipe for this scheme here:

https://docs.sqlalchemy.org/en/14/core/engines.html#generating-dynamic-authentication-tokens

no need to connect() inside the event handler, just update the parameters with the new password.

--

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/3884b3c2-8814-494b-9d47-bc1304ab748cn%40googlegroups.com.

[Srinu Chp]

Hello Mike,

Thank you very much for prompt response. yes, I tried to update password and every thing works fine but every time I need to fetch new password from secret client is costly operation. So, is there any way I can catch  "ORA-01017: invalid username/password; logon denied" and try password update instead of every time calling secret client to fetch same password.

Regards,
Srinu

[Mike Bayer]

you would most easily amend the "costly operation" part of it to cache the result for a given period of time, so that you can get this password when you need it from local memory.   the generated password should be delivered with an approximate expiration time so that you can store it locally within the scope of that timeframe.

otherwise, opt to fully replace the connect function, use oracle connect with try/except, then connect again with the new password, use the hook example at https://docs.sqlalchemy.org/en/14/core/engines.html#fully-replacing-the-dbapi-connect-function 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d0ce3a81-c53f-4884-9258-c4a2d742e39cn%40googlegroups.com.

[Srinu Chp]

Thank you Mike for the help.