RLS when a user can have different roles on different clients (using a third table for role assignments to create MANY-TO-MANY relationships)

[JoeyBrannon]

the overall app is complicated but the issue can be illustrated with four tables

USERS

CLIENTS

USERASSIGNMENTS

TASKS

login credentials are contained in USERS

USERS can be assigned to multiple CLIENTS by using records in USERASSIGNMENTS that contain a unique userID and a unique clientID

each record in TASKS has a userID field and a clientID field

----

in order to secure the database I need to be able to limit record access so that a user can access only tasks related to the client they are working in at the moment

the only way I have found to achieve this (to get the clientID as one of the fields available in the authentication dropdown for RLS) is to have the clientID as a third authentication element, but this requires users to logout and re authenticate every time they start working on a new client's tasks. That is not a workable solution in our use case.

[JoeyBrannon]

The solution I landed on seems to be working pretty well. It requires a third table to establish a many-many relationship between users and projects. Here is the format

USER table is used for authentication and user has a unique id called userID

USERASSIGNMENTS table contains two fields; projectID and userID

PROJECTS field contains projectID

A view relates PROJECTS and USERASSIGNMENTS using the fields PROJECTS.projectID and USERASSIGNMENTS.projectID in a one-to-many relationship

Now all records returned by the passed parameter can be subject to RLS by checking the field USERASSIGNMENTS.userID against the authentication field USER.userID.

The only downside is that if there are 10 users assigned to a project the view will return 10 records related to that project. RLS ensures that only one of those records shows up in the results, but it may cause undesired overhead as the database grows. But it does solve the problem of unwanted access if a user who is not assigned to the project gets hold of the projectID through a copied url or if they were to piece it together themselves.