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