Password plugin thread - with zpanel and SQL [SOLVED]

[xenon2000]

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.

Code Select Expand

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.

[xenon2000]

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.

[xenon2000]

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 ?

[xenon2000]

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:

Code Select Expand

$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';

and changed it to:

Code Select Expand

$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:

Code Select Expand

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.

[xenon2000]

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:

Code Select Expand

$rcmail_config['passwd_db_dsn'] = '';

to connect specifically to my postfix DB that has the mailbox table with passwords:

Code Select Expand

$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.