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----- >
Drop this from the end of your query:
AND cryptpwd = password ('%w')
and Steffen is right, it wouldn't hurt you to get a better understanding of
the principles at work here.
Nothing in this thread has had anything to do with dovecot so far.
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-----
>>
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-----
>>
Here is what worked:
password_query = \
SELECT username AS USER, \
IF(password IS NULL OR password='',
CONCAT('{PLAIN}',clearpwd),
PASSWORD) 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'
PLEASE NOTE THE FOLLOWING as per an earlier post: you MUST have field
called "password" when using password_query, but under WHERE you may
show what the "password" field is called, for example:
WHERE username = '%u' AND active = '1' AND cryptpwd = password
('%w')
ALSO NOTE my original post was about following a dovecot wiki, I was told by
Gedalya to do the above, now that I "understand" that
"password_query" I will move on with the rest of the solution he gave
me.
------------
You have a good day now, en mag jou m?re ook so wees,
Carl A Jeptha
On 2016-05-03 10:26, Gedalya wrote:> 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-----
>>>