Just tried to run it on the "Live" database, the simulation found all the rows, but when I ran the query I got this error (still trying to see what mus be changed): |#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 '.sha(RAND()))) WHERE password IS NULL OR password=''' at line 1 | ------------ You have a good day now, en mag jou m?re ook so wees, Carl A Jeptha On 2016-05-03 18:33, Gedalya wrote:> Just make sure it says: > > WHERE password IS NULL OR password=''; > > With no space between the quote marks, this way it matches an empty string > > > On 05/03/2016 12:29 PM, Carl Jeptha wrote: >> Thank you, >> Due to changes I had to make to let password_query work, I think your "quick" version should be like this my setup: >> >> UPDATE mailbox set password = ENCRYPT(clearpwd, CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=' '; >> >> ------------ >> You have a good day now, en mag jou m?re ook so wees, >> >> Carl A Jeptha >> >> On 2016-05-03 18:10, Gedalya wrote: >>> The script I sent you should do the job of populating your cryptpwd column with a SHA512-CRYPT version of the clearpwd column. >>> The only reason why you would bother with a perl script is to get a better quality salt from /dev/urandom >>> If you don't care so much about the quality of the salt, you can just run this single query. >>> Make a backup of your database first!! >>> >>> UPDATE mailbox set cryptpwd = ENCRYPT(clearpwd, CONCAT('$6$',sha(RAND()))) WHERE cryptpwd IS NULL OR cryptpwd=' '; >>> >>> Here you are using MySQL's RAND() function to generate salt. It will do the minimum job of making the resulting encrypted password not equal to a SHA512 of the password itself, but the salt isn't very random. So the perl script I sent you reads 12 bytes of better quality random data from /dev/urandom and uses that. This means that if your database gets stolen it will be harder to decrypt the passwords. >>> >>> >>> On 05/03/2016 11:58 AM, Carl Jeptha wrote: >>>> Steffen, >>>> If you can point me in the direction as to how to convert a column of clear text passwords to SHA512-CRYPT I will be happy to follow it and close this query, I only came here because I had spent almost two weeks trying to make the dovecot wiki work and thought someone would point out the mistakes I had made. >>>> >>>> But otherwise, I will move on, and not waste anyone's time anymore. >>>> >>>> ------------ >>>> You have a good day now, en mag jou m?re ook so wees, >>>> >>>> >>>> Carl A Jeptha >>>> >>>> On 2016-05-03 07:02, Steffen Kaiser 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-----
You have a typo in your SQL statement it should be ,sha not .sha On 5/3/2016 1:07 PM, Carl Jeptha wrote:> Just tried to run it on the "Live" database, the simulation found all > the rows, but when I ran the query I got this error (still trying to see > what mus be changed): > |#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 '.sha(RAND()))) WHERE password IS NULL OR password=''' at line 1 | > > ------------ > You have a good day now, en mag jou m?re ook so wees, > > Carl A Jeptha > > On 2016-05-03 18:33, Gedalya wrote: >> Just make sure it says: >> >> WHERE password IS NULL OR password=''; >> >> With no space between the quote marks, this way it matches an empty >> string >> >> >> On 05/03/2016 12:29 PM, Carl Jeptha wrote: >>> Thank you, >>> Due to changes I had to make to let password_query work, I think your >>> "quick" version should be like this my setup: >>> >>> UPDATE mailbox set password = ENCRYPT(clearpwd, >>> CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=' '; >>> >>> ------------ >>> You have a good day now, en mag jou m?re ook so wees, >>> >>> Carl A Jeptha >>> >>> On 2016-05-03 18:10, Gedalya wrote: >>>> The script I sent you should do the job of populating your cryptpwd >>>> column with a SHA512-CRYPT version of the clearpwd column. >>>> The only reason why you would bother with a perl script is to get a >>>> better quality salt from /dev/urandom >>>> If you don't care so much about the quality of the salt, you can >>>> just run this single query. >>>> Make a backup of your database first!! >>>> >>>> UPDATE mailbox set cryptpwd = ENCRYPT(clearpwd, >>>> CONCAT('$6$',sha(RAND()))) WHERE cryptpwd IS NULL OR cryptpwd=' '; >>>> >>>> Here you are using MySQL's RAND() function to generate salt. It will >>>> do the minimum job of making the resulting encrypted password not >>>> equal to a SHA512 of the password itself, but the salt isn't very >>>> random. So the perl script I sent you reads 12 bytes of better >>>> quality random data from /dev/urandom and uses that. This means that >>>> if your database gets stolen it will be harder to decrypt the >>>> passwords. >>>> >>>> >>>> On 05/03/2016 11:58 AM, Carl Jeptha wrote: >>>>> Steffen, >>>>> If you can point me in the direction as to how to convert a column >>>>> of clear text passwords to SHA512-CRYPT I will be happy to follow >>>>> it and close this query, I only came here because I had spent >>>>> almost two weeks trying to make the dovecot wiki work and thought >>>>> someone would point out the mistakes I had made. >>>>> >>>>> But otherwise, I will move on, and not waste anyone's time anymore. >>>>> >>>>> ------------ >>>>> You have a good day now, en mag jou m?re ook so wees, >>>>> >>>>> >>>>> Carl A Jeptha >>>>> >>>>> On 2016-05-03 07:02, Steffen Kaiser 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-----
You are to fast, see my "late" email :-[ I picked it up immediately after sending the update. ------------ You have a good day now, en mag jou m?re ook so wees, Carl A Jeptha On 2016-05-03 19:13, Michael Toth wrote:> You have a typo in your SQL statement it should be ,sha not .sha > > On 5/3/2016 1:07 PM, Carl Jeptha wrote: >> Just tried to run it on the "Live" database, the simulation found all >> the rows, but when I ran the query I got this error (still trying to see >> what mus be changed): >> |#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 '.sha(RAND()))) WHERE password IS NULL OR password=''' at line 1 | >> >> ------------ >> You have a good day now, en mag jou m?re ook so wees, >> >> Carl A Jeptha >> >> On 2016-05-03 18:33, Gedalya wrote: >>> Just make sure it says: >>> >>> WHERE password IS NULL OR password=''; >>> >>> With no space between the quote marks, this way it matches an empty >>> string >>> >>> >>> On 05/03/2016 12:29 PM, Carl Jeptha wrote: >>>> Thank you, >>>> Due to changes I had to make to let password_query work, I think your >>>> "quick" version should be like this my setup: >>>> >>>> UPDATE mailbox set password = ENCRYPT(clearpwd, >>>> CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=' '; >>>> >>>> ------------ >>>> You have a good day now, en mag jou m?re ook so wees, >>>> >>>> Carl A Jeptha >>>> >>>> On 2016-05-03 18:10, Gedalya wrote: >>>>> The script I sent you should do the job of populating your cryptpwd >>>>> column with a SHA512-CRYPT version of the clearpwd column. >>>>> The only reason why you would bother with a perl script is to get a >>>>> better quality salt from /dev/urandom >>>>> If you don't care so much about the quality of the salt, you can >>>>> just run this single query. >>>>> Make a backup of your database first!! >>>>> >>>>> UPDATE mailbox set cryptpwd = ENCRYPT(clearpwd, >>>>> CONCAT('$6$',sha(RAND()))) WHERE cryptpwd IS NULL OR cryptpwd=' '; >>>>> >>>>> Here you are using MySQL's RAND() function to generate salt. It will >>>>> do the minimum job of making the resulting encrypted password not >>>>> equal to a SHA512 of the password itself, but the salt isn't very >>>>> random. So the perl script I sent you reads 12 bytes of better >>>>> quality random data from /dev/urandom and uses that. This means that >>>>> if your database gets stolen it will be harder to decrypt the >>>>> passwords. >>>>> >>>>> >>>>> On 05/03/2016 11:58 AM, Carl Jeptha wrote: >>>>>> Steffen, >>>>>> If you can point me in the direction as to how to convert a column >>>>>> of clear text passwords to SHA512-CRYPT I will be happy to follow >>>>>> it and close this query, I only came here because I had spent >>>>>> almost two weeks trying to make the dovecot wiki work and thought >>>>>> someone would point out the mistakes I had made. >>>>>> >>>>>> But otherwise, I will move on, and not waste anyone's time anymore. >>>>>> >>>>>> ------------ >>>>>> You have a good day now, en mag jou m?re ook so wees, >>>>>> >>>>>> >>>>>> Carl A Jeptha >>>>>> >>>>>> On 2016-05-03 07:02, Steffen Kaiser 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----- >