Password plugin/SQL error 1045

[lewispgj]

Hi All,

Sorry for any superfluous info, but I want to make sure fellow noobs know they're reading the right question 

I installed postfix/dovecot using this guide https://library.linode.com/email/postfix/postfix2.9.6-dovecot2.0.19-mysql, which recommended user 'mailuser' and database 'mailserver'.

I've got roundcube sending and receiving emails, but I'm having trouble implementing the password change plugin. I get the "Could not save new password" error.

The guide that I used to set up postfix/dovecot also suggested a password encryption function, as follows:

Code: [Select]

INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('firstpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
  ('2', '1', ENCRYPT('secondpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');
I'm wondering if I have failed to correctly set driver options. I've currently got:

Code: [Select]

$rcmail_config['password_db_dsn'] = 'mysql://mailuser:password@localhost/mailserver';
$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
$rcmail_config['password_crypt_hash'] = 'md5';
Of note, perhaps, is the following error from roundcube/logs/errors, immediately after attempting to change password in roundcube:

[04-Nov-2013 14:31:07 -0800]: DB Error: SQLSTATE[28000] [1045] Access denied for user 'mailuser'@'localhost' (using password: YES) in /var/www/round/program/lib/Roundcube/rcube_db.php on line 154 (POST /round/?_task=settings&_action=plugin.password-save?_task=&_action=)

Additionally, I am unable to $mysql -u mailuser -p, from the terminal; it results in a very similar error:

ERROR 1045 (28000): Access denied for user 'mailuser'@'localhost' (using password: YES)

So I'm thinking that it's either a bad driver option configuration or there's some kind of accessibility problem with MySQL. As I said before, I can login/send/receive mail just fine, so I would be more inclined to think it was a driver config problem, if it weren't for the SQL errors above.

Thanks for any help you can offer!


EDIT: In case it was something weird with MySQL access, I tried changing password_db_dsn from localhost to 127.0.0.1: $rcmail_config['password_db_dsn'] = 'mysql://mailuser:password@127.0.0.1/mailserver';

roundcube/logs/errors now reads:

[04-Nov-2013 15:00:13 -0800]: DB Error: [1305] FUNCTION mailserver.update_passwd does not exist (SQL Query: SELECT update_passwd('xxx', 'myuser@example.com')) in /var/www/round/program/lib/Roundcube/rcube_db.php on line 416 (POST /round/?_task=settings&_action=plugin.password-save?_task=&_action=)

Is that a sign of progress? 

[SKaero]

Is that a sign of progress? 

Yes now it is able to connect to the mysql server, before it couldn't. You need to update the password_query line with a SQL that works to update the password in the database, the SQL query that is there is just a place holder.

[lewispgj]

I next tried...

Code: [Select]

$rcmail_config['password_query'] = 'UPDATE mailserver SET password=%D WHERE username=%u';...which resulted in "Could not update password. Encryption function missing."

I reviewed the guide I had used, noting...

Code: [Select]

default_pass_scheme = SHA512-CRYPT...in /etc/dovecot/dovecot-sql.conf.ext

The guide that I used to set up postfix/dovecot also suggested a password encryption function, as follows:

Code: [Select]

INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('firstpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
  ('2', '1', ENCRYPT('secondpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');
Line 110 of the password plugin readme states:

Use a MYSQL crypt function (*nix only) with random 8 character salt
 -UPDATE users SET password=ENCRYPT(%p,concat(_utf8'$1$',right(md5(rand()),8),_utf8'$')) WHERE username=%u LIMIT 1

Having read another user's bad experience using this command, I attempted to modify password_query as follows:

Code: [Select]

$rcmail_config['password_query'] = 'UPDATE mailserver SET password=ENCRYPT(%p,concat('$6$', SUBSTRING(SHA(RAND()), -16))) WHERE username=%u LIMIT 1';
Which resulted in this error, from the roundcube log:

[05-Nov-2013 19:08:20 UTC] PHP Parse error:  syntax error, unexpected '$' in /var/www/round/plugins/password/config.inc.php on line 45

This breaks the roundcube settings page; neither the password option nor any other options are accessible for the user.

Maybe I'm lacking independence or insight, but password_query seems very challenging for the non-expert system admin to configure. If there's any way to help newcomers configure this more easily, I'm sure it would be much appreciated and much more utilized.

Can you show me the correct syntax, SKAero?

Thanks!

EDIT: adding the _utf8 syntax, with or without the trailing "_utf8'$'", also does not work.

Code: [Select]

$rcmail_config['password_query'] = 'UPDATE mailserver SET password=ENCRYPT(%p,concat(_utf8'$6$', SUBSTRING(SHA(RAND()), -16),_utf8'$')) WHERE username=%u LIMIT 1';
EDIT2: I doubt this is relevant, but "CRAM-MD5" was worrying me, so here's more from my config:

Code: [Select]

$rcmail_config['password_crypt_hash'] = 'sha512';
$rcmail_config['password_idn_ascii'] = false;
$rcmail_config['password_dovecotpw'] = '/usr/sbin/dovecot';
$rcmail_config['password_dovecotpw_method'] = 'CRAM-MD5';
$rcmail_config['password_dovecotpw_with_method'] = false;
$rcmail_config['password_hash_algorithm'] = 'sha1';
$rcmail_config['password_hash_base64'] = false;
EDIT3: Curiously, even the readme-supplied crypt function (line 110) breaks roundcube:

Code: [Select]

$rcmail_config['password_query'] = 'UPDATE users SET password=ENCRYPT(%p,concat(_utf8'$1$',right(md5(rand()),8),_utf8'$')) WHERE username=%u LIMIT 1';
I presume that adjoining _utf8 to '$1$' was supposed to prevent this error, so maybe it's as simple as using a different prefix? I'm totally lost on this one. Any good ideas out there? 

[SKaero]

Your quotes are wrong on the query, you need double quotes on the end because your using single quotes in the query. Unfortunately the password plugin does require knowledge of how the system is setup because there isn't one universal api to change passwords that work on all systems.

[lewispgj]

Success!    Whew, tough stuff. For those who have a database configured like mine and need a little help, these are the settings that worked for me:

$rcmail_config['password_query'] = 'UPDATE virtual_users SET password=ENCRYPT(%p,concat("$6$", SUBSTRING(SHA(RAND()), -16))) WHERE email=%u LIMIT 1';
$rcmail_config['password_crypt_hash'] = 'sha512';
$rcmail_config['password_idn_ascii'] = false;
$rcmail_config['password_dovecotpw'] = '/usr/sbin/dovecot';
$rcmail_config['password_dovecotpw_method'] = 'CRAM-MD5';
$rcmail_config['password_dovecotpw_with_method'] = false;
$rcmail_config['password_hash_algorithm'] = 'sha1';
$rcmail_config['password_hash_base64'] = false;

I don't know how to edit the roundcube readme (if it's even possible), but line 111 should perhaps be changed from...

Code: [Select]

UPDATE users SET password=ENCRYPT(%p,concat(_utf8'$1$',right(md5(rand()),8),_utf8'$')) WHERE username=%u LIMIT 1...to...

Code: [Select]

UPDATE users SET password=ENCRYPT(%p,concat(_utf8"$1$",right(md5(rand()),8),_utf8"$")) WHERE username=%u LIMIT 1
Thanks again for the help, SKaero!