I have looked at many forum posts on the roundcube and zpanel forums about the roundcube password plugin. And I just can't get it to work.
Some details about the server:
Ubuntu 12.04 LTS x64 server
zpanel 10.1.0 x64 installed via .x file
Which then installed the LAMP dependancies, Dovecot, etc.
Working email, etc. Now working on customizing roundcube. First thing I need is the password plugin.
I see the passwords in a mysql table. So I feel I should be focusing on the sql driver.
When I try to change the password with the enabled plugin (which came with the zpanel install of roundcube), this is the message I get as I have debugging level at 4 to see the error at the top of the page.
DB Error in /etc/zpanel/panel/etc/apps/webmail/program/lib/Roundcube/rcube_db.php (416): [1305] FUNCTION zpanel_roundcube.update_passwd does not exist (SQL Query: SELECT update_passwd('<hashed password created by roundcube>', '<user>@<domain>'))
I have also asked on the zpanel forum in case there are specifics to zpanel to consider. But I think this is just a roundcube question.
Not sure if this means anything. But I notice now that the hash being using to update SQL is not MD5 like the password that is already stored in mysql. Using an MD5 generator, I looked up what the new password hash would be. And it does not match what is listed in the error. Without looking into the PHP code, I do not know if it is doing some kind of encoding to pass on to mysql. But I am pretty sure it should be an MD5 hash with exactly how the SQL query would be run at the command prompt.
Though the SQL db shows {PLAIN-MD5} before the hash. Something I also don't see in the error message which shows the SQL query.
The part of the error that concerns me the most, is the "function missing" aspect. Is there more to it than enabling the plugin via the 2 config files? main.inc.php and config.inc.php ?
Getting closer I think. Looks like the Query is the main issue. Would be nice if the config had a few query examples and notes commented in it for reference.
So I switched the default that was there:
$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
and changed it to:
$rcmail_config['password_query'] = "UPDATE `mailbox` SET `password` = %c, modified=now() WHERE `username` = %u LIMIT 1";
And now I see an error that says the table does not exist. Specifically:
Table 'zpanel_roundcube.mailbox' doesn't exist (SQL Query: UPDATE `mailbox` SET `password` = '<encoded password>', modified=now() WHERE `username` = '<user>@<domain>' LIMIT 1)
The issue now appears to be that it thinks the SQL table is in a DB called 'zpanel_roundcube', but the mailbox table with the passwords is actually in a DB called 'zpanel_postfix'
I really don't want to specify the db in the Query. I would like to figure out where it is getting the variable set as 'zpanel_roundcube' and change it there as it likely is used elsewhere too.
Any ideas?
Update: Nevermind, when I give the db name in the QUERY, it still uses the variable somewhere and appends 'zpanel_roundcube' to the query no matter what.
So I see the password plugin uses the default roundcube db.inc.php config for the db connection. And when I look at that file, it of course is connecting to the zpanel_roundcube DB. Rightfully so. But that isn't where my passwords are.
So I am forcing a different db connection by changing the default:
$rcmail_config['passwd_db_dsn'] = '';
to connect specifically to my postfix DB that has the mailbox table with passwords:
$rcmail_config['passwd_db_dsn'] = 'mysql://<db_user>:<db_password>@<db_host>/zpanel_postfix';
And now it works. Since this is the password plugin config, this should only affect this plugin.
While it now updates the password in the correct DB that I am using. And I am able to login using the new password, I will call this solved. BUT, I do see that the hash method appears to be different. The original zpanel password hash had {PLAIN_MD5} before the actual password hash that is an MD5 style hash. But the new password using the above solution, is NOT an MD5 hash and it does not have the {PLAIN_MD5} preceding the new hash. Yet I can still login and use roundcube email.
I will keep looking to see if I can have the password plugin create the same MD5 hash method, but at least the plugin works for me now.