Usage of dnsmasq's `--user` option does not chown db-shm and db-wal files on first launch

jacklul · May 18, 2024, 9:35am

When launching FTL with pihole-FTL -- -u pihole for the first time the database is created with correct ownership of pihole user but -shm and -wal files are left owned by root - this makes writes to the database fail.
Now if you kill the process and start it again the same way those files have their ownership corrected and everything is working.

From what I can tell the database is being created as root then it's ownership is changed by chown() call in dnsmasq_interface.c:2939, forgetting about -shm and -wal files.
Perhaps changing ownership of those files should also happen around mentioned code in dnsmasq_interface.c ?
On next launch those files have their ownership corrected by sqlite code (sqlite.c:42800 and sqlite.c:44453).

I discovered this while working on a project to make v6 run directly on a router where setcap is not supported and using --user is the only way to switch to a different user.
To make sure it's not an 'error' specific to my router setup I re-tested in Debian virtual machine - log below.

CLICK FOR LOG

root@debian:/# bash /opt/pihole/pihole-FTL-prestart.sh && pihole-FTL -- -u pihole
FTL started!

root@debian:/# ls -al /etc/pihole/pihole-FTL.db*
-rw-r-----  1 pihole pihole   86016 May 18 10:29 pihole-FTL.db
-rw-r-----  1 root   root     32768 May 18 10:29 pihole-FTL.db-shm
-rw-r-----  1 root   root      8272 May 18 10:29 pihole-FTL.db-wal

