Version: 3.46.1

Given a database owned by another user located in a directory also owned by that other user,

$ ls -l /home/otheruser/
total 21256
drwxr-xr-x 2 otheruser users       32 Nov 11 15:58 .
dr-xr-xr-x 7 root      root        99 Nov 11 15:58 ..
-rw-r--r-- 1 otheruser users 21766144 Nov  6 15:02 dat.sqlite

it is impossible to deactivate any journalling, let alone querying the current journal mode. There are three interrelated issues.

Suboptimal error message

$ strace -fe openat sqlite3 /home/otheruser/dat.sqlite 
sqlite> select * from users;
openat(AT_FDCWD, "/home/otheruser/dat.sqlite-wal", O_RDWR|O_CREAT|O_NOFOLLOW|O_CLOEXEC, 0644) = -1 EACCES (Permission denied)
Parse error: attempt to write a readonly database (8)

Expected:

Parse error: could not start read transaction: create dat.sqlite-wal: Permission denied (8)

Can't set journal mode

sqlite> pragma journal_mode=memory;
openat(..) = EACCES

Expected:

Output memory to indicate success in changing the journal mode.

Can't query journal mode

sqlite> pragma journal_mode=memory;
openat(AT_FDCWD, "/home/otheruser/dat.sqlite-wal", O_RDWR|O_CREAT|O_NOFOLLOW|O_CLOEXEC, 0644) = -1 EACCES (Permission denied)

Expected:

Output delete or something to indicate success in querying the journal mode.

It's a little bit round-about, but perhaps a work-around is:

• sqlite3 -cmd 'pragma journal_mode=memory;' /home/otheruser/dat.sqlite

• Set pragma journal_mode, first

• Attach the database, second

Hopefully it doesn't default to an initial WAL and try to create it, and will effectively use it in a read-only fashion.

Use a URI filename.

First read www://uri.html with care.

Then try using mode=ro. If that doesn't work, try immutable=1.

Again: read the docs to understand the difference, and the risks.

Note that this behaviour is part of providing the ACID guarantees. sqlite has no server or other centralised component to ensure consistency of the database file, which means every client shares this responsibility. Specifically, every client when initially opening a database needs to be prepared to recover a hot journal in the case of an application crash/power outage which leaves the main database file in an inconsistent state, and this requires write permissions on the main database file.

Furthermore, the journal mode is a property of the database file, see https://www.sqlite.org/fileformat.html#the_database_header and the File format read/write versions. This would definitely require write permissions to change.

Finally, the absence of the dat.sqlite-wal file here is a red flag which may indicate other problematic actions are afoot, eg. deleting the -wal file or moving the main database file without its associated -wal journal. The -wal journal file is persistent and is required for database consistency -- see https://www.sqlite.org/wal.html for more details.