Changing Password Schemes

[Gedalya]

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