root@debian:/# tail /var/log/pihole/FTL.log -n 100
2024-05-18 10:29:52.087 [14681M] INFO: ########## FTL started on debian! ##########
2024-05-18 10:29:52.087 [14681M] INFO: FTL branch: development-v6
2024-05-18 10:29:52.087 [14681M] INFO: FTL version: vDev-a078106
2024-05-18 10:29:52.087 [14681M] INFO: FTL commit: a0781069
2024-05-18 10:29:52.087 [14681M] INFO: FTL date: 2024-05-18 09:44:22 +0200
2024-05-18 10:29:52.087 [14681M] INFO: FTL user: root
2024-05-18 10:29:52.087 [14681M] INFO: Compiled for linux/amd64 (compiled on CI) using cc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924
2024-05-18 10:29:52.089 [14681M] INFO: Wrote config file:
2024-05-18 10:29:52.089 [14681M] INFO:  - 135 total entries
2024-05-18 10:29:52.089 [14681M] INFO:  - 130 entries are default
2024-05-18 10:29:52.089 [14681M] INFO:  - 5 entries are modified
2024-05-18 10:29:52.089 [14681M] INFO:  - 0 entries are forced through environment
2024-05-18 10:29:52.090 [14681M] INFO: Parsed config file /etc/pihole/pihole.toml successfully
2024-05-18 10:29:52.090 [14681M] WARNING: Starting pihole-FTL as user root is not recommended
2024-05-18 10:29:52.090 [14681M] INFO: PID of FTL process: 14681
2024-05-18 10:29:52.090 [14681M] INFO: listening on 0.0.0.0 port 53
2024-05-18 10:29:52.091 [14681M] INFO: listening on :: port 53
2024-05-18 10:29:52.091 [14683M] INFO: PID of FTL process: 14683
2024-05-18 10:29:52.092 [14683M] WARNING: No database file found, creating new (empty) database
2024-05-18 10:29:52.137 [14683M] INFO: Database version is 1
2024-05-18 10:29:52.137 [14683M] INFO: Updating long-term database to version 2
2024-05-18 10:29:52.145 [14683M] INFO: Updating long-term database to version 3
2024-05-18 10:29:52.153 [14683M] INFO: Updating long-term database to version 4
2024-05-18 10:29:52.160 [14683M] INFO: Updating long-term database to version 5
2024-05-18 10:29:52.170 [14683M] INFO: Updating long-term database to version 6
2024-05-18 10:29:52.176 [14683M] INFO: Updating long-term database to version 7
2024-05-18 10:29:52.181 [14683M] INFO: Updating long-term database to version 8
2024-05-18 10:29:52.191 [14683M] INFO: Updating long-term database to version 9
2024-05-18 10:29:52.198 [14683M] INFO: Updating long-term database to version 10
2024-05-18 10:29:52.210 [14683M] INFO: Updating long-term database to version 11
2024-05-18 10:29:52.219 [14683M] INFO: Updating long-term database to version 12
2024-05-18 10:29:52.225 [14683M] INFO: Updating long-term database to version 13
2024-05-18 10:29:52.232 [14683M] INFO: Updating long-term database to version 14
2024-05-18 10:29:52.237 [14683M] INFO: Updating long-term database to version 15
2024-05-18 10:29:52.247 [14683M] INFO: Updating long-term database to version 16
2024-05-18 10:29:52.253 [14683M] INFO: Updating long-term database to version 17
2024-05-18 10:29:52.262 [14683M] INFO: Database successfully initialized
2024-05-18 10:29:52.281 [14683M] INFO: Imported 0 queries from the on-disk database (it has 0 rows)
2024-05-18 10:29:52.281 [14683M] INFO: Parsing queries in database
2024-05-18 10:29:52.281 [14683M] INFO: Imported 0 queries from the long-term database
2024-05-18 10:29:52.281 [14683M] INFO:  -> Total DNS queries: 0
2024-05-18 10:29:52.281 [14683M] INFO:  -> Cached DNS queries: 0
2024-05-18 10:29:52.281 [14683M] INFO:  -> Forwarded DNS queries: 0
2024-05-18 10:29:52.281 [14683M] INFO:  -> Blocked DNS queries: 0
2024-05-18 10:29:52.282 [14683M] INFO:  -> Unknown DNS queries: 0
2024-05-18 10:29:52.282 [14683M] INFO:  -> Unique domains: 0
2024-05-18 10:29:52.282 [14683M] INFO:  -> Unique clients: 0
2024-05-18 10:29:52.282 [14683M] INFO:  -> DNS cache records: 0
2024-05-18 10:29:52.282 [14683M] INFO:  -> Known forward destinations: 0
2024-05-18 10:29:52.282 [14683M] INFO: FTL is going to drop from root to user pihole (UID 998)
2024-05-18 10:29:52.282 [14683M] INFO: Reading certificate from /etc/pihole/tls.pem ...
2024-05-18 10:29:52.282 [14683M] INFO: Using SSL/TLS certificate file /etc/pihole/tls.pem
2024-05-18 10:29:52.283 [14683M] INFO: Restored 0 API sessions from the database
2024-05-18 10:29:52.301 [14683M] INFO: Blocking status is enabled
2024-05-18 10:29:52.403 [14683/T14685] INFO: Compiled 0 allow and 0 deny regex for 0 client in 0.1 msec
2024-05-18 10:30:00.086 [14683/T14685] ERROR: SQLite3: statement aborts at 50: [DELETE FROM query_storage WHERE id IN (SELECT id FROM query_storage WHERE timestamp <= 1708158600 LIMIT (SELECT COUNT(*)/100 FROM query_storage));] attempt to write a readonly database (8)
2024-05-18 10:30:00.086 [14683/T14685] ERROR: ERROR: SQL query "DELETE FROM query_storage WHERE id IN (SELECT id FROM query_storage WHERE timestamp <= 1708158600 LIMIT (SELECT COUNT(*)/100 FROM query_storage));" failed: attempt to write a readonly database (SQLITE_READONLY)
2024-05-18 10:30:00.086 [14683/T14685] WARNING: Database /etc/pihole/pihole-FTL.db is read-only and cannot be used.
2024-05-18 10:30:00.086 [14683/T14685] ERROR: delete_old_queries_in_DB() failed!
2024-05-18 10:30:00.086 [14683/T14685] WARNING: ERROR: Storing devices in network table ("BEGIN TRANSACTION IMMEDIATE") failed

root@debian:/# killall -9 pihole-FTL && bash /opt/pihole/pihole-FTL-poststop.sh
root@debian:/# bash /opt/pihole/pihole-FTL-prestart.sh && pihole-FTL -- -u pihole
FTL started!

root@debian:/# ls -al /etc/pihole/pihole-FTL.db*
-rw-r-----  1 pihole pihole   86016 May 18 10:44 pihole-FTL.db
-rw-r-----  1 pihole pihole   32768 May 18 10:44 pihole-FTL.db-shm
-rw-r-----  1 pihole pihole    8272 May 18 10:44 pihole-FTL.db-wal

Originally I wanted to report this directly to the FTL repo but I an not launching FTL using official method so it doesn't have to be considered a bug.

DL6ER · May 18, 2024, 11:13am

Yes, amending the ownership change of these files seems to have been forgotten when switching to WAL journaling mode. I will come back to you once we had time to work on a fix for this, thank you very much for the report!

