Jump to content
  • 0

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



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





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.

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...