Looks like an SQL update would do this: UPDATE users SET passwd_SHA512 = SHA2(passwd_clear, 512);

Bill

On 4/29/2016 9:07 AM, Carl A Jeptha wrote:

converting the passwords in the database from clear/plain text to SHA512-CRYPT

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-04-29 15:02, Steffen Kaiser wrote:

...

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

On Fri, 29 Apr 2016, Carl A Jeptha wrote:

...

Good Day, I have been following this tutorial without much luck - http://wiki2.dovecot.org/HowTo/ConvertPasswordSchemes#CA-26af2b83a43b8100522...

It is obvious to me that I am not following the instructions correctly and need to be shown what I am misunderstanding.

I have an old postfix + dovecot + Roundcube mailserver (mail.domain.Tld). Server clock is loosing time and adjusting it causes dovecot to kill itself, we cannot install more memory, bigger hard-drives, etc.

I have built a new server box (mail2.domain.Tld) With Postfix + dovecot + Roundcube. This server is functioning, with a client's new domain, and my personal domain. it's password system is SHA512-CRYPT.

I have imported the users from the old server over to the new server. I have created a new column "plain_pass" for the plain passwords.

I will be using imapsync to transfer the mail folders from the one server to the other, but will not proceed until I fix this issue.

Actually, _what_ issue?

...

# 2.2.18: /etc/dovecot/dovecot.conf # Pigeonhole version 0.4.8 (0c4ae064f307+) # OS: Linux 4.2.0-35-generic x86_64 Ubuntu 15.10 ext4 auth_mechanisms = plain login first_valid_gid = 8 first_valid_uid = 150 last_valid_gid = 8 last_valid_uid = 150 listen = * mail_gid = mail mail_location = maildir:/var/vmail/%d/%n mail_uid = vmail namespace inbox { inbox = yes location = mailbox Drafts { special_use = \Drafts } mailbox Junk { special_use = \Junk } mailbox Sent { special_use = \Sent } mailbox "Sent Messages" { special_use = \Sent } mailbox Trash { special_use = \Trash } prefix = } passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { quota_grace = 10%% quota_rule = *:storage=1G quota_rule2 = Trash:storage=+100M quota_warning = storage=95%% quota-warning 95 %u quota_warning2 = storage=80%% quota-warning 80 %u } postmaster_address = postmaster@airnet.ca protocols = imap pop3 lmtp lmtp pop3 service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } unix_listener auth-userdb { group = mail mode = 0666 user = vmail } } service imap-login { inet_listener imap { port = 143 } inet_listener imaps { port = 993 ssl = yes } } service imap-postlogin { executable = script-login /usr/local/etc/popafter.sh user = $default_internal_user } service imap { executable = imap imap-postlogin } service pop3-login { inet_listener pop3 { port = 110 } inet_listener pop3s { port = 995 ssl = yes } } service pop3-postlogin { executable = script-login /usr/local/etc/popafter.sh user = $default_internal_user } service pop3 { executable = pop3 pop3-postlogin } ssl_cert =

-- ------------ You have a good day now, en mag jou môre ook so wees, Carl A Jeptha

• -- Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1

iQEVAwUBVyNbaXz1H7kL/d9rAQIbnQgAyiX9368WmhyTfTY38YfNWTXW/UW/b/0t PqyEPx/DLLLW60aSkA0NqJa0nKzsoHip8GQfO/ZY3fqdTdep2rW4NbZW6o8/rFwW dmNTarux25w7dNvRGWrGZiXTnGAlwQtnJTr8wuwqi2JILqPoh1dL1Ubo90ABTERv G8q2NXgtD4m0a2aJqmyMXRmep8ocMy3IEIg8JZ/xJtmL86d4bP7kagI2yP7viIUx EY9JSazl/u6iVIrI6jFDuFUfzAs4dr+wcQHhAM0sY8mFUVYFsdjqxCbytLy39q4O zyj66UNAGR5yAnXAlADJ7G1fIghskFBa82p/t8QCX9VNSvOnuklqGQ== =Q4iK -----END PGP SIGNATURE-----

Hi Bill, using PHPMYAdmin: UPDATE 'mailbox' SET 'password' = SHA2 ('clearpwd',512); {UPDATE 'the users table' SET 'cryptic password column' = SHA2 ('clear text password column',512);} gives this error: #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 '[clearpwd512] FROM mailbox WHERE password <> SHA2 [ clearpwd OR 512' at line 1

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-04-29 15:20, Bill Shirley wrote:

