Changing the password on Roundcube WILL make Postfix unable to receive mail

[ddavness]

God help me, this took a big, big while to figure out

So a technical TL;DR on this: SQLite has a few logging modes, among them wal (Write-Ahead-Log) and delete (aka none).

1. Postfix can only work with the users.sqlite database when it is set to delete.
2. But when changing the password on Roundcube, it will set the database to wal.
3. Chaos ensues with errors like these:

postfix/trivial-rewrite[22530]: fatal: dict_sqlite_lookup: /etc/postfix/virtual-alias-maps.cf: SQL prepare failed: disk I/O error?

(which are as helpful as a submarine in the desert)
4. Postfix will never be able to process incoming mail because they cannot read the database the way the want to.

[ddavness]

Possibly related to mail-in-a-box/mailinabox#2185

[kiekerjan]

Issue 2185 concerns a misconfiguration of the Roundcube password plugin used by Mail-in-a-Box.

Update: removed link to Roundcube issue as I don't think it applies anymore.

[ddavness]

Both are indeed related. I want to keep an eye on how they'll eventually solve it.

This issue was caused due to the fix I applied on #77, though in all honesty is still a bit mysterious to me how it ended up happening: since that behavior goes back to Roundcube 1.4.x, I'd expect it to come up sooner.

Strangely enough nobody else reported this problem to me so far. I wonder if it actually happened to somebody else or if it was a spurious episode on my case.

[kiekerjan]

I see your solution for the #77 bug is the same as mine for the 2185 bug. Of course, because it is the same issue ;) I don't think #77 caused the postfix sqlite bug. Probably most people don't change their password via Roundcube and thus the issue was not seen before. Also, I did not see errors in the MiaB status page, which thus also hides the issue somewhat.

Update: when testing this, an easy way to disable wal is sqlite3 users.sqlite 'PRAGMA journal_mode=delete;';

Another update: I think the reason we're seeing this only now, is because Roundcube only introduced the PRAGMA journal_mode=WAL line in version 1.6.0 of Roundcube. It was not part of Roundcube 1.5.3.

[ddavness]

Ah. Suddenly everything begins to make sense.

Update: when testing this, an easy way to disable wal is sqlite3 users.sqlite 'PRAGMA journal_mode=delete;'

I've came to the same conclusion, but this is probably not very practical or secure to automate via php. That leaves us with using the Mail-in-a-Box Driver somebody had already implemented for us. Unfortunately, the system password changes every time the admin panel is restarted, which doesn't make it a fast solution to implement right away, either.

[e-fu]

Both are indeed related. I want to keep an eye on how they'll eventually solve it.

This issue was caused due to the fix I applied on #77, though in all honesty is still a bit mysterious to me how it ended up happening: since that behavior goes back to Roundcube 1.4.x, I'd expect it to come up sooner.

Strangely enough nobody else reported this problem to me so far. I wonder if it actually happened to somebody else or if it was a spurious episode on my case.

i have this

God help me, this took a big, big while to figure out

So a technical TL;DR on this: SQLite has a few logging modes, among them wal (Write-Ahead-Log) and delete (aka none).

1. Postfix can only work with the users.sqlite database when it is set to delete.
2. But when changing the password on Roundcube, it will set the database to wal.
3. Chaos ensues with errors like these:

postfix/trivial-rewrite[22530]: fatal: dict_sqlite_lookup: /etc/postfix/virtual-alias-maps.cf: SQL prepare failed: disk I/O error?

(which are as helpful as a submarine in the desert) 4. Postfix will never be able to process incoming mail because they cannot read the database the way the want to.

j have the same problem. cant receive any emails anymore

[kiekerjan]

@e-fu go to the folder where your users.sqlite database is stored and run the following command:
sudo sqlite3 users.sqlite 'PRAGMA journal_mode=delete;';
That should restore the database for postfix.

[ddavness]

I have done some work on this and reached to a solution that more or less is able to please everyone-ish (see the PR above). It will have some drawbacks though; it's a good idea to take a look at it and share your thoughts :)

[ddavness]

Fixed on v60.4

[codihaus]

I'm not really using MiaB, but facing the same problem which my mail server is on Postfix (the cf refer to .db file), and the roundcube allow change password.

When user come to roundcube UI and change password, I can see it created -wal file and -shm file. Then postfix crashed.

Can you please suggest me a way to resolve this please?