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 >>
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
> >>
>
On 2016-05-02 11:32, Carl Jeptha wrote:> 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'You have a right parenthesis after clearpwd in your sql statement CONCAT('{PLAIN}',clearpwd),cryptpwd)> 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 1The PHPMyAdmin error message shows the relevant part to inspect. -- Christian Kivalo
On 05/02/2016 05:32 AM, Carl Jeptha wrote:> 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'I'm not sure, maybe it's checking case-sensitive. Your query returns PASSWORD. Make it lowercase.> > 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 > >It also sarts with a \ ... did you leave that in? That is specific to the dovecot config file. In PHPMyAdmin you should remove the line-continuation backslashes. Actually if you use the mysql command-line client, you would be able to paste that in with the backlashes. Make sure to put in a real value in WHERE username = '%u' <<<