Is it expected behavior that users created with scram-sha-256 passwords can still connect via md5 in pg_hba.conf?
Yes. From the docs:
To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.
You can think of "md5" inside pg_hba.conf as "md5 or better"
As a result, some users are able to connect, while others cannot.
Can you expand on this? Nothing you have done should be preventing logins, as far as I can tell.
Best solution: Upgrade everyone to scram, then change md5 to scram in pg_hba.conf and never look back.
That requires setting the password to null and then recreating the password, no? Otherwise IIRC, changing an md5 password leaves the new password also in md5 format.
