Configuring password plugin with Dovecot and MySQL using Dovecot settings

[jdpond]

Hadn't seen an update on this recently and spent a couple of hours trying to configure - hopefully this will be useful to someone else.  Updated the following which was installed at:
/var/www/roundcube on an Ubuntu server with current upgrades of all services and tools. YMMV


If you are using a different server, you can probably find a lot of the information you'll need to modify this at:
/etc/dovecot/dovecot-sql.conf.ext (or wherever you dovecot sql extension configuration parameters are)

specific config values you'll want to look for there are:

Code Select Expand


driver = [YourSqlService]
connect = host=[localhost] dbname=[dbname] user=[dbuser] password=[dbpassword]
default_pass_scheme = [encryption schema]

This may be a good time to upgrade your default schema on both Dovecot and roundcube since the default may be MD5, which isn't perhaps the strongest possible.  To see the available encryption schemas:

Code Select Expand

dovecotpw -l
and here's a description of Password Schemes

You should copy the distribution config to a usable one then edit it

Code Select Expand


cp /var/www/roundcube/plugins/password/config.inc.php.dist /var/www/roundcube/plugins/password/config.inc.php
vim /var/www/roundcube/plugins/password/config.inc.php


Here were the key connection configurations that needed to be modified (Example here is to use the Dovecot password encryption type SHA512-CRYPT)

Code Select Expand


$config['password_db_dsn'] = 'mysql://[dbuser]:[dbpassword]@localhost/[dbname]';
$config['password_dovecotpw'] = '/usr/bin/doveadm pw';
$config['password_algorithm'] = 'dovecot';
$config['password_dovecotpw_method'] = 'SHA512-CRYPT';
$config['password_dovecotpw_with_method'] = true;
$config['password_query'] = 'UPDATE [dbname].[dbtable] SET [pwdField]=%D WHERE [UserNameField]=%u LIMIT 1';

(bracketed values on right side of equation should be replaced by the values you found in the dovecot configs)

Of course, look at the rest of the config values and modify as desired, but the rest of the connection/encryption could be ignored.  Some you may want to modify could be:

Code Select Expand


$config['password_minimum_length'] = 8;
$config['password_require_nonalpha'] = true;
$config['password_force_save'] = true;
$config['password_force_new_user'] = true;


[robertcates]

Hi, I know this post is a bit outdated but it's the best guide I could find to get me started. Unfortunately I am having this (somewhat vague) error : "Could not save new password. Encryption function missing." I have Ubuntu Server 20.04.1, Postfix, Dovecot and Roundcube ver. 1.4.3 from the Ubuntu package (I believe that's the version number) and I've configured the password plugin according to this post, but I've also tried a few minor changes. the config.inc.php is currently:

// Password Plugin options
// -----------------------
// A driver to use for password change. Default: "sql".
// See README file for list of supported driver names.
$config['password_driver'] = 'sql';

// Determine whether current password is required to change password.
// Default: false.
$config['password_confirm_current'] = true;

$config['password_db_dsn'] = 'mysql://dbuser:password@localhost/db';
$config['password_dovecotpw'] = '/usr/bin/doveadm pw';
$config['password_algorithm'] = 'dovecot';
$config['password_dovecotpw_method'] = 'SHA512-CRYPT';
$config['password_dovecotpw_with_method'] = true;
$config['password_query'] = 'UPDATE mailbox SET password=%D WHERE username=%u LIMIT 1';

$config['password_minimum_length'] = 10;
$config['password_require_nonalpha'] = true;
$config['password_force_save'] = true;
$config['password_force_new_user'] = true;

I've tried %P inplace of %D because I read that %D is deprecated, but nothing seems to work. I'm wondering if it might have something to do with the MySQL 8.x install.??

Hep to resolve this issue would be greatly appreciated!

[JohnDoh]

The error is caused by a failure to generate the encrypted password. Is there anything in the Roundcube error log? May be the PHP user does not have rights to execute doveadm? Also have you tried setting `password_algorithm` to sha512-crypt and skipping out the call to doveadm?

[stewartmjohnson]

I'm having the exact same problem as Robert Cates, with the exact same setup. I previously had the password plugin configured to update passwords using a line of SQL from the guide:

UPDATE virtual_users SET password=ENCRYPT(%p,CONCAT(_utf8\'$6$\',RIGHT(MD5(RAND()),8),_utf8\'$\')) WHERE email=%u LIMIT 1

but in MySQL v8 that doesn't work any more because the ENCRYPT function has been deprecated.

I changed my settings to use doveadm pw but I get the same error as Robert Cates. I wonder if it's because of user permissions. I have to run doveadm as sudo, and if I run it without I get this error:

doveadm(stewart): Fatal: Error reading configuration: stat(/etc/dovecot/dovecot.conf) failed: Permission denied (euid=1000(stewart) egid=1000(stewart) missing +x perm: /etc/dovecot, we're not in group 121(dovecot), dir owned by 5000:121 mode=0750)

Does the roundcube process running through apache have the right permissions to run doveadm?

Is there an alternative line of SQL I can use for MySQL that doesn't use ENCRYPT()?

[stewartmjohnson]

Nope - I figured it out!

The password column in my virtual_users table is set to be 106 characters wide, which exactly fits "$6$" + salt + $ + password_hash. The password plugin was configured to prepend the hashing algorithm so added {SHA512-CRYPT} at the start, so the string was too long to fit in the database table.

I turned off the prepending of the hashing algorithm and it works just fine.