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.
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
Last activity: May 15, 2023, 06:26 PM