Changing Password Schemes

[Carl Jeptha]

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-----