I have a desktop application that currently uses a database which is stored locally on the computer that is running the application, and I am starting to implement a server DB so that some of the information in this application can be shared between users on different machines. The local DB schema was not designed to support RLS, or anything close to it. I'm now trying to design a schema that holds the same data, but restricts access to it based on a users 'role' or 'group', etc.
I would ideally like to use PostgreSQL, but others are fine, and saw that they have RLS support, but I'm unsure how to proceed. I'm thinking of using something like a RL_Groups and Users table, with a junction table like RL_Group_Users to determine row level access instead. I'm not sure of performance / security implications of either option, or even the alternative options. I've attached a simplified diagram to give some context to my current thoughts.
- The local database is SQLite 3.
- There are roughly 100 tables at the moment.
- These 100 tables will all need some kind of RLS to restrict information to users and user_groups.
If I missed anything or you have any questions please comment so I can get back to you.
NOTE: The attached diagram has been simplified, data has been removed, etc. but it should be plenty to illustrate the problem.