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.
