Virtual Users And Domains With Postfix, And Roundcube?

Discussion in 'HOWTO-Related Questions' started by huey23, Dec 10, 2010.

  1. huey23

    huey23 New Member

  2. falko

    falko Super Moderator

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

    klonos New Member

    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:

    Code:
    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:

    Code:
    // 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';
    
    Code:
    // SQL Driver options
    // ------------------
    // PEAR database DSN for performing the query. By default
    // Roundcube DB settings are used.
    $rcmail_config['password_db_dsn'] = '';
    
    Code:
    // 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:

    Code:
    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.
    
     
    Last edited: Dec 13, 2011

Share This Page