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