[DOVECOT] Re: [DOVECOT] Re: Changing Password Schemes
Michael Toth
dovecot.mtoth at queldor.net
Tue May 3 17:13:51 UTC 2016
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 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