webmail client change pass

[joximu]

Update: new howto is in the wiki: http://www.isp-control.net/ispcp/wiki/howto_webmailpass

Ok, I did some digging in the infos about change_passwd in ispcp webmail.
There is an old thread with infos about how to do it with vhcs2 - but hey, vhcs2 is dead!

Ok. I'll write my notes down - it's not complete but will be...

Firs we need a mysql user which has access to the relevant tables/fields...

Code:

GRANT USAGE ON ispcp.*
TO 'ispcp-mail-user'@'localhost' IDENTIFIED BY '###greatpassword###'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;

GRANT SELECT (`domain_id`, `domain_name`
) ON `ispcp`.`domain` TO 'ispcp-mail-user'@'localhost';

GRANT SELECT (`domain_id`, `alias_id`, `alias_name`
) ON `ispcp`.`domain_aliasses` TO 'ispcp-mail-user'@'localhost';

GRANT SELECT (`domain_id`, `subdomain_id`, `subdomain_name`
) ON `ispcp`.`subdomain` TO 'ispcp-mail-user'@'localhost';

GRANT SELECT ( `mail_acc`, `domain_id`, `sub_id`, `mail_type`, `status`, `mail_pass`),
UPDATE (`mail_pass` , `status`) ON `ispcp`.`mail_users` TO 'ispcp-mail-user'@'localhost';

Then we need to install the "change_sqlpass" plugin in webmail (squirrel). It needs the compatibility plugin but this seems already to be delivered with ispcp - change_sql_password on squirrelmail.org:
http://www.squirrelmail.org/plugin_view.php?id=25
Direct download: http://www.squirrelmail.org/plugin_downl...5&rev=1226
The plugin has to be stored in /var/www/ispcp/gui/tools/webmail/plugins/change_sqlpass - the last folder name is already in the tar file downloaded...
set the ownership and permissions to vu2000:vu2000 444 (files)

We have to activate the plugin
add this line to .../ispcp/gui/tools/webmail/config/config_local.php:

Code:

$plugins[26] = 'change_sqlpass';

(the index number should be higher than the ones from the already activated plugins...)

Then we need to adopt the config.php:
cp config.php.sample config.php

There are 3 variables to set...

Code:

$csp_dsn = 'mysql://ispcp-mail-user:###greatpassword###@localhost/ispcp';

$lookup_password_query = "SELECT count(DISTINCT t1.`mail_id`)
    FROM `mail_users` t1, `domain` t2, `domain_aliasses` t3, `subdomain` t4
    WHERE t1.`mail_acc` = '%2' AND t1.`mail_pass` = '%5' AND
     t2.`domain_id` = t1.`domain_id` AND t3.`domain_id` = t1.`domain_id` AND t4.`domain_id` = t1.`domain_id` AND
    (
     (t2.`domain_name` = '%3' AND  t1.`mail_type` = 'normal_mail') OR
     (t3.`alias_name` = '%3' AND t3.`alias_id` = t1.`sub_id` AND t1.`mail_type` = 'alias_mail') OR
     (CONCAT(t4.`subdomain_name`, '.', t2.`domain_name`) = '%3' AND t4.`subdomain_id` = t1.`sub_id` AND t2.`domain_id` = t1.`domain_id` AND t1.`mail_type` = 'subdom_mail')
    )";

$password_update_queries = array(
...not finished...
   );

well - there is the some complex sql code (since we don't have a filed with the complete mail address) - and the third one I don't have yet :-)
I first want to check if the WHEREs are all ok - before I change the passwords randomly :-)

So, maybe BeNe can open a wiki page for this... and maybe someone can have a look at this sql thing... (result of the lookup should be 1) :-)

Next step: last query and then a function call to "send_request()"...

/Joximu