Thanks a lot for stepping in! This indeed does improve approach and print out way more relevant information. I still have a question/concern about it though. As from what I get, it still selects the MAX(id) only from the subset which satisfies the condition? In other words, like `MAX(id) WHERE pws.password_hash like '$6$%'`? Because if the user changes the password, the old one will remain with the old hash and not gonna be marked as expired? So, like, should it be selected as a MAX(id) for distinct user_id and only then select ones having the password hash matching the criteria? Or am I getting it wrong after all and it does exactly that? вт, 26 авг. 2025 г. в 14:11, Konstantin Larin <klarin@sardinasystems.com>:
Hi!
Let me try on the query:
SELECT local_user.user_id, local_user.name, user.created_at, pws.expires_at FROM local_user INNER JOIN user ON local_user.user_id=user.id INNER JOIN ( SELECT local_user_id, MAX(id), expires_at, password_hash FROM password GROUP BY local_user_id ) AS pws ON local_user.id=pws.local_user_id WHERE user.enabled = 1 AND ( pws.expires_at is NULL OR pws.expires_at > NOW() ) AND ( pws.password_hash like '$6$%' OR pws.password_hash like '$5$%' )
This returns user ID, user name, user creation date and password expiry date for only enabled users with non-expired password, password is selected by its largest id.
On Tue, 2025-08-26 at 09:29 +0200, Dmitriy Rabotyagov wrote:
the only way for keyston to do this would be to wait for the user to auth and as a sideffect of thatt caluate a newer hash after they verify the users credentials
And that would be really a great way to do it from my prespective. Though, it would require at this point to: - re-implement sha512-crypt algorithm natively without passlib - backport to 2025.1 - maintain it for couple of cycles to allow users to naturally login and update the hash
the SELECT query shows one less entry
I realized there is an issue in this SQL query actually - I have not figured out the correct one so far though. And the issue is, that not all "non-expired" passwords are actually relevant ones for the user, so there has to be another JOIN. As current query may include also historical passwords for the user.
Is this approach reasonable to just switch the hashing algorithm and nothing else by simply setting the existing password again
Sure, if you know the password - setting the same password again for the user will work indeed.
вт, 26 авг. 2025 г. в 09:02, Tobias Urdin - Binero < tobias.urdin@binero.com>:
Thanks for the heads-up! :)
/Tobias
On 25 Aug 2025, at 18:12, Dmitriy Rabotyagov <noonedeadpunk@gmail.com> wrote:
Hey folks,
I wanted to raise awareness of operators regarding Keystone upgrade to Epoxy (2025.1), as found that existing release notes are slightly more vague about corner case definition.
During analysis, it appeared that a lot of users (and under "a lot" I mean 4 figures number) in my really old deployments (which were deployed during Newton), still in use passwords hashed with sha512_crypt which has been dropped in 2025.1 [1]. As this was referenced as a "corner case" in upgrade docs, it may not bring enough attention to this matter reading notes, while sha512_crypt removal is mentioned in "Other" section which I personally missed when analyzing upgrade requirements.
So I would highly recommend to ensure that all your users are using supported hashing algorithms before the upgrade to Epoxy, and inform them to reset their passwords if they don't.
You can list users who are using unsupported hashing algorithm using following MySQL query: SELECT distinct l_u.user_id FROM keystone.local_user AS l_u JOIN keystone.password AS hash ONl_u.id = hash.local_user_id WHERE (hash.password_hash LIKE '$6$%' OR hash.password_hash LIKE '$5$%') AND hash.expires_at i s null;
Hope this helps somebody to save some time in the future.
[1] https://opendev.org/openstack/keystone/commit/4a4137d5f77ced3f4f011846fb9c28...