Changing Password Schemes

[Gedalya]

Oh, you uppercased PASSWORD again.

Change:

IF(cryptpwd IS NULL OR cryptpwd=' ', CONCAT('{PLAIN}',clearpwd), cryptpwd) AS PASSWORD

To:

IF(cryptpwd IS NULL OR cryptpwd=' ', CONCAT('{PLAIN}',clearpwd), cryptpwd) AS password

and again, try to understand what's going on here.


On 05/03/2016 08:08 AM, Carl Jeptha wrote:
> 1. Auth debug turned on, - nothing
> 2. cryptpwd is the name of my "password" column, have to specify that if
> you want to run password_query as it relies on a field "password" to work.
> 3. I have access to the "clear passwords" but none of my google searches
> worked for converting them to SHA512_CRYPT
>
> On Tue, May 3, 2016 at 1:02 PM, Steffen Kaiser <
> skdovecot at smail.inf.fh-brs.de> 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-----
>>