[DOVECOT] Re: [DOVECOT] Re: Changing Password Schemes
Carl Jeptha
cajeptha at gmail.com
Tue May 3 17:16:18 UTC 2016
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 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