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.