Changing Password Schemes

Mon May 2 09:32:18 UTC 2016

driver = mysql
connect = host=127.0.0.1 dbname=********* user=***********
password=******************
default_pass_scheme = SHA512-CRYPT

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'

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

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

  Carl A Jeptha

On Sun, May 1, 2016 at 5:40 PM, Gedalya <gedalya at gedalya.net> wrote:

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