Author Topic: Roundcube, password Plugin, and Mysql  (Read 2808 times)

Offline dmehler

  • Newbie
  • *
  • Posts: 4
Roundcube, password Plugin, and Mysql
« on: March 11, 2018, 01:39:39 PM »
Hello,

 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?

   My current password change query is this which does give me an error an error occurred can not save password, nothing else:

$config['password_query'] = "UPDATE virtual_users SET password=CONCAT(_utf8'{SHA512-CRYPT}',ENCRYPT(_utf8'%p',CONCAT(_utf8'$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user='%u' LIMIT 1";

  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.

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,140
Re: Roundcube, password Plugin, and Mysql
« Reply #1 on: March 12, 2018, 03:54:53 AM »
You do not need to add quotes around the macros (%u, %p etc) this is done automatically. you said you have sql_debug etc enabled but nothing is written in the logs. please confirm that there are entries in the logs (the logging is working) just nothing relating to the password change? For example when you get the message "new password could not be saved" you should see the query which roundcube attempted to execute in the sql debug log. There should also be something in the error log.
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…

Offline dmehler

  • Newbie
  • *
  • Posts: 4
Re: Roundcube, password Plugin, and Mysql
« Reply #2 on: March 12, 2018, 05:41:17 PM »
Hello,

Thank you for your reply. Here's what my current query looks like:


$config['password_query'] = "UPDATE postfix.virtual_users SET password=CONCAT(_utf8'{SHA512-CRYPT}',ENCRYPT(_utf8%p,CONCAT(_utf8'$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user=%u LIMIT 1";

and I do not see anything in my errors or error.log file but I do see output in my logs/sql file, here it is and it makes no sense to me:

[12-Mar-2018 17:19:53 -0400]: <2t6im6u2o> [1] SELECT `vars`, `ip`, `changed`, now() AS ts FROM `session` WHERE `sess_id` = '2t6im6u2ogsogp85g2m4as9ss1';
[12-Mar-2018 17:20:03 -0400]: <2t6im6u2o> [1] SELECT `vars`, `ip`, `changed`, now() AS ts FROM `session` WHERE `sess_id` = '2t6im6u2ogsogp85g2m4as9ss1';
[12-Mar-2018 17:21:04 -0400]: <2t6im6u2o> [1] SELECT `vars`, `ip`, `changed`, now() AS ts FROM `session` WHERE `sess_id` = '2t6im6u2ogsogp85g2m4as9ss1';


Thanks.
Dave.

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,430
    • http://SKaero.com/
Re: Roundcube, password Plugin, and Mysql
« Reply #3 on: March 12, 2018, 07:12:19 PM »
Does your Roundcube database user have access to the postfix.virtual_users table? Are you setting a separate database username and password?

Offline alec

  • Hero Member
  • *****
  • Posts: 1,118
Re: Roundcube, password Plugin, and Mysql
« Reply #4 on: March 13, 2018, 03:12:07 AM »
I'm guessing you enabled per_user_logging, but you didn't create per-user logs folder.

Offline dmehler

  • Newbie
  • *
  • Posts: 4
Re: Roundcube, password Plugin, and Mysql
« Reply #5 on: March 13, 2018, 12:45:56 PM »
Hello,

Thanks. Yes I did have the per_user log setting enabled, I turned it off and the sql file shows the running of queries but nothing like an error.

[13-Mar-2018 12:31:12 -0400]: [1] SELECT `vars`, `ip`, `changed`, now() AS ts FROM `session` WHERE `sess_id` = '3qpi80gft6n39ebs9phfg7mno2';
[13-Mar-2018 12:31:12 -0400]: [2] SELECT * FROM `users` WHERE `user_id` = '1';
[13-Mar-2018 12:31:12 -0400]: [3] SELECT id FROM carddav_addressbooks WHERE user_id='1' AND active=1;
[13-Mar-2018 12:31:12 -0400]: [4] UPDATE postfix.virtual_users SET password=CONCAT(_utf8'{SHA512-CRYPT}',ENCRYPT(_utf8'PasswordGoesHere',CONCAT(_utf8'$6$', SUBSTRING(SHA(RAND()), -16)))) WHERE user='userGoesHere' LIMIT 1;
[13-Mar-2018 12:31:12 -0400]: [5] SELECT * FROM `identities` WHERE `del` <> 1 AND `user_id` = '1' ORDER BY `standard` DESC, `name` ASC, `email` ASC, `identity_id` ASC;



Any other suggestions welcome.

Thanks.
Dave.

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,140
Re: Roundcube, password Plugin, and Mysql
« Reply #6 on: March 14, 2018, 03:33:26 AM »
and if you connect to the db using the same creds as you have specified in the plugin and run the update sql manually then it comes back with 1 row updated?

looking at the code in the password driver it should either come back with password_success or password_error. you may need to put some extra debug code into the php to track this down.
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and more…

Offline dmehler

  • Newbie
  • *
  • Posts: 4
Re: Roundcube, password Plugin, and Mysql
« Reply #7 on: March 15, 2018, 05:29:51 PM »
Hello,

Thanks for everyone's reply. I was going to write and say I was still getting the could not save error with very little debugging, but I fixed the issue.

First of all I've got a field in my database that was originally username a full email address I was using %u at the end of the query, which I'm sure is in this thread somewhere and was not working. I then made a database addition and split that field in to username and a second field for domain. I was still getting the could not save error until I changed the %u at the end of the query to a %l that gave me a successful save, updated the password, and confirmed so in the logs/password file.

I am still getting some error I am not sure if this is related or not. In my logs/session file I am seeing:

[15-Mar-2018 13:05:18 -0400]: Session auth check failed for ii8cfr3g6gl0tqjp553nit5vp4; timeslot = 2018-03-15 13:05:00
[15-Mar-2018 13:05:18 -0400]: Send new auth cookie for ii8cfr3g6gl0tqjp553nit5vp4: NI98s3uAIq3vMPCgMx9OJw1NS1-1521133200
[15-Mar-2018 13:10:18 -0400]: Session auth check failed for ii8cfr3g6gl0tqjp553nit5vp4; timeslot = 2018-03-15 13:10:00
[15-Mar-2018 13:10:18 -0400]: Send new auth cookie for ii8cfr3g6gl0tqjp553nit5vp4: NI98s3uAIq3vMPCgMx9OJw1NS1-1521133500

Thanks for everyone's help.
Dave.