I am currently exploring Postgres's Row Level Security and related policies for my project, and have a question about best practices:
If I have these tables:
| box |
|---|
| id |
| name |
| user_id |
| thing |
|---|
| id |
| big_box_id |
| name |
I have a select policy which checks the user_id agains JWT claims. This let's me only select my own boxes.
Question: What's the best way to prevent users to select things which do not belong to them?
As I see it, there are two options:
Option 1: add a user_id column to thing and have each item also store its user_id to be used on policies (same as in box)
Option 2: on the select policy for thing, add a check on the user_id of the parent box
My intuition says option 2 is cleaner, but I wonder how would that go when things get deeper, let's say my things can also have children, then I have to travel back several levels to the parent box within my select policy?
Any insights are extremely appreciated!!
Top comments (1)
Hi Daniel, did you solve your dilemma? I came across the Kuroda multi_tenancy repo on github and have found it very helpful. This approach uses a tenant_id on EVERY table and the policies are setup using a migration helper to assure the correct row selection for related tables, etc depending on the policy applied. I think I will adopt this method. I am working on an app now and getting it to play nicely with devise. I'm looking at creating a separate policy to expose just the users table for devise login for non-logged in users.