Cannot set password_query for Roundcube password plugin

[conderus]

Hi, I cannot set properly password_query from Roundcube password plugin, to change passwords by users. I tried many configs without result. Connection is through SQL, here are my settings:

Code: [Select]

$config['password_db_dsn'] = 'mysql://MY-USER:MY PASSWORD@conderus.mysql.dhosting.pl/MY-DATABASE-NAME’;
$config['password_crypt_hash'] = 'md5';
$config['password_idn_ascii'] = false;
$config['password_hash_algorithm'] = 'sha1';
$config['password_hash_base64'] = false;
In general at my hosting when I use ‘localhost’ or something like that then nothing works. I must always provide exact database host name, and other things.

The server is: Linux 3.2.0-4-amd64 #1 SMP Debian 3.2.82-1 x86_6

And bellow are my password_query settings and logs, every time different error:

Code: [Select]

$config['password_query'] = 'UPDATE users SET crypt=ENCRYPT(%p,CONCAT(_utf8\'$5$\',RIGHT(MD5(RAND()),8),_utf8\'$\')) WHERE id=%u LIMIT 1';
[18-Sep-2019 11:32:34 +0200]: <df308198> DB Error: [1054] Unknown column 'id' in 'where clause' (SQL Query: UPDATE users SET crypt=ENCRYPT('MY-PASSWORD-HERE',CONCAT(_utf8'$5$',RIGHT(MD5(RAND()),8),_utf8'$')) WHERE id='MY@MAIL.PL' LIMIT 1) in /home/klient.dhosting.pl/conderus/wielechowski.pl/public_html/program/lib/Roundcube/rcube_db.php on line 543 (POST /?_task=settings&_action=plugin.password-save)
[18-Sep-2019 11:32:54 +0200]: <df308198> PHP Error: Request security check failed (POST /?_task=mail&_action=refresh)


$config['password_query'] = 'SELECT update_passwd(%c, %u)';   
[18-Sep-2019 11:35:16 +0200]: <ef19691a> DB Error: [1305] FUNCTION ohcoh3_wielecho.update_passwd does not exist (SQL Query: SELECT update_passwd('HERE WAS SOME RANDOM STRING', 'MY@MAIL.PL')) in /home/klient.dhosting.pl/conderus/wielechowski.pl/public_html/program/lib/Roundcube/rcube_db.php on line 543 (POST /?_task=settings&_action=plugin.password-save)


$config['password_query'] = 'SELECT update_passwd(%c, %u)';
[18-Sep-2019 11:35:16 +0200]: <ef19691a> DB Error: [1305] FUNCTION ohcoh3_wielecho.update_passwd does not exist (SQL Query: SELECT update_passwd('HERE WAS SOME RANDOM STRING', 'MY@MAIL.PL')) in /home/klient.dhosting.pl/conderus/wielechowski.pl/public_html/program/lib/Roundcube/rcube_db.php on line 543 (POST /?_task=settings&_action=plugin.password-save)


$config['password_query'] = 'UPDATE mailbox SET password=%c WHERE username=%u';
[18-Sep-2019 11:38:44 +0200]: <b2841511> DB Error: [1146] Table 'ohcoh3_wielecho.mailbox' doesn't exist (SQL Query: UPDATE mailbox SET password='HERE WAS SOME RANDOM STRING' WHERE username='MY@MAIL.PL') in /home/klient.dhosting.pl/conderus/wielechowski.pl/public_html/program/lib/Roundcube/rcube_db.php on line 543 (POST /?_task=settings&_action=plugin.password-save)


$config['password_query'] = 'UPDATE mail_user SET password=%c WHERE email=%u LIMIT 1';
[18-Sep-2019 11:54:52 +0200]: <0593269d> DB Error: [1146] Table 'ohcoh3_wielecho.mail_user' doesn't exist (SQL Query: UPDATE mail_user SET password='HERE WAS SOME RANDOM STRING' WHERE email='MY@MAIL.PL' LIMIT 1) in /home/klient.dhosting.pl/conderus/wielechowski.pl/public_html/program/lib/Roundcube/rcube_db.php on line 543 (POST /?_task=settings&_action=plugin.password-save)
Maybe the problem is the user / mail / id is always input as my@mail.com , and I log in to Roundcube webmail by “my” only, not full mail.

I am not proficient in such things I just wanted to run webmail for self and family.

I have also one important question. When a user change password through webmail, I understand that IMAP / POP password ALSO will be changed ? Or this is only password to webmail ?

Thanks

[JohnDoh]

So you've tried several password queries but all of them have been wrong. I guess the first question, is which one is the right query or are none of the right - I mean do you store your IMAP user info in a table called users or mailbox or mail_user? Does the connection string you have put in password_db_dsn connect to the correct database?

I have also one important question. When a user change password through webmail, I understand that IMAP / POP password ALSO will be changed ? Or this is only password to webmail ?

Roundcube does not store passwords or authentic users, that is does by your IMAP server. Its why there are different drivers for the password plugin - you need to configure the correct driver for how you are storing user information in your IMAP server.

[kishon]

Normally the passwords are stored in file '/config/postfix-accounts.cf'.

I've just spent few hours to configure Roundcube with plugin 'password' and tried with internal SQLite and external MySQL but with no luck.
Will try tomorrow again further.

Option 'sql' does not help neither for MySQL nor SQLite.

Need to create table 'mailbox' with 'username', 'password' and other columns.