Re: [RCU] roundcube password plugin changing mysql password

Hello,

I am still stuck with this password plugin not giving me any useful output. 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.

On 3/7/18, David Mehler dave.mehler@gmail.com wrote:

Hello,

I'm a first time poster. 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.

Any suggestions on either issue welcome.

Thanks. Dave.