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@domain.tld, method=PLAIN, rip=165.255.109.89,
lip=10.0.0.12, TLS, session=
On Tue, May 3, 2016 at 11:10 AM, Carl Jeptha cajeptha@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@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@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' <<<