update: It appears that what I'm naively trying to do here, of manually appending the salt to the password, will not work because of the way the crypt algorithm works (see Unix crypt using SHA-256 and SHA-512), so this is now really not a Stack Overflow question, but a purely Server Fault question on MySQL 8 functions. Leaving this here, in case anyone else has this problem. You can see the question on Server Fault here.
There's a long backstory here, but the short story is, I can't seem to get MySQL 8 and Dovecot to generate the same SHA256 hashes, given the same salt and password. I suspect either a trivial error, or some string encoding problem (or possibly both).
NB I asked this same question on Server Fault, as it seemed more appropriate for there, but it's not getting any love, and I see several similar questions here, so ...
Dovecot:
$doveadm pw -s SHA256-CRYPT -p apassword
{SHA256-CRYPT}$5$h1JEsg1tmnTGS9Ub$Saoi1jr/uddYVD.n5p0hz70H9slnubpG7MQCkzpAiu4
Then, I grab that salt (h1JEsg1tmnTGS9Ub), and try and get the same output from MySQL 8:
SELECT CONCAT('$5$', 'h1JEsg1tmnTGS9Ub', '$', TO_BASE64(UNHEX(SHA2(CONCAT('apassword', 'h1JEsg1tmnTGS9Ub'), 256)))) WHERE true
$5$h1JEsg1tmnTGS9Ub$Vm9gPbWHuXt/zslurPQ7Nx0JLp1CphlBQbnL9R86XbM=
As I mentioned, there seem to be several similar quesitons here, but either they're with MySQL 5.x using the ENCRYPT function, which has been removed in 8.0.3, or people seem to have decided to drop the salt and just use SHA512 unsalted. It seems to me that this should be easy, and yet ...
Anyone have any ideas on how to get MySQL 8 to generate the same hash as doveadm, with same password and salt?
FWIW, I've tried the MySQL with and without TO_BASE64 and with and without UNHEX (I believe in all combinations).
Thanks!
