fatal: dict_sqlite_lookup: /etc/postfix/sqlite/local-aliases.cf: SQL prepare failed: disk I/O error?

[deoren]

Alternate title:

Postfix + SQLite lookup tables shared with external applications in
Write-Ahead Logging (WAL) mode

Hi,

Ultimately, the fix appears to be to set the owner of the containing
directory and the SQLite database files to "postfix". What I don't
understand is _why_ this is the fix. I expect it is likely something
very basic that I am overlooking.

Thanks in advance for your help.

In short, I took a working Postfix configuration that uses MySQL for its
lookup tables and updated it so that it uses SQLite instead. All lookups
work without issue except for SQLite lookups via the local daemon for
the "alias_maps" setting, unless I set the "postfix" user account as
owner of the directory/files.

Environment:

Ubuntu 18.04/Postfix 3.3.0 with a configuration originally written for
Ubuntu 12.04/Postfix 2.9.6 and migrated forward through the various
Ubuntu LTS releases. This installation serves as a backend mail relay
for a load-balanced setup. Currently I have this node out of that
backend pool for reconfiguration/testing work.


The SQLite database is stored in a /var/cache/mysql2sqlite directory and
is maintained by an external tool called by a cron job.

The /var/cache/mysql2sqlite directory has 2770 permissions (or whatever
rwxrws is if I I got that wrong). The files within have
mysql2sqlite:mysql2sqlite owner:group and permissions of 660.

The cron job sets a umask of 002 when run as the mysql2sqlite user so
that group retains write permission.

The postfix user account was added to the mysql2sqlite group.

I know it isn't the same, but I've gained a shell as the postfix user
account and can query the SQLite database without issue. I have
confirmed that it has permission to enable Write-Ahead Logging via
"pragma journal_type=wal;" and watched as it created the necessary files
within /var/cache/mysql2sqlite associated with the Write-Ahead Logging
journal.

