Changing Password Schemes

Tue May 3 16:10:26 UTC 2016

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 at domain.tld>, method=PLAIN, rip=165.255.109.89,
>>> lip=10.0.0.12, TLS, session=<LywBS+0xdQCl/21Z>
>>
>> 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 at 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 at 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 at 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-----