David Mehler
2018-03-10 16:52:34 UTC
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:***@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.
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:***@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.