webmail client change pass

[deptrai]

I've installed Squirrelmail plugin: Change SQL Password.
Mail_pass value in database is change when i excute the plugin but mail account still need the old password to log in.
It seems ispcp stores mail pass in files or some others.
Please help me fix this problem.
Thank in advanced.

[joximu]

ispcp stores th password in the table "mail_users".
*But* the imap server does not look them up in the database - courier gets the user information from /etc/courier/user* (several files).
They are managed through the engine files of ispcp.
I think it's enough, if you set the status-files to "change" and execute the request manager (there is a php funtion for this).

Hope this helps.
/Joximu

[BeNe]

So you mean that ispCP writes the files for courier new !?
Ok - that could be.
But this means also that you must set the "change" & the new Password in the DB and run the request manager directly after that. Right ?

Greez BeNe

[Esni]

I have got a password changer to work for the mail users at vhcs so it must work with ispcp, too. I have used the mscgeek.com's password changer which works fantasicly, you might want to check that out.

[joximu]

BeNe Wrote:So you mean that ispCP writes the files for courier new !?
Ok - that could be.
But this means also that you must set the "change" & the new Password in the DB and run the request manager directly after that. Right ?

Greez BeNe

yes - by updateing the database/table with the new password you should set the status and after this you should call the function "send_request()" (from ispcp-function.php).

/J

[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

[BeNe]

Of course --> http://www.isp-control.net/ispcp/wiki/howto_webmailpass

Thanks for this great work joximu!

Greez BeNe

[joximu]

Thanx BeNe, it's "in work"... :-)

[joximu]

Finished - it needed some changes in the plugin code from squirrel (we don't have a filed in the database with just the full mail address in it).
But now this works - request manager signal is also included...

Feedback is welcome - Joximu

[BeNe]

Perfect!
After dovecot now this nice feature.

I will test it today. Thanks joximu!

Greez BeNe