Roundcube Community Forum

 

Password plugin sql query fails [SOLVED]

Started by djmcq, July 03, 2023, 02:01:48 AM

Previous topic - Next topic

djmcq

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

Jul  3 15:30:22 fast roundcube: <en9u6fuf> [1] UPDATE mailbox SET password='7f7fc0890ac49a7e9f741f9c7e4d7e3dfc660664c98bfcac3f0a2ba6a' WHERE username ='[email protected]';
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 ='[email protected]') 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:
$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.
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:
$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
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! :
<?php
$config
['password_driver'] = 'sql';
$config['password_strength_driver'] = null;
$config['password_confirm_current'] = false;
$config['password_minimum_length'] = "";
$config['password_minimum_score'] = 0;
$config['password_log'] = true;
$config['password_login_exceptions'] = null;
$config['password_hosts'] = null;
$config['password_force_save'] = true;
$config['password_force_new_user'] = false;
$config['password_algorithm'] = 'dovecot';
$config['password_algorithm_prefix'] = '';
$config['password_dovecotpw'] = '/usr/bin/doveadm pw';
$config['password_dovecotpw_method'] = 'CRAM-MD5';
$config['password_dovecotpw_with_method'] = false;
$config['password_blowfish_cost'] = 12;
$config['password_disabled'] = false;
$config['password_username_format'] = '%u';
$config['password_http_client'] = [];
$config['password_db_dsn'] = 'mysql://postfix:xxxxxxxx@localhost/postfix';
$config['password_query'] = 'UPDATE mailbox SET password=%c WHERE username =%u';
$config['password_crypt_hash'] = 'md5';
$config['password_idn_ascii'] = false;
$config['password_hash_algorithm'] = 'sha1';
$config['password_hash_base64'] = false;


[&#39; means single quote]

alec

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

djmcq

Quote from: alec on July 07, 2023, 08:30:07 AM
%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)