I have a running Prodigy instance that connects to a Postgres DB. I am currently storing the credentials in prodigy.json and the connection works well. However, the credentials for this DB change every day at random times. My workaround at the moment is to stop the prodigy instance when I get a DB connection error, overwrite prodigy.json with the new credentials and re-run the instance. This is clearly not a great solution and I was wondering if there is any way to have a dynamic DB connection where I can update the DB credentials without the need of stopping the prodigy instance.
I'm afraid there is currently no mechanism in Prodigy to dynamically update database credentials while the server is running. The database connection in Prodigy is established during the initialization and the connection parameters are read only once from the configuration at startup.
You can set database credentials in environment variables like PRODIGY_CONFIG_OVERRIDES which can be easier to manage in some deployment scenarios, but this still requires a server restart.
One solution you could test is to set up a database proxy like PgBouncer that can handle credential rotation while maintaining the same connection string that Prodigy uses. Looking at their docs on RELOAD command PgBouncer can gradually transition to the new credentials. When a client (like Prodigy) finishes its transaction and returns the connection to the pool, PgBouncer will close that specific server connection and any new connection request will use the updated connection parameters.
!