UPDATE users SET passwd_SHA512 = SHA2(passwd_clear, 512);

The database is MySQL.

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-04-30 01:14, Gedalya wrote:

That's not SHA512-CRYPT. That's just a simple sha512 of the password, without salt.

A SHA512-CRYPT password will be generated with:

printf "1234\n1234" | doveadm pw -s SHA512-CRYPT

or:

doveadm pw -s SHA512-CRYPT -p 1234

or:

mkpasswd -m sha-512 1234

(without the "{SHA512-CRYPT}" prefix)

What exactly is the difficulty you are having with converting the passwords? What database engine are you using?

On 04/29/2016 03:20 PM, Bill Shirley wrote:

...

Looks like an SQL update would do this: UPDATE users SET passwd_SHA512 = SHA2(passwd_clear, 512);

Bill

On 4/29/2016 9:07 AM, Carl A Jeptha wrote:

...

converting the passwords in the database from clear/plain text to SHA512-CRYPT

This looks good, except it is truncated, it should be something like
95chars long, Is your hash column set to 128 or up around there or
larger?

Quoting Carl A Jeptha cajeptha@gmail.com:

Sorry for double reply, but this what a password looks like in the
"hashed" password column: {SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI2

---

You have a good day now, en mag jou môre ook so wees,

On 2016-04-30 01:14, Gedalya wrote:

...

That's not SHA512-CRYPT. That's just a simple sha512 of the
password, without salt.

A SHA512-CRYPT password will be generated with:

printf "1234\n1234" | doveadm pw -s SHA512-CRYPT

or:

doveadm pw -s SHA512-CRYPT -p 1234

or:

mkpasswd -m sha-512 1234

(without the "{SHA512-CRYPT}" prefix)

What exactly is the difficulty you are having with converting the passwords? What database engine are you using?

On 04/29/2016 03:20 PM, Bill Shirley wrote:

...

Looks like an SQL update would do this: UPDATE users SET passwd_SHA512 = SHA2(passwd_clear, 512);

Bill

On 4/29/2016 9:07 AM, Carl A Jeptha wrote:

...

converting the passwords in the database from clear/plain text to
SHA512-CRYPT

First of all, you can probably go online before you convert all passwords. You can modify your query in dovecot-sql.conf.ext to something like the following:

SELECT IF(crypt_pass IS NULL OR crypt_pass='', CONCAT('{PLAIN}',plain_pass), crypt_pass) as password FROM mailuser ..

This is assuming that:

• for incoming users, you have a plain_pass column containing just the plaintext password, without a {PLAIN} prefix, which we are adding in the query, letting dovecot process it correctly
• for these users, your other password column, "crypt_pass" in this example, is either NULL or an empty string.
• once crypt_pass is populated, it will contain a usable value, and this value will be returned by the query.

Now, as for converting your database, try this, after adjusting the queries to fit your schema:

#!/usr/bin/perl use strict; use warnings; use DBI; use MIME::Base64 'encode_base64';

my $dbtype = 'mysql'; my $dbhost = 'localhost'; my $dbname = 'maildb'; my $dbuser = 'dbuser'; my $dbpass = 'password';

my $dbh = DBI->connect("DBI:$dbtype:host=$dbhost;database=$dbname", $dbuser, $dbpass) or die "Could not connect to database: " . $DBI::errstr . "\n"; my $selectsth = $dbh->prepare('SELECT localpart, domain, plain_pass FROM mailuser where crypt_pass IS NULL OR crypt_pass=""'); my $updatesth = $dbh->prepare('UPDATE mailuser SET crypt_pass=? where localpart=? and domain=?'); $selectsth->execute; while (my $row = $selectsth->fetchrow_hashref) { open my $urand, '<', '/dev/urandom'; read $urand, my $salt, 12; close $urand; $salt = encode_base64($salt); $salt =~ s/\+/\./g; $salt =~ s/[^0-9a-z\.\/]//ig; #this shouldn't be needed my $cryptpw = '{SHA512-CRYPT}' . crypt $row->{plain_pass}, '$6$'.$salt; print "$row->{localpart}\@$row->{domain}: $cryptpw\n"; # uncomment this when you feel comfortable #$updatesth->execute($cryptpw, $row->{localpart}, $row->{domain}); }

You can run this safely with the last line commended out, and review the output. Perhaps try to test by manually updating one user with the displayed output. If everything seems sane, uncomment the line and run again.

On 04/30/2016 02:52 PM, Carl A Jeptha wrote:

Sorry not truncated: {SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI02QWAQNNfY5.Rk9zcSetYTgRfo4SPKf8qzMXsruvvS8uaSUidlvwDTLLSr3cVsQx2e6cu2/

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-04-30 14:58, Patrick Domack wrote:

...

This looks good, except it is truncated, it should be something like 95chars long, Is your hash column set to 128 or up around there or larger?

Quoting Carl A Jeptha cajeptha@gmail.com:

...

Sorry for double reply, but this what a password looks like in the "hashed" password column: {SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI2

---

You have a good day now, en mag jou môre ook so wees,

On 2016-04-30 01:14, Gedalya wrote:

...

That's not SHA512-CRYPT. That's just a simple sha512 of the password, without salt.

A SHA512-CRYPT password will be generated with:

printf "1234\n1234" | doveadm pw -s SHA512-CRYPT

or:

doveadm pw -s SHA512-CRYPT -p 1234

or:

mkpasswd -m sha-512 1234

(without the "{SHA512-CRYPT}" prefix)

What exactly is the difficulty you are having with converting the passwords? What database engine are you using?

On 04/29/2016 03:20 PM, Bill Shirley wrote:

...

Looks like an SQL update would do this: UPDATE users SET passwd_SHA512 = SHA2(passwd_clear, 512);

Bill

On 4/29/2016 9:07 AM, Carl A Jeptha wrote:

...

converting the passwords in the database from clear/plain text to SHA512-CRYPT

You do need to complete the query. Don't just replace your query with the one I wrote. You have to have a WHERE clause, and you might need to return other fields. Keep the password query you had before, just replace the 'password' column with "IF( ... ) as password" The query as you have it now simply returns all the passwords for all the users, because you don't have a WHERE clause.

On 05/01/2016 11:27 AM, Carl Jeptha wrote:

Hi, Was testing your solution and was receiving:

May 1 11:10:03 mail2 dovecot: message repeated 5 times: [ auth-worker(24202): Error: sql(user@domain.com,xxx.xxx.xxx.xxx): Password query returned multiple matches]

Here is my dovecot-sql.conf.ext file:

driver = mysql connect = host=127.0.0.1 dbname=vmail user=********* password=************* default_pass_scheme = SHA512-CRYPT password_query = SELECT IF(cryptpwd IS NULL OR cryptpwd='',CONCAT('{PLAIN}',clearpwd),cryptpwd)as password FROM mailbox user_query = SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail, 150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS quota FROM mailbox WHERE username = '%u' AND active = '1'

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On Sun, May 1, 2016 at 3:02 AM, Gedalya gedalya@gedalya.net wrote:

...

First of all, you can probably go online before you convert all passwords. You can modify your query in dovecot-sql.conf.ext to something like the following:

SELECT IF(crypt_pass IS NULL OR crypt_pass='', CONCAT('{PLAIN}',plain_pass), crypt_pass) as password FROM mailuser ..

This is assuming that:

• for incoming users, you have a plain_pass column containing just the plaintext password, without a {PLAIN} prefix, which we are adding in the query, letting dovecot process it correctly
• for these users, your other password column, "crypt_pass" in this example, is either NULL or an empty string.
• once crypt_pass is populated, it will contain a usable value, and this value will be returned by the query.

Now, as for converting your database, try this, after adjusting the queries to fit your schema:

#!/usr/bin/perl use strict; use warnings; use DBI; use MIME::Base64 'encode_base64';

my $dbtype = 'mysql'; my $dbhost = 'localhost'; my $dbname = 'maildb'; my $dbuser = 'dbuser'; my $dbpass = 'password';

my $dbh = DBI->connect("DBI:$dbtype:host=$dbhost;database=$dbname", $dbuser, $dbpass) or die "Could not connect to database: " . $DBI::errstr . "\n"; my $selectsth = $dbh->prepare('SELECT localpart, domain, plain_pass FROM mailuser where crypt_pass IS NULL OR crypt_pass=""'); my $updatesth = $dbh->prepare('UPDATE mailuser SET crypt_pass=? where localpart=? and domain=?'); $selectsth->execute; while (my $row = $selectsth->fetchrow_hashref) { open my $urand, '<', '/dev/urandom'; read $urand, my $salt, 12; close $urand; $salt = encode_base64($salt); $salt =~ s/\+/\./g; $salt =~ s/[^0-9a-z\.\/]//ig; #this shouldn't be needed my $cryptpw = '{SHA512-CRYPT}' . crypt $row->{plain_pass}, '$6$'.$salt; print "$row->{localpart}\@$row->{domain}: $cryptpw\n"; # uncomment this when you feel comfortable #$updatesth->execute($cryptpw, $row->{localpart}, $row->{domain}); }

You can run this safely with the last line commended out, and review the output. Perhaps try to test by manually updating one user with the displayed output. If everything seems sane, uncomment the line and run again.

On 04/30/2016 02:52 PM, Carl A Jeptha wrote:

...

Sorry not truncated:

...

---

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-04-30 14:58, Patrick Domack wrote:

...

This looks good, except it is truncated, it should be something like 95chars long, Is your hash column set to 128 or up around there or larger?

Quoting Carl A Jeptha cajeptha@gmail.com:

...

Sorry for double reply, but this what a password looks like in the "hashed" password column: {SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI2

---

You have a good day now, en mag jou môre ook so wees,

On 2016-04-30 01:14, Gedalya wrote:

...

That's not SHA512-CRYPT. That's just a simple sha512 of the password, without salt. A SHA512-CRYPT password will be generated with:

printf "1234\n1234" | doveadm pw -s SHA512-CRYPT

or:

doveadm pw -s SHA512-CRYPT -p 1234

or:

mkpasswd -m sha-512 1234

(without the "{SHA512-CRYPT}" prefix)

What exactly is the difficulty you are having with converting the

{SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI02QWAQNNfY5.Rk9zcSetYTgRfo4SPKf8qzMXsruvvS8uaSUidlvwDTLLSr3cVsQx2e6cu2/ passwords?

...

...

...

...

What database engine are you using?

On 04/29/2016 03:20 PM, Bill Shirley wrote: > Looks like an SQL update would do this: > UPDATE users > SET passwd_SHA512 = SHA2(passwd_clear, 512); > > Bill > > On 4/29/2016 9:07 AM, Carl A Jeptha wrote: >> converting the passwords in the database from clear/plain text to SHA512-CRYPT

On 2016-05-02 11:32, Carl Jeptha wrote:

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'

You have a right parenthesis after clearpwd in your sql statement CONCAT('{PLAIN}',clearpwd),cryptpwd)

user_query =
SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail,
150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS quota
FROM mailbox WHERE username = '%u' AND active = '1'

Above is what I have done, but still getting an error:

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'

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 The PHPMyAdmin error message shows the relevant part to inspect.

-- Christian Kivalo

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

-----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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session=

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

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

Drop this from the end of your query: AND cryptpwd = password ('%w')

and Steffen is right, it wouldn't hurt you to get a better understanding of the principles at work here. Nothing in this thread has had anything to do with dovecot so far.

On 05/03/2016 08:08 AM, Carl Jeptha wrote:

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@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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session=

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

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

Here is what worked: 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'

PLEASE NOTE THE FOLLOWING as per an earlier post: you MUST have field called "password" when using password_query, but under WHERE you may show what the "password" field is called, for example:

WHERE username = '%u' AND active = '1' AND cryptpwd = password ('%w')

ALSO NOTE my original post was about following a dovecot wiki, I was told by Gedalya to do the above, now that I "understand" that "password_query" I will move on with the rest of the solution he gave me.

You have a good day now, en mag jou môre ook so wees,

Carl A Jeptha

On 2016-05-03 10:26, Gedalya wrote:

Oh, you uppercased PASSWORD again.

Change:

IF(cryptpwd IS NULL OR cryptpwd=' ', CONCAT('{PLAIN}',clearpwd), cryptpwd) AS PASSWORD

To:

IF(cryptpwd IS NULL OR cryptpwd=' ', CONCAT('{PLAIN}',clearpwd), cryptpwd) AS password

and again, try to understand what's going on here.

On 05/03/2016 08:08 AM, Carl Jeptha wrote:

...

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@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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session=

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

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

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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session=

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

• -- 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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session= 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@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' <<< >

• -- 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 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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session= 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@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' <<< >>

• -- 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@domain.tld, method=PLAIN, rip=165.255.109.89, >> lip=10.0.0.12, TLS, session= > 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@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' <<< >>>> > - -- 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@domain.tld, method=PLAIN, rip=165.255.109.89, lip=10.0.0.12, TLS, session= 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@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' <<< >>

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