Changing Password Schemes

[Carl Jeptha]

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
>