sha512-crypt mysql and dovecot

Question

I have a question about understanding sha512-crypt hashing. I found this tutorial to set up dovecot and postfix with mysql. I followed the tutorial (with slight modifications) and everything works fine. But there is one thing, that I do not understand:

To add a user, I should use:

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

and again, this works perfectly fine, i.e. I can log in with my password (and only my password) to dovecot. But why? If I see it right, it encrypts the password with a random salt, but it doesn't save it anywhere. So hashing the same password twice gives me 2 different hashes (I tried it). So my question boils down to: Could I get a brief explanation of sha-512 (which I couldn't find online) and and explanation as to why these lines work?

Thanks already

Overbuild

Have you found the query that matches the password / user against the database by any chance? – Escalera

yes, it's: password_query = SELECT password FROM user WHERE username = '%u' – Overbuild

Answer 1

The salt is saved as part of the password. For example calling:

ENCRYPT('firstpassword', CONCAT('$6$', 'FooBarBaz')) 

Gives

$6$FooBarBaz$.T.G.7FRJqZ6N2FF7b3BEkr5j37CWhwgvPOOoccrr0bvkBbNMmLCxzqQqKJbNhnhC.583dTBLEuZcDuQe7NEe.

This stores both the algorithm used (6 being SHA512) and the salt ('FooBarBaz') both delinated by $.

Edit: To check a password you can use:

password = ENCRYPT('user_input', `password`)

ENCRYPT will grab the salt from the stored password and use this when checking user_input.

Full credit to hek2mgl for the password check he detailed in this answer.

Bout

Thanks a lot, that's it. Does mysql have an easy way to match a password against a string that's saved like that in the database? I.e. a function which I can give $6$FooBarBaz$.T.G.7FRJqZ6N2FF7b3BEkr5j37CWhwgvPOOoccrr0bvkBbNMmLCxzqQqKJbNhnhC.583dTBLEuZcDuQe7NEe and a password to match it against as arguments returning a true or false somehow? – Overbuild

@ciem ENCRYPT can be used for that to. I'll edit in an example. – Bout

Answer 2

You can use dovecot directly to generate the hash.

Use:

sudo doveadm pw -s SHA512-CRYPT

This will ask you for a password for the user you are trying to make and return to you a string that you can copy and paste into your MYSQL database.

INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  (1, 1, '{SHA512-CRYPT}$6$5kJy...', '[email protected]'),
  (...)
  ;

Carnes