Postgres RLS and policies: best practices?

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!!

Comments

[llawrenc]

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.