[sork] Re: passwd 2.2.1-RC1 + vpopmail + mysql

[Chris Nojima]

Hi Darren,

Been looking over the horde/passwd code a bit.  I'm not familiar enough 
with their coding specifications to submit a proper patch.

just to reiterate some of the things i wrote to you privately:
- the error we've been experiencing using passwd with vpopmail+mysql is 
due to the way vpopmail encrypts passwords: explained in much more depth 
here: 
http://article.gmane.org/gmane.comp.horde.sork/1114/match=passwd+vpopmail
+patch

so, to get horde/passwd to "correctly" encrypt the user's password, we 
have to update its encryption scheme to match that being used by 
vpopmail.  Dorneles Treméa does a tremendous job explaining/providing a 
patch, so i won't bother with this point anymore.

his patch combined with a current passwd-RELENG distribution worked 
perfectly for me.

on to your particulars:
 
> The authentication I'm trying to use is vpopmail, so the values I'm using
> are 'pw_name' and 'pw_passwd', which are correct. The problem is that
> 'pw_domain' doesn't exist in this vpopmail configuration. Neither does the
> 'vpopmail' table. Inside the vpopmail database, there are tables created for
> every domain name in this format:

right, your problem stems from the use of "--disable-many-domains" at 
the configuration point of your vpopmail (which creates a table for each 
virtual domain instead of storing all users in a single table).  i think 
you mentioned using a qmail-toaster distribution.  curious that it came 
this way, as iirc, that's not a default option at configuration time.

passwd currently doesn't seem to support that particular part of 
vpopmail. 

in the "backends.php" file, this is the default setting for using 
vpopmail as the backend:

/**************************************************************/
$backends['vpopmail'] = array (
    'name' => 'vpopmail Authentication',
    'preferred' => '',
    'password policy' => array(
        'minLength' => 3,
        'maxLength' => 8,
        'maxSpace' => 0,
        'minUpper' => 0,
        'minLower' => 0,
        'minNumeric' => 0
    ),
    'driver' => 'vpopmail',
    'params' => array(
        'phptype'    => 'mysql',
        'hostspec'   => 'localhost',
        'username'   => 'user',
        'password'   => '******',
        'encryption' => 'crypt',
        'database'   => 'vpopmail',
        'table'      => 'vpopmail',
        'name'    => 'pw_name',
        'domain'  => 'pw_domain',
   'passwd' =>  'pw_passwd',
   'clear_passwd' => 'pw_clear_passwd',
   'use_clear_passwd' => true
    )
);
/**************************************************************/

it's hard-coded to use "vpopmail" as the table to retrieve/update 
password information.

follow the code a bit and you find this bit, which takes the user name, 
domain info, and submitted password to compare against the data in your 
database.  all of this is taken from 
"horde/passwd/lib/Driver/vpopmail.php" and we're looking at the function 
"_lookup($user, $oldPassword)":

