Ask HN: Row Level Security Extension for SQLite Feasiblity(sqlite.org)
sqlite.org
Ask HN: Row Level Security Extension for SQLite Feasiblity
https://www.sqlite.org/loadext.html
3 comments
SQLite doesn't have an authentication mechanism to identify users. This seems to me to be a dependency for setting Authorization.
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.
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.
Thanks for the input. I'll make some previously unstated assumptions, which I should have sent in the first post:
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.
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.
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...