Describe the solution you'd like

Add support to dual password to make it easier to rotate passwords. Similar to MySQL 8 implementation: https://www.percona.com/blog/using-mysql-8-dual-passwords/.

Something like mysql impl:

ALTER USER user1 IDENTIFIED WITH plaintext_password BY 'new_password' RETAIN CURRENT PASSWORD;
ALTER USER user1 DISCARD OLD PASSWORD;

I am willing to spend some time implementing this, but would like to get feedback / ideas first.