View Single Post
Old 13th December 2011, 01:58
klonos klonos is offline
Join Date: Apr 2007
Posts: 78
Thanks: 5
Thanked 3 Times in 3 Posts

I never had any problem setting roundcube up for the virtual users mail server. Its config is pretty straight-forward. The only thing I cannot figure out is how to enable password change.

The core tarball of roundcube includes a 'password' plugin that once enabled provides a "Password" tab in each user account's settings page. Here's an excerpt of its readme file:

2. Drivers

 Password plugin supports many password change mechanisms which are
 handled by included drivers. Just pass driver name in 'password_driver' option.

 2.1. Database (sql)

 You can specify which database to connect by 'password_db_dsn' option and
 what SQL query to execute by 'password_query'. See file for
 more info.

 Example implementations of an update_passwd function:

 - This is for use with LMS ( database and postgres:

	CREATE OR REPLACE FUNCTION update_passwd(hash text, account text) RETURNS integer AS $$
    	    res integer;
    	    UPDATE passwd SET password = hash
	    WHERE login = split_part(account, '@', 1)
		AND domainid = (SELECT id FROM domains WHERE name = split_part(account, '@', 2))
	    RETURNING id INTO res;
	    RETURN res;

 - This is for use with a SELECT update_passwd(%o,%c,%u) query
	Updates the password only when the old password matches the MD5 password
	in the database

	CREATE FUNCTION update_password (oldpass text, cryptpass text, user text) RETURNS text
	    DECLARE currentsalt varchar(20);
	    DECLARE error text;
	    SET error = 'incorrect current password';
	    SELECT substring_index(substr(user.password,4),_latin1'$',1) INTO currentsalt FROM users WHERE username=user;
	    SELECT '' INTO error FROM users WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
	    UPDATE users SET password=cryptpass WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
	    RETURN error;

 Example SQL UPDATEs:

 - Plain text passwords:
    UPDATE users SET password=%p WHERE username=%u AND password=%o AND domain=%h LIMIT 1

 - Crypt text passwords:
    UPDATE users SET password=%c WHERE username=%u LIMIT 1

 - Use a MYSQL crypt function (*nix only) with random 8 character salt
    UPDATE users SET password=ENCRYPT(%p,concat(_utf8'$1$',right(md5(rand()),8),_utf8'$')) WHERE username=%u LIMIT 1

 - MD5 stored passwords:
    UPDATE users SET password=MD5(%p) WHERE username=%u AND password=MD5(%o) LIMIT 1
Any pointers of what the right SQL query would be???

These are the corresponding settings in my config:

// Password Plugin options
// -----------------------
// A driver to use for password change. Default: "sql".
// See README file for list of supported driver names.
$rcmail_config['password_driver'] = 'sql';
// SQL Driver options
// ------------------
// PEAR database DSN for performing the query. By default
// Roundcube DB settings are used.
$rcmail_config['password_db_dsn'] = '';
// The SQL query used to change the password.
// The query can contain the following macros that will be expanded as follows:
//      %p is replaced with the plaintext new password
//      %c is replaced with the crypt version of the new password, MD5 if available
//         otherwise DES.
//      %D is replaced with the dovecotpw-crypted version of the new password
//      %o is replaced with the password before the change
//      %n is replaced with the hashed version of the new password
//      %q is replaced with the hashed password before the change
//      %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)
// Escaping of macros is handled by this module.
// Default: "SELECT update_passwd(%c, %u)"
$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
PS: ...there's also an API so one can code their own password driver:

3. Driver API

 Driver file (<driver_name>.php) must define 'password_save' function with
 two arguments. First - current password, second - new password. Function
 should return PASSWORD_SUCCESS on success or any of PASSWORD_CONNECT_ERROR,
 PASSWORD_CRYPT_ERROR, PASSWORD_ERROR when driver was unable to change password.
 Extended result (as a hash-array with 'message' and 'code' items) can be returned
 too. See existing drivers in drivers/ directory for examples.
You can support Howtoforge and all the people behind it too. Consider becoming a supporter. It only costs a few and has to offer so much more than it already does. Take a look here

Last edited by klonos; 13th December 2011 at 02:04. Reason: ...what I've done so far.
Reply With Quote