Database Administrators Asked on December 28, 2021
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:
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.
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:
Hope this helps.
Answered by Manuel Padilha on December 28, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP