SQLite DB backend

[chrisengelhardt]

Hi friends,

We have a shiny application that uses a SQLite DB backend. Our goal is to deploy this application to Connect, but we are unsure of where best to host the DB backend.

We mistakenly had the following configuration in /etc/rstudio-connect/rstudio-connect.gcfg (thinking we could reference it with a path in the app files):

[Database]
Provider = sqlite

[SQLite]
Dir = /var/lib/rstudio-connect/sqlite

However, this specification is problematic for a number of reasons (e.g., this SQLite DB, as specified here, is used for Connect meta data).

In addition, this directory seems to be "masked," as per the documentation on sandboxing (https://docs.rstudio.com/connect/admin/process-management.html#process-management-sandboxing), and in light of this post (https://support.rstudio.com/hc/en-us/articles/360007981134-Persistent-Storage-on-RStudio-Connect), the answer seems to be to host the SQLite backend in a top-level directory (e.g., /sqlite_db).

Will hosting the SQLite backend in, say, /sqlite_db and referencing the location via an absolute path be a viable solution? We're aware of the multiple write problem and will not have the DB hosted on an NFS server. The amount of data written to the DB backend is extremely trivial, which is why we're not considering other options like Oracle, hdfs, or PostgreSQL.

Thanks for the insight anyone might have on this issue.

Best,

Chris

[cole]

This is a fantastic question! And spot on for the solution. We don't allow any Shiny apps or other content to access the Connect database or other data directories for security reasons.

The masking of various directories is also the reason we suggest creating a top level directory via an absolute path. This also helps with organization and prevents permission issues that can crop up in the file tree. (As an aside, one way to debug those if they ever crop up is namei -l /path/to/my/folder )

One way to make this approach more palatable while deploying is to use something like the config package so that locally (while developing) you use a relative path, but when deployed to Connect, you use an absolute path. The switching in that case comes absolutely for free with no programming changes! Something like

default:
  db_path: "./my.db"
rsconnect:
  db_path: "/sqlite_db/my_app.db"

And then config::get("db_path")

Hope that helps!

[chrisengelhardt]

Thank you for the quick reply, @cole! We will give this a shot and report back.

[chrisengelhardt]

I'm happy to report that creating the top-level directory solved our problem. Thank you, @cole! You are the best!

[chrisengelhardt]

@cole Related to this issue, what is the best practice for permissions on sqlite_db/my_app/app_file? Right now, that file is owned by the rstudio-connect user and the rstudio-connect group. Permissions are -rw-------. The app cannot write to this file under these permissions. However, the app can write to that file if I make permissions 777, but that seems like bad practice.

[cole]

Interesting! What is the run as user for your content? It is a requirement that the run as user be a member of the rstudio-connect group, so 660 is probably a good bet (if I remember correctly... should be rw for group and user). Would you mind giving that a try and letting me know what happens? You could also leave it 600 and just change the owner to be the run as user. With 660, all apps on Connect will have access to the db file.

The last thing I would check is the execute bit (not sure if .db files need to be executed). Set it to 700 or 770.

[chrisengelhardt]

The application runs as rstudio-connect. The issue was that the parent directory of the DB file did not have the right permissions. After changing the owner and group-level permissions to rstudio-connect, everything worked fine.

[cole]

Awesome, good to know! Just a tip: namei -l /path/to/my/folder is a great way to debug hierarchical folder permissions!

[chrisengelhardt]

Oooooh. Cool! Thanks for sharing that!