DL6ER · May 18, 2024, 3:33pm

github.com/pi-hole/FTL

Ensure we also change ownership of the WAL database files

pi-hole:development-v6 ← pi-hole:fix/wal-ownership

opened 03:33PM - 18 May 24 UTC

DL6ER

+35 -3

# What does this implement/fix? If FTL is being invoked in an unusual way, it… can happen that the database is created as `root` and later re-owned to a low-privilege user. Since the default database mode is now WAL, we have additional `-shm` and `-wal` files which need to be re-owned as well or the database becomes read-only. This permission issue "auto-fixes" on the next FTL restart and everything will be fine thereafter. Nonetheless, this PR ensures the permissions are already correct during the initial start and not only after the first restart. --- **Related issue or feature (if applicable):** See linked Discourse thread below. **Pull request in [docs](https://github.com/pi-hole/docs) with documentation (if applicable):** N/A --- **By submitting this pull request, I confirm the following:** 1. I have read and understood the [contributors guide](https://docs.pi-hole.net/guides/github/contributing/), as well as this entire template. I understand which branch to base my commits and Pull Requests against. 2. I have commented my proposed changes within the code. 3. I am willing to help maintain this change if there are issues with it later. 4. It is compatible with the [EUPL 1.2 license](https://opensource.org/licenses/EUPL-1.1) 5. I have squashed any insignificant commits. ([`git rebase`](http://gitready.com/advanced/2009/02/10/squashing-commits-with-rebase.html)) ## Checklist: - [x] The code change is tested and works locally. - [x] I based my code and PRs against the repositories `developmental` branch. - [x] I [signed off](https://docs.pi-hole.net/guides/github/how-to-signoff/) all commits. Pi-hole enforces the [DCO](https://docs.pi-hole.net/guides/github/dco/) for all contributions - [x] I [signed](https://docs.github.com/en/authentication/managing-commit-signature-verification/signing-commits) all my commits. Pi-hole requires signatures to verify authorship - [x] I have read the above and my PR is ready for review.

jacklul · May 18, 2024, 4:01pm

It works!

root@debian:/# rm -fr /etc/pihole/pihole-FTL.db*
root@debian:/# ls -al /etc/pihole/pihole-FTL.db*
ls: cannot access '/etc/pihole/pihole-FTL.db*': No such file or directory
root@debian:/# killall -9 pihole-FTL && bash /opt/pihole/pihole-FTL-poststop.sh
root@debian:/# bash /opt/pihole/pihole-FTL-prestart.sh && pihole-FTL -- -u pihole
root@debian:/# ls -al /etc/pihole/pihole-FTL.db*
-rw-rw---- 1 pihole pihole 86016 May 18 17:57 /etc/pihole/pihole-FTL.db
-rw-rw---- 1 pihole pihole 32768 May 18 17:57 /etc/pihole/pihole-FTL.db-shm
-rw-rw---- 1 pihole pihole  8272 May 18 17:57 /etc/pihole/pihole-FTL.db-wal

jacklul · May 20, 2024, 9:24am

I've tested it on my router setup and it seems to not work, ~~but have no idea why~~... (log has no failure entries)
Perhaps because the code is in if clause that gets executed when chown on main DB fails?
But then why did it work in the VM? I will have to re-check this in VM I guess.

Edit: I quickly set up a VM at work and re-tested and it indeed doesn't work now.
I see my potential mistake - I deleted the database then stopped pihole-FTL - perhaps the database was recreated in the meantime ?

DL6ER · May 20, 2024, 5:37pm

Oh yes, it indeed makes more sense to always try to chown. I guess we all have been carried away from your enthusiastic confirmation

github.com/pi-hole/FTL

Fix chown of aux. database files

pi-hole:development-v6 ← pi-hole:fix/database_aux_files

opened 05:37PM - 20 May 24 UTC

DL6ER

+36 -27

