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

Question

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?

Answer 1

NOTE: The following answer only "cascades" SELECT-permissions. I haven't yet figured out how to achieve this same effect for the other operations (insert, update, delete).

The clue here is that policies will invoke whenever the database is queried. That includes queries from within other policies. I.e. you can write a policy on departments checking the organizations, which will trigger the policy on organizations. So to answer the question, there are the policies I wrote for the various tables:

Departments:

CREATE POLICY "only within own organization" ON "Departments"
AS PERMISSIVE FOR ALL
TO public
USING ((EXISTS ( SELECT "Organizations".id
 FROM "Organizations"
 WHERE ("Organizations".id = "Departments".organization_id))))

Projects:

CREATE POLICY "only within own organization" ON "Projects"
AS PERMISSIVE FOR ALL
TO public
USING ((EXISTS ( SELECT "Departments".id
 FROM "Departments"
 WHERE ("Departments".id = "Projects".department_id))))

Tasks:

CREATE POLICY "only within own organization" ON "Tasks"
AS PERMISSIVE FOR ALL
TO public
USING ((EXISTS ( SELECT "Projects".id
 FROM "Projects"
 WHERE ("Projects".id = "Tasks".project_id))))

Now when a user has a session and queries Tasks, it will apply all the above policies and the one from the question consecutively and then only return the tasks from the organization this user belongs to.