Encrypt function not working following linode process for mailserver setup

ENCRYPT() was deprecated in a recent version of MySQL. Try using SHA2() instead.

I'll look into getting the guide updated, thanks for pointing this out!

That worked!!! Thanks

Also since you are going to update the procedure….

GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuserpass';

Should be

CREATE USER 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuserpass';

GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' ;

The line on the procedure does not work.

Thanks! I'll add that in to be changed as well.

Finally got around to this again and it seems that ENCRYPT and SHA2 can not be interchanged. I am unable to login with the password hashes that are being created with my original question.

ENCRYPT('password'…

@fifo --

Your problem is at step #9 where you edit /etc/dovecot/dovecot-sql.conf.ext:

dovecot-sql.conf.ext
…
driver = mysql
…
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=mailuserpass
…
default_pass_scheme = SHA512-CRYPT
…
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';
…

What you produce with MySQL and what is set in dovecot(1)s default_pass_scheme need to match. The correct function in MySQL to produce SHA512-CRYPT passwords is

SHA2(str, hash_length)

You're probably not setting the hash_length (or setting it incorrectly)…which produces either a NULL or a SHA256-CRYPT. Try this:

SHA2('password', 512)

See:

https://doc.dovecot.org/configuration_manual/authentication/password_schemes/

and

https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html

Just as an aside, if you are going to have a relatively small number of mailboxes that remain relatively static, SQLite is a much better solution for this…just as secure and much easier to administer (and no database server overhead).

-- sw

Ok I am still a little confused here.

I do have my /etc/dovecot/dovecot-sql.conf.ext set up just as you noted.

dovecot-sql.conf.ext
…
driver = mysql
…
default_pass_scheme = SHA512-CRYPT
…

Now where the difference lies is in the MySQL portion. My original SQL statement is this that I got from the manual with the exception of the ENCRYPT being replaced by SHA2

SHA2('password', CONCATENATE(' $6$ ', SUB STRING( SHA( RAND() ), -16))), 'example@example.com')

While the sql query works, it doesn't let me log in. Using what you provided above, I tried the following but am now getting a different error. "ERROR 1406 (22001): Data too long for column 'password' at row 1
"

SHA2( 'password', 512 ), 'example@example.com')

Finally, am I correct in thinking that the purpose of having

CONCATENATE( ' $6$ ', SUB STRING ( SHA ( RAND() ), -16 ) ) )

is to create a random hash? I'm not sure I understand why this step is done in the first place. The procedure doesn't really go into why we are doing the commands :(

*Sorry about the incorrect syntax, the forum won't take the correct syntax as it treats it as a hacking attempt and blocks it.

I tried to answer your question but I kept getting "You're blocked" messages…so it looks like you're on your own. From what you've written above, here are some hints:

• The length error probably means you’re violating the datatype/length constraint for the column in the table definition (SHA512-CRYPT hashes are > 100 chars long). Also, SHA2() only has two arguments…not 3. You shouldn't expect that SHA2() is a drop-in replacement for ENCR(). Read the MySQL link reference I posted above.

• As for the CONC() fragment, it looks like it's trying to build a fake SHA512 hash of the last 16 characters of a SHA256 hash of a random number…probably a work-around for an earlier version of MySQL that didn't have SHA2().

I hope this helps…

-- sw

Yes that was annoying me as well but thanks for the help! I figured it out earlier today reading some dovecot manuals and your tips above.

In order to get the password encrypted I performed this.

doveadm pw -s SHA512-CRYPT

It asked for the password twice and came back with a hash like this

$6$BZSC5XSsiXKnakAw$cx03ACxRtDKkiPq3c0kI7.P7eSnP5N6IKROo35k7QTU9o1lX5ofLpbdulC1.XCDeRo20r1l/SdTyQz5tPh59T/

I later grabbed that hash and put it in the query where it asks for the password and I was able to get in. Again thanks for all the help!

@mjones you should probably update that in the procedure as well. Thanks

I'm glad you discovered doveadm(1). I had pointed it out in my original post (and even posted a sample SHA512-CRYPT hash as you did) but I figured that might be causing the "You're blocked" messages.

Since my postfix/dovecot uses SQLite and not MySQL, I wrote a bunch of utilities in Ruby to do all the management of the database. I'm still working on it…since I'm a retired programmer (30+ years experience), it's a labor of love and will probably be sent to the bit bucket when I die.

You can supply the password to doveadm(1) on the command line:

doveadm pw -s SHA512-CRYPT -p "thepassword" -r number_of_rounds

You should be aware that some of the 'schemes' supported by the -s flag are not advisable for use in a production system requiring secure passwords. These include SHA1 and MD5. You can get a list of all the schemes doveadm(1) can generate by doing

doveadm pw -l

For what it's worth, setting up dovecot(1) is the toughest part of all this. I find the configuration cryptic and not very well-documented. You find yourself relying on the comments in the configuration files more than the published documentation (although there are some outdated/wrong things there as well). Once you get a configuration that works, you tend to be loath about messing with it.

I'm glad you figured it out. You really ought to check out SQLite. You can find out more about it here:

http://sqlite.org

It's more portable than the C code it's written in and the databases it creates/uses are platform neutral. If you use a smartphone with either Android or iOS, you use SQLite every day. I discovered it when I was tasked with building a configuration system for IPSec…I was looking for some kind of structured storage that was portable between Unix & Windoze. Needless to say, it saved my butt.

Since my needs are mostly modest, SQLite is my go to database because it's simple and server-less. I believe there's an API for it for just about every language I've ever encountered (PHP, Java, Ruby, Perl, Python, etc). SQLite is choice-quality stuff…

-- sw

Great to know, I will definitely take a look at that sqlite and again thanks for the help. This was driving me crazy.

SQLite is not MySQL (it doesn't have encryption functions at all for example -- but there are ways to extend it fairly easily…those are well-documented). If you need something more sophisticated than what SQLite can do, it's probably not suitable.

In my world of few mail accounts where the configuration never changes (unless I screw up), SQLite does the trick. Every embedded platform on the planet (smartphones, routers, etc) uses it. It's free, bug-free, well-documented and incredibly low-overhead.

If you need to scale up to > 1500 email accounts, you'd best look elsewhere. However, for small or experimental applications, it can't be beat!

The inventor, Dr. D. Richard Hipp, deserves some kind of international technology prize.

-- sw