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 Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    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