Allow creating database connection that support short lived temporary passwords

[ianwallen]

Feature description

We are using QGIS to connect to a postgresql database within MS Azure cloud using AAD authorization which is described here.

https://docs.microsoft.com/en-us/azure/postgresql/howto-configure-sign-in-aad-authentication

We are able to connect to the database using this method however after 1 hour we loose the database connection.

When using the AAD method described above, it provide a temporary password that is only good for up to 1 hour. After the hour, it seems like QGIS tries to make new database connection via the connection pool and fails. This causing the user to have to re-login to the database. This can be very frustrating for a user that is working with QGIS for a full day having to re-login several times during the day.

I believe a possible solutions could be to have an option in qgis to disable opening or closing connections in the connection pool once they have been created. So the idea is that all connection would be opened when the connection is initially made and it would not close any connections after that point (Unless the connection is dropped or lost)? Another option could be to have an option to disable connection pooling but this may cause some performance impacts?

Additional context

No response

[kannes]

Are you sure that the connection is not terminated by the remote database/host after that hour? Maybe you could check for that with a small non-QGIS script that keeps a connection open for so long and does a query every so often.

If it is actually a termination from the remote because of the token expiry, then QGIS can't do much.

A workaround could be to have a background task that refreshes the token and updates the connections if necessary.

[ianwallen]

We used tools like pgadmin4 and psql and we can keep the session open for as long as we want without the session being killed. So we know that the session is not being killed from postgresql.

We can use the token/password to login several times until the token/password has expired (which is 1 hour). At that point any existing sessions will remain open but we cannot create new sessions using the token/password and we need to request a new token/password if we wish to create any new sessions.

[ianwallen]

Basically, think of it like being on a system where passwords are constantly changing every hour.

If you want to test this issue I believe it can be easily reproduced if you do the following.

• Used qgis to process a large amount of data (enough data to have time to complete the next step).
• In a separate task, while qgis is processing the data, change the password on the user account that is currently processing data.

I suspect that connection pool will start failing because it can no longer create new connections.

If you do the same test using psql session and a separate task to reset the password. your current psql session will not die/end once the password has been change. But if you need to reconnect, you will need to enter the new password.