# What does this implement/fix? Always try to `chown` auxiliary database file…s, not only when `chown` of the database files itself failed. I simplified and re-arranged the code to reduce the nesting depth by up to two levels, making such issues easier to detect visually. --- **Related issue or feature (if applicable):** N/A **Pull request in [docs](https://github.com/pi-hole/docs) with documentation (if applicable):** N/A --- **By submitting this pull request, I confirm the following:** 1. I have read and understood the [contributors guide](https://docs.pi-hole.net/guides/github/contributing/), as well as this entire template. I understand which branch to base my commits and Pull Requests against. 2. I have commented my proposed changes within the code. 3. I am willing to help maintain this change if there are issues with it later. 4. It is compatible with the [EUPL 1.2 license](https://opensource.org/licenses/EUPL-1.1) 5. I have squashed any insignificant commits. ([`git rebase`](http://gitready.com/advanced/2009/02/10/squashing-commits-with-rebase.html)) ## Checklist: - [x] The code change is tested and works locally. - [x] I based my code and PRs against the repositories `developmental` branch. - [x] I [signed off](https://docs.pi-hole.net/guides/github/how-to-signoff/) all commits. Pi-hole enforces the [DCO](https://docs.pi-hole.net/guides/github/dco/) for all contributions - [x] I [signed](https://docs.github.com/en/authentication/managing-commit-signature-verification/signing-commits) all my commits. Pi-hole requires signatures to verify authorship - [x] I have read the above and my PR is ready for review.

jacklul · May 20, 2024, 8:02pm

I compiled it for my device this time and it seems to be working
Seems to be working in the previous VM as well, tested it twice.

Testing log

admin@RT-AX58U:/opt/etc/pihole# pihole-FTL branch
fix/database_aux_files
admin@RT-AX58U:/opt/etc/pihole# la
drwxr-xr-x    5 admin    root         500 May 20 21:58 .
drwxr-xr-x   15 admin    root         740 May 20 11:04 ..
-rw-r--r--    1 admin    root          65 May 20 11:04 adlists.list
-rw-r--r--    1 admin    root          65 May 19 11:21 adlists.list.bak_1716179613
drwxrwx---    2 admin    root          60 May 20 06:33 config_backups
-rw-r--r--    1 admin    root         651 May 20 11:04 dns-servers.conf
-rw-rw-rw-    1 admin    root        5.3K May 20 06:33 dnsmasq.conf
-rw-rw-r--    1 pihole   pihole      7.7M May 20 06:33 gravity.db
-rw-rw-r--    1 pihole   pihole    100.0K May 20 06:33 gravity_old.db
drwxr-xr-x    2 admin    root          60 May 20 06:33 hosts
-rw-r--r--    1 admin    root        2.7M May 20 06:33 list.1.raw.githubusercontent.com.domains
-rw-rw-rw-    1 admin    root          99 May 20 06:33 list.1.raw.githubusercontent.com.domains.sha1
-rw-r--r--    1 admin    root        5.1K May 20 06:33 list.2.raw.githubusercontent.com.domains
-rw-rw-rw-    1 admin    root          99 May 20 06:33 list.2.raw.githubusercontent.com.domains.sha1
-rw-r--r--    1 admin    root         382 May 20 11:08 logrotate
-rw-r--r--    1 admin    root        3.0M May 20 11:04 macvendor.db
drwxrwxrwx    2 admin    root          60 May 20 06:33 migration_backup
-rw-r--r--    1 admin    root         112 May 20 11:04 pihole-FTL.conf
-rw-rw-rw-    1 pihole   pihole     46.3K May 20 06:33 pihole.toml
-rw-r--r--    1 admin    root         254 May 20 11:04 pihole.toml-opkg
-rw-r--r--    1 admin    root         254 May 19 11:21 pihole.toml.bak_1716179613
-rw-rw-rw-    1 admin    root         737 May 20 06:33 tls.crt
-rw-rw-rw-    1 admin    root        1.0K May 20 06:33 tls.pem
-rw-rw-rw-    1 admin    root         733 May 20 06:33 tls_ca.crt
-rw-r--r--    1 admin    root         195 May 20 11:04 versions
admin@RT-AX58U:/opt/etc/pihole# eservice pihole-FTL start
Failed to set capabilities on file '/opt/sbin/pihole-FTL': Operation not supported
Starting pihole-FTL as 'admin' (then dropping to 'pihole') because setting capabilities is not supported on this system
 Starting pihole-FTL...              done.
admin@RT-AX58U:/opt/etc/pihole# la pihole-FTL.db*
-rw-r-----    1 pihole   pihole     84.0K May 20 21:58 pihole-FTL.db
-rw-r-----    1 pihole   pihole     32.0K May 20 21:58 pihole-FTL.db-shm
-rw-r-----    1 pihole   pihole      8.1K May 20 21:58 pihole-FTL.db-wal

Must say development-v6 works very well on a router now that lighttpd dependency is no more.