DEV Community

Daniel da Rocha
Daniel da Rocha

Posted on

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

Discussion (1)

Collapse
llawrenc profile image
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.