Re: [DOVECOT] Re: [DOVECOT] Re: Changing Password Schemes

You are to fast, see my "late" email :-[ I picked it up immediately after sending the update.

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-05-03 19:13, Michael Toth wrote:

You have a typo in your SQL statement it should be ,sha not .sha

On 5/3/2016 1:07 PM, Carl Jeptha wrote:

...

Just tried to run it on the "Live" database, the simulation found all the rows, but when I ran the query I got this error (still trying to see what mus be changed): |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.sha(RAND()))) WHERE password IS NULL OR password=''' at line 1 |

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-05-03 18:33, Gedalya wrote:

...

Just make sure it says:

WHERE password IS NULL OR password='';

With no space between the quote marks, this way it matches an empty string

On 05/03/2016 12:29 PM, Carl Jeptha wrote:

...

Thank you, Due to changes I had to make to let password_query work, I think your "quick" version should be like this my setup:

UPDATE mailbox set password = ENCRYPT(clearpwd, CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=' ';

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-05-03 18:10, Gedalya wrote:

...

The script I sent you should do the job of populating your cryptpwd column with a SHA512-CRYPT version of the clearpwd column. The only reason why you would bother with a perl script is to get a better quality salt from /dev/urandom If you don't care so much about the quality of the salt, you can just run this single query. Make a backup of your database first!!

UPDATE mailbox set cryptpwd = ENCRYPT(clearpwd, CONCAT('$6$',sha(RAND()))) WHERE cryptpwd IS NULL OR cryptpwd=' ';

Here you are using MySQL's RAND() function to generate salt. It will do the minimum job of making the resulting encrypted password not equal to a SHA512 of the password itself, but the salt isn't very random. So the perl script I sent you reads 12 bytes of better quality random data from /dev/urandom and uses that. This means that if your database gets stolen it will be harder to decrypt the passwords.

On 05/03/2016 11:58 AM, Carl Jeptha wrote:

...

Steffen, If you can point me in the direction as to how to convert a column of clear text passwords to SHA512-CRYPT I will be happy to follow it and close this query, I only came here because I had spent almost two weeks trying to make the dovecot wiki work and thought someone would point out the mistakes I had made.

But otherwise, I will move on, and not waste anyone's time anymore.

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-05-03 07:02, Steffen Kaiser wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Tue, 3 May 2016, Carl Jeptha wrote: > >> OK QUERY is WORKING ("password_query" relies on having a >> field/column >> "password', hence the addition under WHERE): >> password_query =
>> SELECT username AS USER,
>> IF(cryptpwd IS NULL OR cryptpwd=' ', >> CONCAT('{PLAIN}',clearpwd), >> cryptpwd) AS PASSWORD,
>> '/var/vmail/%d/%n' as userdb_home,
>> 'maildir:/var/vmail/%d/%n' as userdb_mail, 150 as >> userdb_uid, 8 as >> userdb_gid
>> FROM mailbox
>> WHERE username = '%u' AND active = '1' AND cryptpwd = >> password ('%w') >> >> But still no happy dance, we now have a new error: >> >> dovecot: imap-login: Disconnected (auth failed, 3 attempts in 15 >> secs): user=user@domain.tld, method=PLAIN, rip=165.255.109.89, >> lip=10.0.0.12, TLS, session= > 1st) You should also enable auth debugging. > > 2nd) You are poking in the dark with SQL without understanding it, > > WHERE ... cryptpwd = password ('%w') > > ???? > > 3rd) I had the impression that you want to upgrade lower hashed > passwords into stronger hashed ones with a specific scheme and > that you therefore need to authentificate against two columns, but > update the strong hashes from the entered plain text password if > missing. > > If you already have access to the clear/text passwords, hash them, > put the hashes into the database and be fine. No need for > different columns and a > post login script. > > Otherwise: Nobody answered this particular question. And I see no > evidance, that Dovecot passes an environment variable named > PLAIN_PASSWORD along. I've read the Wiki, but I see nothing like > that in the code. Did you've verified that the post login script > gets the plain password? > > If you have hashed passwords, CONCAT('{PLAIN}',clearpwd) is > nonsense. > >> >> On Tue, May 3, 2016 at 11:10 AM, Carl Jeptha cajeptha@gmail.com >> wrote: >> >>> Here is what is in phpmyadmin: >>> password_query = >>> SELECT >>> username as user, >>> SELECT >>> IF( >>> cryptpwd IS NULL >>> OR cryptpwd = '', >>> CONCAT('{PLAIN}', clearpwd), >>> cryptpwd >>> ) as password, >>> '/var/vmail/%d/%n' as userdb_home, >>> 'maildir:/var/vmail/%d/%n' as userdb_mail, >>> 150 as userdb_uid, >>> 8 as userdb_gid >>> FROM >>> mailbox >>> WHERE >>> username = '%u' >>> AND active = '1' >>> >>> and the error now: >>> #1064 - You have an error in your SQL syntax; check the manual >>> that >>> corresponds to your MySQL server version for the right syntax to >>> use near >>> 'password_query = >>> SELECT >>> username as user, >>> SELECT >>> IF( >>> cryptpwd IS NULL >>> ' at line 1 >>> >>> On Mon, May 2, 2016 at 2:07 PM, Gedalya gedalya@gedalya.net >>> wrote: >>> >>>> On 05/02/2016 05:32 AM, Carl Jeptha wrote: >>>>> May 2 05:26:03 |****** dovecot: auth-worker(3442): Error: >>>>> sql(user@domain.tld,xxx.xxx.xxx.xxx): Password query must >>>>> return a >>>>> field named 'password' >>>> I'm not sure, maybe it's checking case-sensitive. Your query >>>> returns >>>> PASSWORD. Make it lowercase. >>>> >>>>> For testing purposes I put the query in PHPMyAdmin and it >>>>> complains this >>>>> (notice it drops "PASSWORD", but shows it in the query: >>>>> #1064 - You have an error in your SQL syntax; check the manual >>>>> that >>>>> corresponds to your MySQL server version for the right syntax >>>>> to use >>>> near '
>>>>> IF(cryptpwd IS NULL OR cryptpwd='', >>>>> CONCAT('{PLAIN}',clearpwd), >>>>> cryptpwd) as ' at line 1 >>>>> >>>>> >>>> It also sarts with a \ ... did you leave that in? That is >>>> specific to the >>>> dovecot config file. In PHPMyAdmin you should remove the >>>> line-continuation >>>> backslashes. >>>> >>>> Actually if you use the mysql command-line client, you would be >>>> able to >>>> paste that in with the backlashes. >>>> >>>> Make sure to put in a real value in WHERE username = '%u' <<< >>>> > - -- Steffen Kaiser > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1 > > iQEVAwUBVyiFMXz1H7kL/d9rAQKnRAgAuvDfoovuWo6Pe9K0xOL7P3EDzB2KNdMH > 8Wdno9O859LH9sBFIn3//WW2oQqgqOPCWfOnkUTG/w+l4yYHkFCeVmJgDoKlWGUd > +tNlpZjFvrqBKazKlTAaJ/WBiMkyDlT3qJzrIAGMaXZv+0ycUMTN3+ulrUceB4WW > +Uk5Cvt6LEq9wuqDABje4frIfQc9WVVxI69+z8bHnW6OIq2sL2DXFFRskPbdKFTG > LTUewcpZTzBKSYLtbFfseBXTCmLy2XPazziamDr9/GWE9yBUR8VhcaTlCp4aI9VG > 0vB4qCwHF5GNZ6740vYwkVWPFHNYaZW+xZ7v9GCY2mF71A2viCP+QA== > =sXel > -----END PGP SIGNATURE-----