SQLite files, ownership and permissions #

This page is for database administrators and system administrators who set up or repair the files of an Exponential SQLite database. With SQLite the file system is the access control: the right owner, group and mode are what grants and roles are on a server database. This page answers: which files exist, who must write them, how to set them up once so that every process keeps working, and how to check that the database cannot be downloaded. Source: Files, ownership and mixed users.

Contents #

1. The four files
2. Who uses the database
3. Set it up once
4. Check after a day
5. What not to do
6. Never serve the database over HTTP
7. Checklist

The four files #

SQLite creates and removes -wal and -shm itself, so the directory must be writable by every process as well. The driver creates var/storage/sqlite3/ with mode 0775, but it does not set the mode or owner of the files: they get the owner and umask of whichever process created them first.

Who uses the database #

On a typical server three kinds of process open it:

• the web server's PHP (PHP-FPM or mod_php), as the site user;
• Velocity workers;
• cronjobs and console commands, often another user, sometimes root.

If one of them creates -shm with mode 0644, the others fail with "attempt to write a readonly database".

Set it up once #

# one group that every process runs with (the web server's group is usual)
chgrp -R <web group> var/storage/sqlite3
# the setgid bit: files created in the directory inherit its group
chmod 2775 var/storage/sqlite3
chmod 664 var/storage/sqlite3/*

Then give every process a umask of 0002, so new files are group-writable:

• in a systemd unit: UMask=0002;
• at the top of a cron line: umask 002; cd /path/to/installation && php runcronjobs.php ...;
• in the PHP-FPM pool and Velocity's service, through their service manager.

Chapter 8 covers the users the servers run as; Writable directories and permissions the same for hosting companies.

Check after a day #

ls -l var/storage/sqlite3/

Every file should be group-writable and carry the common group (owner, group and sizes are yours):

-rw-rw-r-- 1 example www-data 187678720 Oct  5 10:14 exponential.db
-rw-rw-r-- 1 example www-data     32768 Oct  5 10:20 exponential.db-shm
-rw-rw-r-- 1 example www-data   4124152 Oct  5 10:20 exponential.db-wal
-rw-rw-r-- 1 example www-data         0 Oct  5 09:58 exponential.db.writer-lock

What not to do #

• Do not run bin/modfix.sh (bin:modfix) to fix this. It makes var/storage and other directories world-writable (mode 777, files 666), and the script itself warns that this is not secure. Fix the group and mode as above.
• Do not delete -wal or -shm while any process has the database open.
• Do not put the database on a network file system shared by several machines: WAL needs shared memory on one host.
• Do not copy a busy database file as a backup; use .backup or VACUUM INTO (Backups per engine).

Never serve the database over HTTP #

The database lives below var/storage, which the shipped .htaccess sends to index.php and which Velocity's list of static files leaves out. If the web server is configured by hand, check it on your own host; anything but 200 is right:

curl -sI https://www.example.com/var/storage/sqlite3/exponential.db | head -1

A database kept at an absolute path outside the document root (Database=/srv/exponential/data/site.db) cannot be served at all; the same ownership rules apply to that directory.

Checklist #

• To do: One common group for every process; directory 2775; files 664.
• To do: Every process (PHP-FPM, Velocity, cron, console) runs with umask 0002.
• To do: No file in var/storage/sqlite3/ is owned by a user the others cannot write for.
• To do: curl -sI on the database address does not return 200.
• To do: Backups of the file are readable only by administrators.

Related pages #

• SQLite 3 for database administrators
• Securing the database
• Troubleshooting database errors
• SQLite for hosts
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.