Roundcube, password plugin, and mysql

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Roundcube, password plugin, and mysql

David Mehler
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.
_______________________________________________
Roundcube Development discussion mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/dev