SQLite does not work on the document portal's filesystem

[johrpan]

Trying to open an SQLite database using libsqlite or the command line utility does fail both from within the sandbox, and from the host system. Reading from, writing to and creating files works as expected from inside and outside the sandbox. Only SQLite does not work.

The following steps can be used to replicate the issue:

1. Optionally, replace the running instance of xdg-document-portal to observe logs:

   /usr/lib/xdg-document-portal --replace --verbose
   

2. Make xdg-desktop-portal share a folder containing an SQLite database file with a sandboxed application.
3. Using the sqlite3 command, try to open this database, in my case:

   ~ $ sqlite3 /run/user/1000/doc/e1b555fb/Musik/test.db
   

SQLite fails with the non-descriptive error message:

Error: unable to open database "/run/user/1000/doc/e1b555fb/Musik/test.db": unable to open database file

The logs from xdg-document-portal contain not very much information besides error 40:

XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: ACCESS b4ee787c774c261d
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: ACCESS b4ee787c774c261d
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: OPEN b4ee787c774c261d RDONLY
XDP: GETATTR b4ee787c774c261d
XDP: GETATTR b4ee787c774c261d
XDP: GETATTR b4ee787c774c261d
XDP: GETATTR b4ee787c774c261d
XDP: FLUSH b4ee787c774c261d
XDP: RELEASE b4ee787c774c261d
XDP: GETATTR 1
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: GETATTR b0cfa07c30fc868b
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: GETATTR b4ee787c774c261d
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: OPEN b4ee787c774c261d RDWR
XDP: OPEN -> error 40
XDP: LOOKUP 5:Musik
XDP: LOOKUP 5:Musik => b0cfa07c30fc868b
XDP: LOOKUP b0cfa07c30fc868b:test.db
XDP: LOOKUP b0cfa07c30fc868b:test.db => b4ee787c774c261d
XDP: OPEN b4ee787c774c261d RDONLY
XDP: OPEN -> error 40

I also tried to get more information from libfuse by setting a custom log function using fuse_set_log_func (), but the library doesn't seem to log anything (also, logs should appear in stderr by default).

This issue is loosely related to #463, which does not apply here, because write access to the whole directory is already present. Also, there are a few mentions of the problem with other applications that need to access SQLite databases, like for example here sqlitebrowser/sqlitebrowser#3400.

[GeorgesStavracas]

Does SQLite try to access other files when creating the database?

[hfiguiere]

It has a -journal file that get created when modifying

[hfiguiere]

https://www.sqlite.org/tempfiles.html

[GeorgesStavracas]

A quick test shows that error 40 is related to too many symlink levels:

$ LC_ALL=C gjs
gjs> const {GLib} = imports.gi;
gjs> GLib.strerror(40)
"Too many levels of symbolic links"

[hfiguiere]

From outside of the sandbox:

-rw-r--r--. 1 hub hub 5931008 Nov 10 22:12 /run/user/1000/doc/21cd154d/niepcelibrary.db

From inside

-r--r--r--. 1 hub hub 5931008 Nov 10 22:12 niepcelibrary.db

So the permissions are incorrect.

With strace:

access("niepcelibrary.db", F_OK)        = 0
openat(AT_FDCWD, "niepcelibrary.db", O_RDONLY) = 3
newfstatat(3, "", {st_mode=S_IFREG|0444, st_size=5931008, ...}, AT_EMPTY_PATH) = 0
read(3, "SQLite format 3\0\20\0\1\1\0@  \0\1\v)\0\0\5\250"..., 4096) = 4096
close(3)                                = 0
getcwd("/run/flatpak/doc/21cd154d", 4096) = 26
newfstatat(AT_FDCWD, "/run", {st_mode=S_IFDIR|0700, st_size=180, ...}, AT_SYMLINK_NOFOLLOW) = 0
newfstatat(AT_FDCWD, "/run/flatpak", {st_mode=S_IFDIR|0700, st_size=220, ...}, AT_SYMLINK_NOFOLLOW) = 0
newfstatat(AT_FDCWD, "/run/flatpak/doc", {st_mode=S_IFDIR|0500, st_size=0, ...}, AT_SYMLINK_NOFOLLOW) = 0
newfstatat(AT_FDCWD, "/run/flatpak/doc/21cd154d", {st_mode=S_IFDIR|0500, st_size=0, ...}, AT_SYMLINK_NOFOLLOW) = 0
newfstatat(AT_FDCWD, "/run/flatpak/doc/21cd154d/niepcelibrary.db", {st_mode=S_IFREG|0444, st_size=5931008, ...}, AT_SYMLINK_NOFOLLOW) = 0
getpid()                                = 8
getpid()                                = 8
openat(AT_FDCWD, "/run/flatpak/doc/21cd154d/niepcelibrary.db", O_RDWR|O_CREAT|O_NOFOLLOW|O_CLOEXEC, 0644) = -1 EACCES (Permission denied)
openat(AT_FDCWD, "/run/flatpak/doc/21cd154d/niepcelibrary.db", O_RDONLY|O_NOFOLLOW|O_CLOEXEC) = -1 ELOOP (Too many levels of symbolic links)
write(2, "Error: unable to open database \""..., 80Error: unable to open database "niepcelibrary.db": unable to open database file
) = 80
exit_group(1)                           = ?
+++ exited with 1 +++

[hfiguiere]

Nevermind. document-export is readonly by default. For the ELOOP error, open uses O_NOFOLLOW:

O_NOFOLLOW
     If the trailing component (i.e., basename) of pathname is a symbolic link, then the open fails, with the error  ELOOP. 

Now open does try to open /proc/self/fd/FD which is symlink to the file. But with O_NOFOLLOW.

I think we need to resolve the link manually here.

[hfiguiere]

Second problem:
once open, doing .dump in sqlite3 cause an errror. strace shows

fcntl(3, F_SETLK, {l_type=F_RDLCK, l_whence=SEEK_SET, l_start=1073741824, l_len=1}) = -1 ENOSYS (Function not implemented)
fcntl(3, F_SETLK, {l_type=F_RDLCK, l_whence=SEEK_SET, l_start=1073741824, l_len=1}) = -1 ENOSYS (Function not implemented)

[hfiguiere]

We need to implement the locking mechanisms for sqlite.