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@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@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:
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@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
{SHA512-CRYPT}$6$wEn1UFuiMzl9OSjd$Vh/PZ95WDID1GwI02QWAQNNfY5.Rk9zcSetYTgRfo4SPKf8qzMXsruvvS8uaSUidlvwDTLLSr3cVsQx2e6cu2/ 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> SETpasswd_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