search

ddavness / power-mailinabox

A Mail-in-a-Box with extra capabilities and more customizability. Not just for power users!
Creative Commons Zero v1.0 Universal
168 stars 32 forks source link

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

Closed ddavness closed 1 year ago

ddavness commented 1 year ago

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 commented 1 year ago

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

kiekerjan commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

@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 commented 1 year ago

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 commented 1 year ago

Fixed on v60.4

codihaus commented 3 weeks ago

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?