munin-httpd with new sqlite database fails since 2.999.12

[sumpfralle]

Describe the bug Since munin 2.999.12 (a720a05bb6667ddaca26e9652d71b19b72d71834) the following sqlite setting is used when creating a new database:

PRAGMA journal_mode=WAL;

A database with this setting cannot be used in read-only mode. The database is in read-only mode, if munin-cron and munin-httpd are running under different user IDs (as done in the Debian packaging for experimental).

Due to the read-only access munin-httpd bails out with the following stacktrace, if any GET request is sent:

2019/08/01-00:28:05 Munin::Master::Http::NetServer (type Net::Server::PreFork) starting! pid(5971)
Resolved [*]:4948 to [::]:4948, IPv6
Not including resolved host [0.0.0.0] IPv4 because it will be handled by [::] IPv6
Binding to TCP port 4948 on host :: with IPv6
Group Not Defined.  Defaulting to EGID '109 109'
User Not Defined.  Defaulting to EUID '108'
DBD::SQLite::db prepare_cached failed: attempt to write a readonly database at /usr/share/perl5/Munin/Master/Update.pm line 80.
        Munin::Master::Update::__ANON__("DBD::SQLite::db prepare_cached failed: attempt to write a rea"..., DBI::db=HASH(0x55e3ab136010), undef) called at /usr/share/perl5/Munin/Master/HTML.pm line 133
        Munin::Master::HTML::handle_request(CGI=HASH(0x55e3aae1f1d8)) called at /usr/bin/munin-httpd line 29
        Munin::Master::Http::handle_request(Munin::Master::Http=HASH(0x55e3a8b07290), CGI=HASH(0x55e3aae1f1d8)) called at /usr/share/perl5/HTTP/Server/Simple/CGI.pm line 153
        eval {...} called at /usr/share/perl5/HTTP/Server/Simple/CGI.pm line 153
        HTTP::Server::Simple::CGI::handler(Munin::Master::Http=HASH(0x55e3a8b07290)) called at /usr/share/perl5/HTTP/Server/Simple/CGI/PreFork.pm line 271
        HTTP::Server::Simple::CGI::PreFork::__ANON__(Munin::Master::Http::NetServer=HASH(0x55e3aab17ec0), Net::Server::Proto::TCP=GLOB(0x55e3aae0b2f0)) called at /usr/share/perl5/Net/Server.pm line 73
        Net::Server::run_client_connection(Munin::Master::Http::NetServer=HASH(0x55e3aab17ec0)) called at /usr/share/perl5/Net/Server/PreFork.pm line 225
        eval {...} called at /usr/share/perl5/Net/Server/PreFork.pm line 225
        Net::Server::PreFork::run_child(Munin::Master::Http::NetServer=HASH(0x55e3aab17ec0)) called at /usr/share/perl5/Net/Server/PreFork.pm line 183
        Net::Server::PreFork::run_n_children(Munin::Master::Http::NetServer=HASH(0x55e3aab17ec0), 5) called at /usr/share/perl5/Net/Server/PreFork.pm line 107
        Net::Server::PreFork::loop(Munin::Master::Http::NetServer=HASH(0x55e3aab17ec0)) called at /usr/share/perl5/Net/Server.pm line 58
        Net::Server::run("Munin::Master::Http::NetServer", "port", HASH(0x55e3a8ae4458), Munin::Master::Http=HASH(0x55e3a8b07290), "prefork", 1, "max_servers", 10) called at /usr/share/perl5/HTTP/Server/Simple/CGI/PreFork.pm line 343
        HTTP::Server::Simple::CGI::PreFork::run(Munin::Master::Http=HASH(0x55e3a8b07290), "prefork", 1, "max_servers", 10) called at /usr/bin/munin-httpd line 52

To Reproduce

rm /var/lib/munin/datafile.sqlite
su -s /bin/sh -c munin-cron munin
curl http://localhost:4948/

This results in an empty reply from munin-httpd.

This issue can be worked around by disabling the journal mode in sqlite:

echo "PRAGMA journal_mode=delete;" | sqlite3 /var/lib/munin/datafile.sqlite

Now the same curl requests returns a valid response.

[sumpfralle]

I see the following options:

• A) reset the journal mode to its default (probably this won't help, since d7d5c204f59e9e98bf599565b9b459fec3ded9d2 introduced write operations of munin-httpd into the database)
• B) relax permissions for the database file (e.g. group write permission)
• C) use the same user for running munin-cron and munin-httpd

I could imagine, that "B" is the most suitable option, since it mimics the permission model applicable when using a postgresql database (role-based permissions) instead of an sqlite database (user-based permissions) with munin.

[steveschnepp]

Well, having munin-httpd being read-only is a feature. And the fact that it doesn't with a postgresql is the bug. So "B" isn't a reasonable option.

So in postgresql we should connect with 2 different users, to have the same behavior than sqlite.

munin-httpd is opened to the external world. So I'd really like it to be r/o. If it needs to write things, let's setup some dedicated & controlled channel for it.

And, yes, that means that I was wrong & too lazy in d7d5c204f59e9e98bf599565b9b459fec3ded9d2.

[sumpfralle]

Regarding read-only access to sqlite with WAL:

• the query argument immutable could help to work around this limitation: sqlite3 "file://$(pwd)/foo.sqlite?immutable=1"
  • But the documentation says it could cause problems, if the file changes underneath munin-httpd (as sqlite3 assumes not just "read-only for myself", but "cannot be changed at all"). Thus it is probably not an option.
• persistent WAL: the WAL documentation mentions in section 5 the different options for using WAL with a read-only database. Persistent *-wal and *-shm files are the only viable option, I think. This feels a bit fragile / subtle to me, but it could be a solution.

[steveschnepp]

Since the SQLite doc states

Even though it is possible to open a read-only WAL-mode database, it is good practice to converted to PRAGMA journal_mode=DELETE prior to burning an SQLite database image onto read-only media.

We'll just go with PRAGMA journal_mode=delete, as if one really needs performance he should move towards PostgreSQL.

The stats bothers me a little more.

[sumpfralle]

We'll just go with PRAGMA journal_mode=delete, as if one really needs performance he should move towards PostgreSQL.

Sounds good to me.

The stats bothers me a little more.

Did you have a specific use-case in mind? How about requesting the stats via a munin plugin (based on data from an HTTP request)? Or is the munin-httpd process state-less?

[vanyabrovary]

I fixed it by replacing User=munin-httpd on the User=munin in the file /etc/systemd/system/multi-user.target.wants/munin-httpd.service. Then performed systemctl daemon-reload /etc/init.d/munin-httpd restart