Password rotation

[KCala]

Hey @albertodonato! I've just found out your project and it fits the need that we have in our project 100% Thank you for developing it, looks great!

The only problem that I see is that the Postgres database in our environment rotates password every few minutes. Therefore if we deploy the query-exporter it would have to update the connection password as well.

I've found this issue #41 which would suggest that dynamic config reloads are not possible. However, maybe connection settings are somewhat different and there is a way to do it?

I'd be happy to try and create a PR for such a feature, yet it would be nice to hear your thoughts and suggestions on this as a project's creator :)

[albertodonato]

Hey @KCala, thanks, happy that the project could be useful to you.

Currently (as per #41 you mentioned) reloading configs is not possible.
The reason it wouldn't be very useful is that the daemon would basically have to stop everything and restart because then queries/metrics could be different.

I'm curious about your requirement about rotating passwords, how do you handle that in other applications?

I think one option that might help you would be to use .pgpass (https://www.postgresql.org/docs/12/libpq-pgpass.html) to store the password, which you can then update as needed. This is used by libpq (and thus psycopg2) and should work fine with query-exporter. You might want to set keep-connected: false for the database so that the connection is not kept open between queries.

I haven't tried the above, but I'll try to do that when I have a moment. If you get to it, please let me know if that works for you.

[KCala]

Nice, if .pgpass would work that would solve the problem! I'll also give it a try when I have some time for that and will report the result back here. So I guess it will be that one of us the gets to it first ;)

I'm curious about your requirement about rotating passwords, how do you handle that in other applications?

That's a security requirement, and it's handled by our AWS RDBS cluster. Our applications (written in Scala) periodically obtain the new DB-token and replace it in our connection pool. I wouldn't know how do to it in a python application however, no experience here.