» IT tipps and howto's

Howto: Change Confixx email account password in Roundcube

Originally written on August 14 2010
Last update on October 21 2011

One of Roundcube's extensions is the 'password' plugin which allows the logged user to change his password. Several solutions exist, e.g. to change the password via a SQL query, via LDAP, chpasswd, and so on. In this case the goal is to change the password of a user managed by Confixx and where the email account's password usually only could be changed by the Confixx user.

This How To is showing step by step how to grant sql permissions, activate the plugin, and use the correct sql query for Confixx's databases. Only experienced administrators should follow this howto and under your own risk. A backup of the Confixx databases before activating this plugin is highly recommended. I've done this with the latest Roundcube 0.4-stable version.

Enjoy.

1. Grant needed SQL permissions

The first step - we could also call this preparation - is to grant the correct permissions to the DB-user you use for your Roundcube installation. To determine your Roundcube user check your db.inc.php located in Roundcube's conf directory.
Search for the line $rcmail_config['db_dsnw'] - in this variable you will find your Roundcube DB-user.

$rcmail_config['db_dsnw'] = 'mysql://roundcube:xxx@localhost/roundcubemail';

So we will continue the How To with the DB-user 'roundcube'. Now grant the following permissions for the db-user 'roundcube':
- SELECT in database confixx, table pop3, coloumns account and longpw
- UPDATE in database confixx, table pop3, coloumn longpw
- UPDATE in database confixx, table allgemein, coloumn newpwd

mysql> GRANT SELECT, UPDATE ON confixx.pop3.longpw TO 'roundcube'@'localhost' IDENTIED BY 'xxx';
mysql> GRANT SELECT ON confixx.pop3.account TO 'roundcube'@'localhost' IDENTIED BY 'xxx';
mysql> GRANT SELECT, UPDATE ON confixx.allgemein.newpwd TO 'roundcube'@'localhost' IDENTIED BY 'xxx';

You might wonder, why you need to have UPDATE rights on confixx.allgemein.newpwd. Actually this value needs to be changed so that Confixx knows there has been a change of a password and the next time the Confixx Counterscript runs, the system passwords (in /etc/shadow) will be updated. If you don't update confixx.allgemein.newpwd the password will only be changed in the pop3 table but not in the system - this will cause that you lose connection to your IMAP server.

 

2. Activate the password plugin in Roundcube

Open your Roundcube main.inc.php configuration located in the config folder. Search for the line containing the word plugins and enter or add the password plugin in the array:

$rcmail_config['plugins'] = array('password');

 

3. Setting the SQL query in the password plugin

Change into the Roundcube's password plugin folder and copy config.inc.php.dist to config.inc.php:

/var/www/roundcube# cd plugins/password
/var/www/roundcube/plugins/password# cp config.inc.php.dist config.inc.php

Open config.inc.php and confirm that sql has been chosen as driver. If not, you have to change it to sql:

$rcmail_config['password_driver'] = 'sql';

Now the most important part of the plugin: Search for the line $rcmail_config['password_query']. This variable will contain the actual SQL query to update the password. Change this to the following query:

$rcmail_config['password_query'] = 'UPDATE confixx.pop3, confixx.allgemein SET confixx.pop3.longpw=%c, confixx.allgemein.newpwd=2 WHERE (account=%u)';

This SQL query will update the password of the logged-in user and set the newpwd parameter in the Confixx table allgemein to the value 2 - meaning for the Counterscript to update the system passwords.

 

4. Small change in plugins/password/drivers/sql.php

This information was sent to me by Aselm on October 21st 2011, thanks a lot for the hint!

You must change a "bug" in /plugins/password/drivers/sql.php. The following line (142)
"if ($db->affected_rows($res) == 1)"
must be changed to
"if ($db->affected_rows($res) > 0"
because the sql-query will be affect 2 rows.

 

5. Test and enjoy

Logged into Roundcube, change into the Settings and you will find a new tab called 'Password'. It looks like this:

Now you can change your password logged into Roundcube and it is not necessary anymore to log into Confixx to change the password.