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