dovecot password hashing with mysql 8 SHA2

Question

Previously (MySQL 5.7) we was using this command to add a new email address into an existing table:

INSERT INTO `servermail`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), '[email protected]'),

then dovecot was able to authenticate users. (more information about dovecot password scheme)
Now Encrypt has been deprecated in the recent versions of MySQL. (link)

I want to rewrite that command using SHA2 but I wasn't succeed.

Edit:
This could help someone to use How To Configure a Mail Server Using Postfix, Dovecot, MySQL, and SpamAssassin to configure a Mail Server with version 8.0 of Mysql.

Answer 1

SHA512-CRYPT seems to be impossible to implement within MySQL and as far Dovecot goes, I'll admit I can't understand why they suggest using encryption to begin with. Using a strong hash as the OP suggested works at least as well and is more secure.

Since I prefer my passwords with extra salt, here's how I'm using Dovecot's SSHA512 which is the "Salted SHA512 sum of the password stored in base64":

INSERT INTO `virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
(NULL, '1',  (SELECT REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT('YourPasswordHere', v.salt), 512)), v.salt)), '\n', '') AS salted FROM (SELECT SHA2(RAND(), 512) AS salt) v), '[email protected]');

Or to update a password:

UPDATE virtual_users
SET `password` = (
    SELECT REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT('YourPasswordHere', v.salt), 512)), v.salt)), '\n', '') AS salted
    FROM (
        SELECT SHA2(RAND(), 512) AS salt
    ) v
)
WHERE email = '[email protected]';

These queries:

1. Generate a random (rather lengthy) salt
2. Append the salt to the password
3. Get the SHA512 Hash of #2
4. Convert the hash to binary
5. Append the salt to #4
6. Convert the whole thing to Base64
7. Remove unwanted newlines added by MySQL's TO_BASE64() function

The result is a 256 byte long string so you may need to update your password field to VARCHAR(256).

There are a number of tutorials that suggest using doveadm to manually generate the encrypted password and while this works well, I find it a bit more cumbersome. For those interested you can call it like so:

doveadm pw -s SHA512-CRYPT -p "YourPasswordHere"

Even more useful is the ability to validate your generated passwords with the same utility:

doveadm auth test [email protected] YourPasswordHere

Answer 2

Finally I changed the default method dovecote uses for its user authentication from SHA512-CRYPT to SHA512. I think it's not less secure than that but is supported by MySQL 8.
After that I used this command to add a new user to the table.

INSERT INTO `servermail`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', TO_BASE64(UNHEX(SHA2('password', 512))), '[email protected]');