Password plugin sql query fails [SOLVED]

[djmcq]

I'm using Postfix/Dovecot/Roundcube on Ubuntu 20.04

Code: [Select]

Jul  3 15:30:22 fast roundcube: <en9u6fuf> [1] UPDATE mailbox SET password='7f7fc0890ac49a7e9f741f9c7e4d7e3dfc660664c98bfcac3f0a2ba6a' WHERE username ='test@example.com.au';
Jul  3 15:30:22 fast roundcube: <en9u6fuf> DB Error: [1142] UPDATE command denied to user 'postfix'@'localhost' for table 'mailbox' (SQL Query: UPDATE mailbox SET password='7f7fc0890ac49a7e9f741f9c7e4d7e3dfc660664c98bfcac3f0a2ba6a' WHERE username ='test@example.com.au') in /var/....../roundcube/program/lib/Roundcube/rcube_db.php on line 566 (POST /?_task=settings&_action=plugin.password-save)
in /var/..../plugins/password/config.inc.php:

Code: [Select]

$config['password_db_dsn'] = 'mysql://postfix:xxxxxxxx@localhost/postfix';
$config['password_query'] = 'UPDATE mailbox SET password=%P WHERE username =%u';
I tried copy/paste from the log file into the command line and the sql works correctly [see edit below!]. I've checked the postfix password.

It kinda looks like a permission problem, but I can't work out where.

[edit] I've just checked and it seems that postfix doesn't have "UPDATE" privilege on mysql. When I tried the mysql command I was logged into mysql as root, so of course it worked.

Code: [Select]

Grants for postfix@localhost                         |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `postfix`@`localhost`          |
| GRANT SELECT ON `postfix`.* TO `postfix`@`localhost`|
+------------------------------------------------------+
2 rows in set (0.00 sec)

Before I rush in and screw something up, is this the problem? Given that I have a pretty standard install, is this in the documentation anywhere?

Also, there is this line in the password config:

Code: [Select]

$config['password_dovecot_passwdfile_path'] = '/etc/mail/imap.passwd';but I have no idea what it is for. This path/file doesn't exist on my system.

[Dmitry42]

I think you can try something like this

GRANT ALL PRIVILEGES ON YourRoundcubeDBName.* TO postfix@localhost IDENTIFIED BY 'postfix PASSWORD';


I read this in one internet  "how to" and use it when I install my RC

[djmcq]

I tried my own suggestion

Code: [Select]

mysql> grant update (password) on mailbox to 'postfix'@'localhost';
I've never seen this step suggested anywhere.
I had already granted all privilege to the roundcube database when I initially installed.
The password now seems to update successfully.
I get a success message on the web page, the log doesn't throw an error, and the hash on the mysql database definitely changes.
Unfortunately the new password doesn't work.
I'm guessing I'm doing something wrong with the encryption method, but I'm not sure what.
If anyone can point me at a simple "howto" I would appreciate it.

[djmcq]

I seem to have it working.... this post was helpful:
https://stackoverflow.com/questions/62655236/how-to-enable-password-plugin-on-roundcube

The comments in plugins/password/config.inc.php say that %c is deprecated, but it works and %P doesn't (for me at least)
If anybody can tell me why, I would appreciate it for future reference.

The database that is being modified is your postfix database, not the roundbase database as implied by the comments
I had to grant update to the postfix user on mysql for it to work (see previous post)

this is my final plugin/password/config.inc.php - all comments invited! :

Code: [Select]

<?php
$config[&#39;password_driver&#39;] = &#39;sql&#39;;
$config[&#39;password_strength_driver&#39;] = null;
$config[&#39;password_confirm_current&#39;] = false;
$config[&#39;password_minimum_length&#39;] = "";
$config[&#39;password_minimum_score&#39;] = 0;
$config[&#39;password_log&#39;] = true;
$config[&#39;password_login_exceptions&#39;] = null;
$config[&#39;password_hosts&#39;] = null;
$config[&#39;password_force_save&#39;] = true;
$config[&#39;password_force_new_user&#39;] = false;
$config[&#39;password_algorithm&#39;] = &#39;dovecot&#39;;
$config[&#39;password_algorithm_prefix&#39;] = &#39;&#39;;
$config[&#39;password_dovecotpw&#39;] = &#39;/usr/bin/doveadm pw&#39;;
$config[&#39;password_dovecotpw_method&#39;] = &#39;CRAM-MD5&#39;;
$config[&#39;password_dovecotpw_with_method&#39;] = false;
$config[&#39;password_blowfish_cost&#39;] = 12;
$config[&#39;password_disabled&#39;] = false;
$config[&#39;password_username_format&#39;] = &#39;%u&#39;;
$config[&#39;password_http_client&#39;] = [];
$config[&#39;password_db_dsn&#39;] = &#39;mysql://postfix:xxxxxxxx@localhost/postfix&#39;;
$config[&#39;password_query&#39;] = &#39;UPDATE mailbox SET password=%c WHERE username =%u&#39;;
$config[&#39;password_crypt_hash&#39;] = &#39;md5&#39;;
$config[&#39;password_idn_ascii&#39;] = false;
$config[&#39;password_hash_algorithm&#39;] = &#39;sha1&#39;;
$config[&#39;password_hash_base64&#39;] = false;
[' means single quote]

[alec]

%c uses crypt, %P uses the configured password_algorithm and related settings.

[djmcq]

%c uses crypt, %P uses the configured password_algorithm and related settings.

excuse my ignorance, but configured where? Dovecot? I would have thought that %P would work, but it doesn't (in my case at least)