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=<u...@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 <cajep...@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 <geda...@gedalya.net> wrote:
>>>>>>
>>>>>>> On 05/02/2016 05:32 AM, Carl Jeptha wrote:
>>>>>>>> May 2 05:26:03 |****** dovecot: auth-worker(3442): Error:
>>>>>>>> sql(u...@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-----
