How to use Microsoft Entra ID for authentication with Azure Database for PostgreSQL in case of long running operations? I need to run pg_dump (I know there are other backup options available directly in Azure) on database. The database or even single table can be huge.
Is there a reliable way to ensure it succeeds? My concern is that access token lifetime is about ~1 hour and that pg_dump can take much longer.
As far as I understand, pg_dump lacks the ability to use a refresh token (to my understanding it's because it uses token as password in this case) within single command.
I know there are ways to mitigate the issue like splitting one pg_dump command into multiple, but that is not a 100% satisfactory solution.
During my testing I noticed that psql left me connected and do some queries after token used for connection got expired but not sure if this is something to rely on (feels more like no).
PostgreSQL only checks a user’s password at connection time. Once a client is authenticated, changing that user’s password on the server does not affect already-established sessions. So no need to worry about long running pg_dump. For details see following answers:
What happens to open sessions when changing password in PostgreSQL?
For how long postgres will keep alive connection after changing credentials?