1

The Situation:

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.

My Understanding:

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.

Supporting Info:

  • 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.

RLS Sample Image

3
  • What is exactly the benefit of RLS if the database is local to the application? The computer running the application and hence the database has obvious full access to the DB content, how are you going to enforce which users sees what and even how you define your users if they are spread on multiple machines you do not control and without centralized authentication? Jul 24, 2020 at 16:42
  • @PatrickMevzek "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 database exists as the primary storage for user, but like I mentioned in my post, I am starting to implement a server-side DB so that information can be shared among user_groups, when allowed. Jul 24, 2020 at 17:04
  • why, oh why, would you try to implement your own RLS when it's built in to Postgres? Dec 14, 2020 at 18:05

1 Answer 1

1

I believe you're problem is similar to one I've faced a few years ago, while trying to design a multi-tenant web application with a single underlying database schema. There's an interesting article by AWS that can point you in the right direction. The core of it is:

CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id::TEXT = current_user);

There are several different approaches, but the one I ended following is this:

  • Use a different database user for each connection coming from your desktop PCs. This should match your RLGroupId, if I understood correctly.
  • Apply a Policy to every table where data segregation by RLGroupId should be enforced

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.