SQLite Forum

Cant write from root user into sqlite when sticky bit is set
Login

Cant write from root user into sqlite when sticky bit is set

(1) By ibse (777777) on 2021-04-16 10:15:40 [link] [source]

OS Ubuntu

I have a sqlite data base in the folder with a sticky bit in permissions. The folder is owned by root. The db file is owned by no root user and has full permissions:

ibse@ibse-VirtualBox:/var/internal$ ll
total 32
drwxrwxrwt 2 root root  4096 кві 16 12:00 ./
drwxr-xr-x 3 root root  4096 кві 16 10:47 ../
-rwxrwxrwx 1 ibse ibse 21504 кві 16 12:00 mydb.dat*

When I try to INSERT into the database from the root user, the database rejects with the error "attempt to write a readonly database":


ibse@ibse-VirtualBox:/var/internal$ sudo sqlite3 mydb.dat

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> INSERT INTO DATA (sent) VALUES (1);
Error: attempt to write a readonly database
sqlite>

But when I remove the sticky bit from the folder containing the database file, I can INSERT into one:


ibse@ibse-VirtualBox:/var/internal$ sudo chmod -t .
ibse@ibse-VirtualBox:/var/internal$ sudo sqlite3 mydb.dat

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> INSERT INTO DATA (sent) VALUES (1);
sqlite>

Why it so? If I right understand the sticky bit concept, the root user can manage all the files in the folder, even if sticky is set. 

Does sqlite handle such cases in some special way?

(2) By Ryan Smith (cuz) on 2021-04-16 12:11:21 in reply to 1 [source]

If I right understand the sticky bit concept, the root user can manage all the files in the folder, even if sticky is set.

Not "manage", just "remove" and "rename" - these are the only two things controlled by the sticky bit.

I don't know the answer, but if I could venture a guess:

Sticky bits allow only the owner (or root) of contained files to rename or delete them. This may be achieved by not allowing certain file locks, which SQLite need to hold - even as root.

I suppose a test would be easy to devise, but not making the effort just yet because the SQLite devs may already know why and simply reply with the reason soon. If it ends up being unknown or confusing, will test it.

Why do you need to edit files as root (as opposed to the owner, which always have all access)? I mean if you are posting this as a problem, it seems you intend to do this often, not just once to manage something quick.

(3) By Simon Slavin (slavin) on 2021-04-16 12:51:15 in reply to 1 [link] [source]

[following explanation is simplified]

In the course of making changes to a database file, SQLite creates a temporary file – either a write-ahead file or a rollback journal – in the same folder. This holds the data about to be written, or the data about to be deleted. Having this file present allows SQLite to recover from a crash or power-failure by inspecting both versions of the data and figuring out what happened.

So for SQLite to work normally, it not only needs r/w access to the database file but creation privs for the folder that that file is in. You can change this behaviour using

PRAGMA journal_mode = OFF

this means that SQLite doesn't use a temporary file, but it also means that a crash can corrupt the database or lose changes, and that the ROLLBACK command doesn't work.

Another way to get around the problem is to set

PRAGMA journal_mode = PERSIST

which means that SQLite will not continually try to create and delete the temporary file. This would solve your problem but would be more difficult to set up. You would have to trick SQLite into creating a journal file somewhere safe, then to copy this file into your secure folder with the database file.

(4) By ibse (777777) on 2021-04-16 13:41:24 in reply to 2 [link] [source]

"Why do you need to edit files as root (as opposed to the owner, which always have all access)?"

This is done by C code (sqlite api), which is run from root

(5) By Larry Brasfield (larrybr) on 2021-04-16 14:15:21 in reply to 1 [link] [source]

The SQLite library takes measures to avoid creating a journal file owned by root when the database file is not owned by root. According to comments in the code, this is to ensure that the database is not rendered unusable to non-root users should a root-owned journal be left behind, which can happen for several reasons.

I imagine that it would be possible for the library to handle the journal file in a special way, reflecting the unusual aspects of your use case. I see no code attempting to do that. I have tried, without success, to conjure motivation to argue that this use case should be supported with additional code complexity. Maybe that mixed-ownership avoidance feature could be conditionally (not) compiled.

A work-around would be for your running-as-root process to use su to assume the identity of the mydb.dat owner. I have replicated your problem and verified efficacy of this work-around on a recent-vintage (20.04) Ubuntu system.

(6) By ibse (777777) on 2021-04-18 07:33:33 in reply to 3 [link] [source]

If I understood correctly what I need to do, I get the same error:

$sudo sqlite3 mydata.dat

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> PRAGMA journal_mode = OFF;
off
sqlite> insert into memos values('deliver project description', 10);
Error: attempt to write a readonly database
sqlite>

(7) By Simon Slavin (slavin) on 2021-04-18 13:26:29 in reply to 6 [link] [source]

Okay. That surprises me. Not doubting your word, but checking just to make sure you're doing what I think you're doing.

Did mydata.dat already exist when you started the sqlite executable ?

Are you sure that the default directory, and therefore mydata.dat, is in the directory you described in the post that started this thread ?

(8) By ibse (777777) on 2021-04-19 06:55:22 in reply to 7 [link] [source]

"Are you sure that the default directory, and therefore mydata.dat, is in the directory you described in the post that started this thread ?"

No, I reproduced the problem on another machine with the same conditions (at least that's what I thought)
On the machine on which I started the thread, I get "disk I/O error":

ibse@ibse-VirtualBox:/var/internal$ sudo sqlite3 mydb.dat

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> PRAGMA journal_mode = OFF;
Error: disk I/O error
sqlite>