Virtual Users And Domains With Postfix, And Roundcube?

[huey23]

Hello all,

I recently followed this tutorial:
http://www.howtoforge.com/virtual-us...l-ubuntu-10.04

I noticed it said Squirrelmail but I was wondering if there was a way to use Roundcube instead? If I install Roundcube will I need to do anything special to query for usernames and passwords in the MySQL db?

Any help is appreciated...

[falko]

Basically it should be no problem to use Roundcube, but I don't know if it can be configured to also change passwords.

[klonos]

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 main.inc.php.dist file for
 more info.

 Example implementations of an update_passwd function:

 - This is for use with LMS (http://lms.org.pl) database and postgres:

	CREATE OR REPLACE FUNCTION update_passwd(hash text, account text) RETURNS integer AS $$
	DECLARE
    	    res integer;
	BEGIN
    	    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;
	END;
	$$ LANGUAGE plpgsql SECURITY DEFINER;

 - 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
    	    MODIFIES SQL DATA
	BEGIN
	    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;
	END

 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.