All of the proxy:sqlite:/etc/postfix/sqlite/*.cf files are accessible by
Postfix and I can run lookups via postalias using those files and get
valid results back.

The mail log records valid lookups for other Postfix lookup table access
attempts (e.g., mail relay), but the local daemon consistently fails
with this error:

postfix/local[125468]: fatal: dict_sqlite_lookup:
/etc/postfix/sqlite/local-aliases.cf: SQL prepare failed: disk I/O error?

Once I change the owner of the directory and files to "postfix", the
lookup succeeds and the mail queue clears.

Is the lookup daemon restricted from accessing the SQLite database
file/directory via its membership in a group that has write access to
the directory and all files within?

The /etc/postfix/master.cf entry has a "n" for the chroot setting, and
once the ownership is changed to the "postfix" user account it can
access the SQLite database, so this doesn't appear to be a case of
chroot blocking access.

I also didn't spot any active AppArmor profiles (this is an Ubuntu
system), so that also doesn't appear to be the problem.

[Wietse Venema]

deoren:

> Alternate title:
>
> Postfix + SQLite lookup tables shared with external applications in
> Write-Ahead Logging (WAL) mode
>
> Hi,
>
> Ultimately, the fix appears to be to set the owner of the containing
> directory and the SQLite database files to "postfix". What I don't
> understand is _why_ this is the fix. I expect it is likely something
> very basic that I am overlooking.
>
> Thanks in advance for your help.
>
> In short, I took a working Postfix configuration that uses MySQL for its
> lookup tables and updated it so that it uses SQLite instead. All lookups
> work without issue except for SQLite lookups via the local daemon for
> the "alias_maps" setting, unless I set the "postfix" user account as
> owner of the directory/files.

Postfix local(8) opens the sqlite map while it still runs as root,
and that file handle should work after the process changes privileges.
I suspect that something is interfering with UNIX permissions model,
maybe selinux or apparmor.

        Wietse

[deoren]

On 9/24/2020 12:43 PM, Wietse Venema wrote:

> deoren:
>> Alternate title:
>>
>> Postfix + SQLite lookup tables shared with external applications in
>> Write-Ahead Logging (WAL) mode
>>
>> Hi,
>>
>> Ultimately, the fix appears to be to set the owner of the containing
>> directory and the SQLite database files to "postfix". What I don't
>> understand is _why_ this is the fix. I expect it is likely something
>> very basic that I am overlooking.
>>
>> Thanks in advance for your help.
>>
>> In short, I took a working Postfix configuration that uses MySQL for its
>> lookup tables and updated it so that it uses SQLite instead. All lookups
>> work without issue except for SQLite lookups via the local daemon for
>> the "alias_maps" setting, unless I set the "postfix" user account as
>> owner of the directory/files.
>
> Postfix local(8) opens the sqlite map while it still runs as root,
> and that file handle should work after the process changes privileges.
> I suspect that something is interfering with UNIX permissions model,
> maybe selinux or apparmor.
>
> Wietse
>

Thanks for the feedback, I appreciate your time!.

I'll dig more in that direction. It's quite likely that I overlooked
something in the apparmor setup when I checked earlier.

[Viktor Dukhovni]

On Thu, Sep 24, 2020 at 11:50:19AM -0500, deoren wrote:

> Postfix + SQLite lookup tables shared with external applications in
> Write-Ahead Logging (WAL) mode

SQLite with WAL mode is a multi-file database, in which some of the
files are opened and closed as you go.  Postfix does not support this.
You can use single-file SQLite databases, but not WAL.  If you want
a real database, you need Postgres or MySQL.

> Ultimately, the fix appears to be to set the owner of the containing
> directory and the SQLite database files to "postfix". What I don't
> understand is _why_ this is the fix. I expect it is likely something
> very basic that I am overlooking.

As Wietse notes, Postfix drops privileges after opening tables, but
this does not work with SQLite in WAL mode.

On Thu, Sep 24, 2020 at 01:43:05PM -0400, Wietse Venema wrote:

> Postfix local(8) opens the sqlite map while it still runs as root,
> and that file handle should work after the process changes privileges.
> I suspect that something is interfering with UNIX permissions model,
> maybe selinux or apparmor.

No, just WAL mode means that we haven't opened all the files that
might later be needed.

--
    Viktor.

[deoren]

On 9/24/2020 5:16 PM, Viktor Dukhovni wrote:
> On Thu, Sep 24, 2020 at 11:50:19AM -0500, deoren wrote:
>
>> Postfix + SQLite lookup tables shared with external applications in
>> Write-Ahead Logging (WAL) mode
>
> SQLite with WAL mode is a multi-file database, in which some of the
> files are opened and closed as you go.  Postfix does not support this.
> You can use single-file SQLite databases, but not WAL.  If you want
> a real database, you need Postgres or MySQL.

Thank you. I'm new to SQLite (specifically WAL), but I observed just
what you're describing here. I had a Nagios check running under the
Nagios user account, Postfix under its usual account and a cron job
which regenerates the database under another. I was relying on
group-based permissions (all of those user accounts are part of the same
group) to share access with mixed results. As I updated the owner of the
files to reflect "postfix" and changed all steps to run as "postfix",
all visible problems appeared to go away. This was a short time after my
last email to the list.

>> Ultimately, the fix appears to be to set the owner of the containing
>> directory and the SQLite database files to "postfix". What I don't
>> understand is _why_ this is the fix. I expect it is likely something
>> very basic that I am overlooking.
>
> As Wietse notes, Postfix drops privileges after opening tables, but
> this does not work with SQLite in WAL mode.
>
> On Thu, Sep 24, 2020 at 01:43:05PM -0400, Wietse Venema wrote:
>
>> Postfix local(8) opens the sqlite map while it still runs as root,
>> and that file handle should work after the process changes privileges.
>> I suspect that something is interfering with UNIX permissions model,
>> maybe selinux or apparmor.
>
> No, just WAL mode means that we haven't opened all the files that
> might later be needed.

If I run all tasks under the postfix user account, how likely am I to
run into issues? Thus far it seems to be working, but I've yet to go a
full 6 hours, much less 24 hours.

Thank you for your help.

[Viktor Dukhovni]

On Thu, Sep 24, 2020 at 05:34:15PM -0500, deoren wrote:

> > No, just WAL mode means that we haven't opened all the files that
> > might later be needed.
>
> If I run all tasks under the postfix user account, how likely am I to
> run into issues? Thus far it seems to be working, but I've yet to go a
> full 6 hours, much less 24 hours.

Well, you'd be running an unsupported configuration, so how adventurous
do you feel?  If the WAL file is ever deleted, it might come back owned
by the wrong user, ... or some other unexpected permission issue.

When Postfix is accessing indexed files directly, they should be
read-only.  For concurrent read-write, use a database.

SQLite is an embedded datastore for applications, where typically all
the reads and writes are handled by some component of that application.
For real multi-application concurrency, use a database designed for
that.

--
    Viktor.

[deoren]

On 9/24/2020 8:10 PM, Viktor Dukhovni wrote:

> On Thu, Sep 24, 2020 at 05:34:15PM -0500, deoren wrote:
>
>>> No, just WAL mode means that we haven't opened all the files that
>>> might later be needed.
>>
>> If I run all tasks under the postfix user account, how likely am I to
>> run into issues? Thus far it seems to be working, but I've yet to go a
>> full 6 hours, much less 24 hours.
>
> Well, you'd be running an unsupported configuration, so how adventurous
> do you feel?  If the WAL file is ever deleted, it might come back owned
> by the wrong user, ... or some other unexpected permission issue.
>
> When Postfix is accessing indexed files directly, they should be
> read-only.  For concurrent read-write, use a database.
>
> SQLite is an embedded datastore for applications, where typically all
> the reads and writes are handled by some component of that application.
> For real multi-application concurrency, use a database designed for
> that.
>

Fair enough. Thanks for the explanation, I see where using WAL mode
could end up biting us in this setup. I'll shelve using WAL mode for now
and go back to using the standard DELETE mode.

By using WAL mode I was hoping to avoid this scenario:

"SQL prepare failed: database is locked"

described on the "Postfix with sqlite - Database becomes locked" thread
from this mailing list.

I ran into that a few error a few times with earlier testing before I
enabled WAL mode. I've since implemented transaction support in the app
responsible for regenerating the SQLite db file, so hopefully the
"database is locked" issue will be resolved (hoping for a narrow
contention window).

That said, I saw in that mailing list thread that using this line was
recommended:

PRAGMA busy_timeout = 1000

If a query is defined like this:


### before
query =
     SELECT destination FROM local_aliases
     WHERE source ='%s'
     AND enabled = '1';


would you modify it like so?


### after
query =
     PRAGMA busy_timeout = 1000;
     SELECT destination FROM local_aliases
     WHERE source ='%s'
     AND enabled = '1';

[Wietse Venema]

deoren:
> If I run all tasks under the postfix user account, how likely am I to
> run into issues? Thus far it seems to be working, but I've yet to go a
> full 6 hours, much less 24 hours.

Don't run non-Postfx programs with Postfix privileges, that
defeats the purpose of privilege separation.

        Wietse

[deoren]

On 9/25/2020 12:27 PM, Wietse Venema wrote:
> deoren:
>> If I run all tasks under the postfix user account, how likely am I to
>> run into issues? Thus far it seems to be working, but I've yet to go a
>> full 6 hours, much less 24 hours.
>
> Don't run non-Postfx programs with Postfix privileges, that
> defeats the purpose of privilege separation.

This is good advice and normally I wouldn't do that.

During earlier testing, I noticed that (as described by Viktor) each
process interacting with the SQLite database in Write-Ahead Logging mode
would create related journal files as their own user id alongside the
SQLite database file. I wasn't concerned initially by this mix of user
ids as I was using a shared user group to permit read/write access to
the database file.

Running a SQLite db regen tool, a Nagios plugin to validate the SQLite
db and Postfix as all the same user account *seemed* to solve the issue.
Since Postfix is the intended consumer of the file, it seemed _less_
risky to use that approach.

Based on advice given (and taken to heart), the db regen tool is now
using the standard "DELETE" journaling mode when regenerating the file.
and set to retry a number of times when attempting to rebuild the SQLite
db tables (in a transaction).

I am seeing regen failures due to locked database errors (but not now
logged by Postfix), but the tool is running at a frequency that allows
it to get in a db refresh often enough to still solve the original
problem: keep the local file in sync with the remote database so that
the relay node isn't dependent on the remote database directly. The
Nagios check is also succeeding at validating the SQLite database, so at
the moment everything appears to be working.

I plan to update the configuration so that the Nagios plugin and db
regen tool are once again running as different user accounts, but
haven't gotten that far yet (this is a side project and not a main focus).

Thanks for everyone's feedback, and apologies if this is too lengthy. I
didn't give a ton of context in prior emails, so wanted to flesh this
out a bit if the earlier emails didn't make a lot of sense.

[Viktor Dukhovni]

On Fri, Sep 25, 2020 at 01:13:17PM -0500, deoren wrote:

> > Don't run non-Postfx programs with Postfix privileges, that
> > defeats the purpose of privilege separation.
>
> This is good advice and normally I wouldn't do that.

Free advice, worth every penny... :-)

DO NOT use SQLite as a Postfix backend database updated live while
Postfix is running.  Use CDB or LMDB of those work well enough,
otherwise use Postgres, MySQL or LDAP for concurrent writes and
reads.

--
    Vikor.