Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application?

Question

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.

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