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))), 'user@example.com'),

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.

score 16 · Accepted Answer · edited Sep 08 '21 at 13:58

16

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))), 'user@example.com');

edited Sep 08 '21 at 13:58

But those new buttons though..

21,377
10
81
108

answered Aug 09 '18 at 08:20

mahyard

1,230
1
13
34

2

Thank you for sharing this problem and the answer: this helped me.. I followed the same tutorial @ https://www.digitalocean.com/community/tutorials/how-to-configure-a-mail-server-using-postfix-dovecot-mysql-and-spamassassin#step-2-create-a-mysql-database,-virtual-domains,-users-and-aliases and I had the same deprecation message as well :) – Henry van Megen Oct 02 '18 at 09:13
Thank you! i was looking for this solution! – Wojtek B Nov 13 '19 at 20:20
YW @wojtek-b . You can voteup the question and the answer if you found them useful. – mahyard Nov 14 '19 at 07:22

But those new buttons though.. · Answer 2 · 2021-09-08T20:35:32.010

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), 'user@example.com');

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 = 'user@example.com';

These queries:

Generate a random (rather lengthy) salt
Append the salt to the password
Get the SHA512 Hash of #2
Convert the hash to binary
Append the salt to #4
Convert the whole thing to Base64
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 user@example.com YourPasswordHere

dovecot password hashing with mysql 8 SHA2

2 Answers2