/**************************************************************/
    function _lookup($user, $oldPassword)
    {
        // Connect to the database
        $res = $this->_connect();
        if (PEAR::isError($res)) {
            return $res;
        }

        list($name,$domain)=explode("@",$user);

        // Build the SQL query.
      $query  = 'SELECT ' . $this->_params['passwd'] . ' FROM ' . 
$this->_params['table'];
      $query .= ' WHERE ' . $this->_params['name'] . ' = ' . 
$this->_db->quote($name);
      $query .= ' AND ' . $this->_params['domain'] . ' = ' . 
$this->_db->quote($domain);

>>[ the function continues, but i've snipped it here ]
/**************************************************************/

this would result in query such as: 
"SELECT pw_passwd FROM vpopmail WHERE pw_name = 'username' AND pw_domain 
= 'example.com'".  since your setup doesn't use the vpopmail table and 
instead uses individual tables to represent what would be in the 
pw_domain column, it would obviously fail every time.

for a 1-table-per-domain vpopmail setup, the pertinent change is to the 
"' FROM ' . $this->_params['table'];" bit.

if you want a quick, testable fix, try replacing the stuff under 
"//Build the SQL query" comment to this:

/**************************************************************/
$this->_params['table'] = strtr($this->_params['domain'], '.', '_');

$query  = 'SELECT ' . $this->_params['passwd'] . ' FROM ' . 
$this->_params['table'];
$query .= ' WHERE ' . $this->_params['name'] . ' = ' . 
$this->_db->quote($name);
/**************************************************************/

this will return a query like:
"SELECT pw_passwd FROM example_com WHERE pw_name = 'username'"

since i don't have this particular setup, i can't test it to make sure.  
instead of looking for 'table', it rewrites the passed 'domain' var 
replacing '.' with underscores as vpopmail does for it's domains.

> 
> +--------------------+
> | Tables_in_vpopmail |
> +--------------------+
> | dir_control        |
> | dnbni_com          |
> | lastauth           |
> | vlog               |
> +--------------------+
> 
> In the dnbni_com table, there are these fields:
> 
> pw_name (the username WITHOUT the domain name appended to it)
> pw_password (The encrypted "$1" type password)
> pw_uid (it's always 0)
> pw_gid (it's always 0)
> pw_gecos (User Full Name field)
> pw_dir (actual user subdirectory)
> pw_shell (quota size)
> pw_clear_password (cleartext password)
> 


the other part here is to update the user's password once the check is 
successful and positive.  here's the default function:

/**************************************************************/
    function _modify($user, $newPassword) {

        // Connect to the database
        $res = $this->_connect();
        if (PEAR::isError($res)) {
            return $res;
        }

        list($name,$domain)=explode("@",$user);

        // Encrypt the password
        $clearPassword = $newPassword;
        $newPassword = $this->encryptPassword($newPassword);

        // Build the SQL query.
        $query = 'UPDATE ' . $this->_params['table'];
        $query .= ' SET ' . $this->_params['passwd'] . ' = ' . 
$this->_db->quote($newPassword);
    if($this->_params['use_clear_passwd']) {
     $query.= ' , ' . $this->_params['clear_passwd'] . ' = ' . 
$this->_db->quote($clearPassword);
    }
        $query .= ' WHERE ' . $this->_params['name'] . ' = ' . 
$this->_db->quote($name);
        $query .= ' AND ' . $this->_params['domain'] . ' = ' . 
$this->_db->quote($domain);

        // Execute the query.
        $result = $this->_db->query($query);

        if (DB::isError($result)) {
            return $result;
        }

        return true;
    }
/**************************************************************/

this will return something like:
"UPDATE vpopmail SET pw_passwd = 'encryptedpassword' , pw_passwd_clear = 
'clearpassword' WHERE pw_name = 'username' AND pw_domain = 'example.com'"


my changes, again untested:

/**************************************************************/
// Build the SQL query.
$this->_params['table'] = strtr($this->_params['domain'], '.', '_');

$query = 'UPDATE ' . $this->_params['table'];
$query .= ' SET ' . $this->_params['passwd'] . ' = ' . 
$this->_db->quote($newPassword);
if($this->_params['use_clear_passwd']) {
   $query.= ' , ' . $this->_params['clear_passwd'] . ' = ' . 
$this->_db->quote($clearPassword);
}
$query .= ' WHERE ' . $this->_params['name'] . ' = ' . 
$this->_db->quote($name);
/**************************************************************/

this will return something like:
"UPDATE example_com SET pw_passwd = 'encryptedpassword' , 
pw_clear_passwd = 'clearpassword' WHERE pw_name = 'username'"

that should update your user's password using your vpopmail setup.

if you haven't applied Dorneles Trema's patch, then all this will be 
moot as it won't be comparing identically encrypted passwords, and will 
hence fail every time.

I hope this helps!

-chris nojima