Changing Password Schemes
Carl Jeptha
cajeptha at gmail.com
Tue May 3 17:07:52 UTC 2016
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 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-----
More information about the dovecot
mailing list