Sample Header Ad - 728x90

How to write (postgres) RLS-policies for related/nested tables?

1 vote
1 answer
659 views
I have a database structure with lots of tables related to eachother. For the sake of this question let's simplify it a bit to a project-management idea: Organizations have Departments, Departments have Projects and Projects have Tasks. And then there's users, which are member of an Organization. Here's an Entity Relationship Diagram to visualize it. ERD of example https://drawsql.app/teams/riskchallenger/diagrams/policy-example Now for the problem: I want to use RLS to make sure all data is properly protected. Users should always only be able to select/insert/update/delete data for their own organization. So I started out with the following Postgres policy:
CREATE POLICY "Query data of own organization only" ON "Organizations"
AS PERMISSIVE FOR ALL
TO public
USING (auth.uid() = user_id)
Note: auth.uid() is the user-id retrieved from the session (using Supabase). Now how do I protect the other tables such as Tasks?
Asked by Rien Heuver (131 rep)
May 12, 2023, 12:21 PM
Last activity: May 15, 2023, 06:26 PM