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