[RCD] Roundcube, password plugin, and mysql

Hello,

I'm not sure if this issue is a bug. I've asked on the users list and don't believe there's anyone on it, i've got no list traffic.

I use Roundcube 1.3.4 on a FreeBSD system.

I use postfix which gets it's authentication from dovecot imap server and goes to a MySQL database. I am now wanting to get roundcube's password plugin to be able to change a users password. In the password plugin configuration file I had this originally:

update virtual_users set password=CONCAT('{SHA512-CRYPT}', ENCRYPT (%p, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user=%u;

This didn't work nor did it return any debug information. I then went in to MySQL directly and did this:

update virtual_users set password=CONCAT('{SHA512-CRYPT}', ENCRYPT ('PasswordGoesHere', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user='Username goes here';

Directly at the MySQL prompt this worked replaced user and password with values and it got returned correctly. I then did this with %p and %u as it appeared that not having them quoted was causing an error. Through none of this am I getting any kind of debug or log output:

update virtual_users set password=CONCAT('{SHA512-CRYPT}', ENCRYPT ('%p', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user='%u';

I try to change the password and I get brought back to the same screen, no errors on the screen and nothing in a log, the password does not get changed.

Any suggestions or ways I can get some logging information?

Also, when doing the change at the MySQL prompt I got a warning, doing a show warnings revealed error 1287 that the ENCRYPT function is deprecated and to use AES_ENCRYPT instead. I tried replacing ENCRYPT with AES_ENCRYPT and that didn't work.

I've got debug and sql and imap debug all enabled. I've got the below sql schema for my virtual users table:

mysql> describe virtual_users; +------------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | domain_id | int(11) | NO | MUL | NULL | | | user | varchar(40) | NO | MUL | NULL | | | password | varchar(128) | NO | | NULL | | | uid | smallint(5) unsigned | NO | | 999 | | | gid | smallint(5) unsigned | NO | | 999 | | | quota | bigint(20) | NO | | 0 | | | active | tinyint(1) | NO | | 1 | | | allow_imap | tinyint(1) | NO | | 1 | | | last_login_ip | varchar(16) | YES | | NULL | | | last_login_date | datetime | YES | | NULL | | | last_login_proto | varchar(5) | YES | | NULL | | +------------------+----------------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec)

The passwords are done as sha512-crypt hashes. Here's my current password/config.inc.php file:

$cat config.inc.php <?php $config['password_driver'] = 'sql'; // Determine whether current password is required to change password. $config['password_confirm_current'] = true; // Require the new password to be a certain length. $config['password_minimum_length'] = 10; // Require the new password to contain a letter and punctuation character $config['password_require_nonalpha'] = true; // Enables logging of password changes into logs/password $config['password_log'] = true; // Comma-separated list of login exceptions for which password change // will be not available (no Password tab in Settings) $config['password_login_exceptions'] = null; // Array of hosts that support password changing. // Listed hosts will feature a Password option in Settings; others will not. // Example: array('mail.example.com', 'mail2.example.org'); // Default is NULL (all hosts supported). $config['password_hosts'] = null; // Enables saving the new password even if it matches the old password. Useful // for upgrading the stored passwords after the encryption scheme has changed. $config['password_force_save'] = true; // Enables forcing new users to change their password at their first login. $config['password_force_new_user'] = false; $config['password_algorithm'] = 'sha512-crypt'; // Password prefix (e.g. {CRYPT}, {SHA}) for passwords generated // using password_algorithm above. Default: empty. $config['password_algorithm_prefix'] = '{SHA512-CRYPT}'; // Path for dovecotpw/doveadm-pw (if not in the $PATH). // Used for password_algorithm = 'dovecot'. //$config['password_dovecotpw'] = '/usr/local/bin/doveadm pw'; // for dovecot-2.x // Dovecot password scheme. // Used for password_algorithm = 'dovecot'. //$config['password_dovecotpw_method'] = 'SHA512-CRYPT'; // Enables use of password with method prefix, e.g. {MD5}$1$LUiMYWqx$fEkg/ggr/L6Mb2X7be4i1/ // when using password_algorithm=dovecot //$config['password_dovecotpw_with_method'] = false; // Iteration count parameter for Blowfish-based hashing algo. // It must be between 4 and 31. Default: 12. // Be aware, the higher the value, the longer it takes to generate the password hashes. $config['password_blowfish_cost'] = 12; // Number of rounds for the sha256 and sha512 crypt hashing algorithms. // Must be at least 1000. If not set, then the number of rounds is left up // to the crypt() implementation. On glibc this defaults to 5000. // Be aware, the higher the value, the longer it takes to generate the password hashes. $config['password_crypt_rounds'] = 1256; // This option temporarily disables the password change functionality. // Use it when the users database server is in maintenance mode or sth like that. // You can set it to TRUE/FALSE or a text describing the reason // which will replace the default. $config['password_disabled'] = false; $config['password_db_dsn'] = 'mysql://database_username:database_password@localhost/database'; // The query can contain the following macros that will be expanded as follows: // %p is replaced with the plaintext new password // %P is replaced with the crypted/hashed new password // according to configured password_method // %o is replaced with the old (current) password // %O is replaced with the crypted/hashed old (current) password // according to configured password_method // %h is replaced with the imap host (from the session info) // %u is replaced with the username (from the session info) // %l is replaced with the local part of the username // (in case the username is an email address) // %d is replaced with the domain part of the username // (in case the username is an email address) // Deprecated macros: // %c is replaced with the crypt version of the new password, MD5 if available // otherwise DES. More hash function can be enabled using the password_crypt_hash // configuration parameter. // %D is replaced with the dovecotpw-crypted version of the new password // %n is replaced with the hashed version of the new password // %q is replaced with the hashed password before the change // Escaping of macros is handled by this module. // Default: "SELECT update_passwd(%c, %u)" //$config['password_query'] = 'SELECT update_passwd(%c, %u)'; $config['password_query'] = 'UPDATE virtual_users SET password=%c WHERE user=%u LIMIT 1'; //$config['password_query'] = 'UPDATE virtual_users SET password=CONCAT('{SHA512-CRYPT}', ENCRYPT (%p, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user=%u limit 1;'; //$config['password_query'] = "update virtual_users set password=CONCAT('{SHA512-CRYPT}', ENCRYPT (%p, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user=%u"; //$config['password_query'] = 'UPDATE users SET crypt=ENCRYPT(%p,CONCAT(_utf8'$5$',RIGHT(MD5(RAND()),8),_utf8'$')) WHERE id=%u LIMIT 1'; //UPDATE users SET password=%p WHERE username=%u AND password=%o AND domain=%h LIMIT 1 //UPDATE users SET password=ENCRYPT(%p,concat(_utf8'$1$',right(md5(rand()),8),_utf8'$')) WHERE username=%u LIMIT 1 $config['password_crypt_hash'] = 'sha512'; $config['password_idn_ascii'] = false; $config['password_hash_algorithm'] = 'sha1'; $config['password_hash_base64'] = false; $config['password_pw_usermod_cmd'] = 'sudo /usr/sbin/pw usermod -h 0 -n';

Suggestions welcome.

Thanks. Dave.