Changing Password Schemes

[Gedalya]

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 at 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 at 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:
>>>
>> {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 at 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
>>