Can SQLite DB files be read-only?

Question

Information from SQLite DB is provided to the user through a web server (displayed in an HTML browser). The database is loaded once for just a small application, independent of the web server. Database data cannot be changed from the user's browser (this is a read-only service).

Since the web server has its own user ID, it accesses the SQLite DB file with "other" permissions. For security reasons, I would like to set DB file permissions as rw-rw-r-- .

Unfortunately, with this permission set, I get an attempt to write a readonly database at line xxx warning message that points to a line about a SELECT transaction (which is basically read-only). Of course, I get no result.

If permissions are changed to rw-rw-rw , everything works fine, but that means everyone can interfere with the database.

Is there a reason SQLite DB might not be read-only?

Are there behind-the-scenes processes that need to be written, even for SELECT transactions?

A search in StackOverflow shows that people usually complain about the opposite situation: they encounter read-only access rights, which prevents writing to the database. My goal is to protect my database from ANY attempt to change.

For the whole story, my web application is written in Perl and uses DBD::SQLite

Answer 1

The solution is given in the answer to this question. Perl DBI considers setting SQLite DB cache_size as a write operation when subclassing DBI .

It turns out that AutoCommit cannot be set to 0 with read-only SQLite DB. Explicitly forcing 1 in the case of read-only DB resolved the problem.

Thanks to everyone who gave clues and leads.

Answer 2

You must connect to your SQLite db in readonly mode.

From the docs :

You can also set sqlite_open_flags (only) when connecting to the database:

 use DBD::SQLite; my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, { sqlite_open_flags => DBD::SQLite::OPEN_READONLY, }); 

- https://metacpan.org/pod/DBD::SQLite#Database-Name-Is-A-File-Name