SQLite Forum

Need help with read only access to database

Need help with read only access to database

(1) By crispo on 2021-07-14 11:44:47 [link] [source]


I need to create a database that can only be updated by the database owner, but allow all other users read privilege on the data.  

* linux OS
* the database is created using sudo to a service account called svc_merc_cdr_prod
* database file and containing directory path set up with chmod 644, eg
-rw-r--r-- 1 svc_merc_cdr_prod svc_merc_cdr_prod 427008 Jul  9 11:08 /opt/bigcdr/nlp/db/prd/control/cdr_control.db

I can access this database as the service account and perform updates, eg from a linux terminal:
sudo -u svc_merc_cdr_prod sqlite3 /opt/bigcdr/nlp/db/prd/control/cdr_control.db

My issue is that no other user can access this.  eg if i run without sudo i'm able to open the sqlite tool but cannot view tables or open the database file??

~$ sqlite3 /opt/bigcdr/nlp/db/prd/control/cdr_control.db
-- Loading resources from /users/******/.sqliterc

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .ta
sqlite> .schema
Error: unable to open database file
sqlite> select * from phase1_control;
Error: unable to open database file

I have another use case where a different service account user is attempting to query a table via jdbc connection and that results in 
"Exception in thread "main" org.sqlite.SQLiteException: [UNKNOWN_ERROR] unknown error (attempt to write a readonly database)"

Its unclear why a select query should require a write operation?

What is the recommended method to restrict read only perms to public users while allowing read write by the database owner?  I've searched the docs but couldn't find anything specific about this.  

https://www.raspberrypi.org/forums/viewtopic.php?t=285406 this post suggests adding a read only pragma to the reading session, is that required?

Any help greatly appreciated, thanks

(2) By Warren Young (wyoung) on 2021-07-14 14:57:51 in reply to 1 [link] [source]

SQLite version 3.7.17 2013-05-20 00:56:22

Read-only behavior has improved in the last 8 years.

Upgrade, then we can discuss any remaining issues you have.

(3) By crispo on 2021-07-14 15:41:24 in reply to 2 [link] [source]

Thanks for the reply Warren

3.7 is the version our sysadmins installed

Which version of sqlite properly allows for read only access as i've described?

is there any other configuration required or is it sufficient to set the appropriate linux file/directory permissions as above?

(4) By Stephan Beal (stephan) on 2021-07-14 15:47:20 in reply to 3 [source]

Which version of sqlite properly allows for read only access as i've described?

If you're going to upgrade, upgrade to the latest. Calls for support of old versions, even "just" a year old, are likely to fall on deaf ears.

(5.1) By Warren Young (wyoung) on 2021-07-14 16:37:50 edited from 5.0 in reply to 3 [link] [source]

3.7 is the version our sysadmins installed

Why are you limited to only what your sysadmins installed? SQLite is easy to compile and install. You don't need complex packaging systems with carefully-crafted permission sets to make it work properly. It's a single executable. On some systems, you can even link it statically so it doesn't depend on platform libraries, so you may even be able to build on a different Linux system type and copy the executable over.

Which version of sqlite properly allows for read only access as i've described?

Search for "read" in the SQLite change log, then ask yourself if you're seriously wanting someone to bisect 8 years of history with numerous changes to the required functionality for you, without even having a reproducible test case to do the bisecting with.

"linux OS" covers hundreds of distributions, each of which supports many filesystems, atop which you have SQLite's own multifarious build details, creating a combinatorial explosion of likely sources for your problem. Would you like us to simply guess which one covers your particular case?

What I can tell you without knowing your platform details is that the --readonly flag went into the sqlite3 shell in February of 2018, subsequently released in 3.23.0. That feature alone might make the difference between success and failure.

(The commit comment only speaks of the shell's .open command, but the flag is available to the shell itself, so you can add --readonly to your calls to sqlite3 without using .open directly.)

Another platform-independent guess is that you're using WAL mode, so the read-only WAL feature that went into 3.22.0 is what you need, possibly coupled with the --readonly flag.

If these informed guesses are wrong, then we're down to filesystem issues, kernel details, build details...all things that are currently hidden from us by your vague "linux OS" specifier.

I would not use the versions above to tell your sysadmins to install 3.22.0 or 3.23.0, though. I repeat my initial request: install the latest release, then let us know if that fails.