Ask HN: Row Level Security Extension for SQLite Feasiblity
sqlite.org
sqlite.org
I am seeing that 1) we can load extensions into SQLite (see attached URL), and 2) that extensions can create modules with pretty rich functionality. Maybe a virtual table impl with `xFilter` and/or `xNext` fns could help yield the row filtering, plus some custom fns to add some state used during the query to configure the dynamic constraint?
Interesting idea? Plausible?
I also saw someone made some handy golang bindings, which could add a few niceties to writing such an extension (albeit heresy to some, to lodge Go into SQLite :)).
https://github.com/riyaz-ali/sqlite/blob/master/virtual_tabl...
Since SQLite DB is just a file accessed using a library, the authorization limitations would only exist if accessed through the library, any program that could read the file could easily bypass the authorization.
This feels like a separate task/module that would need resolving first.
There may be benefit even if we do RLS with a soft authentication mechanism. In this case I would assume the accessing application would handle the Authorization and then pass that to your extension somehow, which then would filter any subsequent queries with RLS trusting that setting until it was changed. Keep in mind, nothing would prevent the app from setting another user, but this could make it easier to segregate data exposed via a webapp based on the webapp's logged in user.
1. the db file is not publicly accessible. it is accessible only to some private application 2. user data is inside the database 3. the application passes some configuration in before the query runs (a la https://www.graphile.org/postgraphile/security/#how-it-works) 4. that configuration identifies the user through some downstream `check constraint` implementation, which currently doesn't exist :)
These assumptions are consistent with your second paragraph, which is my goal.