Changing Password Schemes

[Carl Jeptha]

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