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-----
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-----
OK, I ran that code on a "backup" database using phpmyadmin and it ran
the code:
SQL query:
UPDATE mailbox set password = ENCRYPT(clearpwd,
CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=''
Matched rows: 0
------------
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:> UPDATE mailbox set password = ENCRYPT(clearpwd,
CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password='
';
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-----
Sorry was giving a rapid update, but there was an error, which I picked up immediately, ".sha" should have been ",sha". The query was successful, Thank you, and I did learn a lot. Now to transfer all the mailbox folders from the old server to the new server. ------------ 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-----
So to Close off this, may I summarize what I did.
Changed my password_query in dovecot-sql.conf.ext to:
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'
This allowed HASHED and un-HASHED passwords to be used concurrently.
I then ran the following in PhpMyAdmin, which populated all of my clear
passwords with a hash into HASHED password column:
UPDATE mailbox SET password = ENCRYPT(clearpwd,
CONCAT('$6$',sha(RAND()))) WHERE password IS NULL OR password=''
I again thank Geldalya for his patience and understanding.
------------
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-----