Author Topic: sqlite3: Error setting password using password plugin  (Read 1185 times)

Offline naidu

  • Newbie
  • *
  • Posts: 7
sqlite3: Error setting password using password plugin
« on: March 21, 2017, 08:42:30 AM »
I have a roundcube installation which uses sqlite3 database. I tried to enable the password plugin.

I see below error whenever I try to set the password:

[21-Mar-2017 13:00:31 +0100]: DB Error: [1] no such function: update_passwd (SQL Query: SELECT update_passwd('$1$LXeDlIT0$NGunS8gcCOSrKK2ZJ6RIW/', 'naidu@example.com')) in /opt/www/webmail/program/lib/Roundcube/rcube_db.php on line 539 (POST /webmail/?_task=settings&_action=plugin.password-save)

The internet is full of using mysql as the database. I think I have to update the password change query in

/opt/www/webmail/plugins/password/config.inc.php

from

$sql = 'SELECT update_passwd(%c, %u)';

to

$sql = 'UPDATE mailaccount SET password=%c WHERE nname=%u LIMIT 1';

The UPDATE statement above is valid for mysql. What is the equivalent for sqlite3 database?

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,219
    • http://SKaero.com/
Re: sqlite3: Error setting password using password plugin
« Reply #1 on: March 21, 2017, 10:10:12 AM »
You need to update the password in the mail server database not the Roundcube database.

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #2 on: March 21, 2017, 10:26:32 AM »
Thank you for the hint.  I see that the password is stored as SHA512-CRYPT.

Which setting in config.inc.php should be changed so that the new password also uses the same crypto?  Is it

$config['password_algorithm'] = 'clear'; or
$config['password_crypt_hash'] = 'md5';
 
Using the correct one is necessary here I guess otherwise the later will break.

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #3 on: March 21, 2017, 10:37:43 AM »
And the error has moved to below:

[21-Mar-2017 16:17:59 +0100]: <l9m5nfnk> DB Error: [14] unable to open database file (SQL Query: UPDATE users SET password='$1$c..7hjE.$bSFeqf.Y4D3nYZW3qZmSP1' WHERE address='naidu@example.com' LIMIT 1) in /opt/www/webmail/program/lib/Roundcube/rcube_db.php on line 539 (POST /webmail/?_task=settings&_action=plugin.password-save)

I verified that the db has 664 as the permission + www-data is the group owner.

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,219
    • http://SKaero.com/
Re: sqlite3: Error setting password using password plugin
« Reply #4 on: March 21, 2017, 10:54:18 AM »
Are you setting the $config['password_db_dsn'] line?

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #5 on: March 21, 2017, 10:59:36 AM »
Yes.  I did that. 

Then I received "Permission denied" error. 

When I looked the file permission, it was like:

-rw-r--r-- mail mail users.db.

I changed it to look like

-rw-rw-r-- mail www-data users.db

After this change, now the error is like I posted.

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,219
    • http://SKaero.com/
Re: sqlite3: Error setting password using password plugin
« Reply #6 on: March 21, 2017, 11:06:33 AM »
The web server process that is running Roundcube will need to be able to access it. I can't really help with that since I don't know how your users on the server are setup.

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #7 on: March 21, 2017, 11:10:52 AM »
The db path is mentioned like this:

$config['password_db_dsn'] = 'sqlite:////data/users.db.backup';

Does that look good?.  Also why I changed to www-data is because the rouncube db has same user as the owner of the file.

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #8 on: March 21, 2017, 11:23:48 AM »
ps aux gives me below ouput.

--stripped --
www-data   575  0.0  0.1  65568  3292 ?        S    16:51   0:00 nginx: worker process
www-data   576  0.0  0.0  65184  1424 ?        S    16:51   0:00 nginx: worker process
mail       592  0.0  0.3 347908  7120 ?        S    16:51   0:00 php-fpm: pool admin
mail       593  0.0  0.3 347908  7120 ?        S    16:51   0:00 php-fpm: pool admin
www-data   594  0.0  0.8 354568 16284 ?        S    16:51   0:00 php-fpm: pool www
www-data   595  0.0  0.8 350452 16060 ?        S    16:51   0:00 php-fpm: pool www
--stripped --

Does this output gives you any clue?

I have also set the file permission to 666 but still get the same error.

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,219
    • http://SKaero.com/
Re: sqlite3: Error setting password using password plugin
« Reply #9 on: March 21, 2017, 11:39:08 AM »
You'd want the user to be "mail" not "www-data" because php-fpm is running Roundcube. Nginx is just proxying the connection to php-fpm.

Offline naidu

  • Newbie
  • *
  • Posts: 7
Re: sqlite3: Error setting password using password plugin
« Reply #10 on: March 21, 2017, 11:43:22 AM »
Thanks for the clarification. 
Irrespective of the user, my current permission of the database file looks like below:

-rw-rw-rw-.  1 mail     mail     16384 Mar 21 17:14 users.db.backup

Shouldn't this be sufficient?  But still get

[21-Mar-2017 17:25:51 +0100]: <l9m5nfnk> DB Error: [14] unable to open database file (SQL Query: UPDATE users SET password='$1$Sb9plsl.$PXloGNh/LkexEKwKwL7sj/' WHERE address='naidu@example.com' LIMIT 1) in /opt/www/webmail/program/lib/Roundcube/rcube_db.php on line 539 (POST /webmail/?_task=settings&_action=plugin.password-save)

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,219
    • http://SKaero.com/
Re: sqlite3: Error setting password using password plugin
« Reply #11 on: March 21, 2017, 11:59:03 AM »
As far as I know thats fine, I don't use sqlite so I maybe missing something there.

Offline rm13

  • Full Member
  • ***
  • Posts: 96
Re: sqlite3: Error setting password using password plugin
« Reply #12 on: March 23, 2017, 07:27:21 PM »
I don't use sqlite either, but your probably do not want world read/write on that db file.