How to implement RLS for SELECT and INSERT seperately-CodePudding

CREATE POLICY table_policy 
  ON organisation 
  TO role_A 
 USING (id IN (SELECT organisation_id 
               FROM org_user_map_table 
               WHERE user_id = current_setting('app.current_user_id')::uuid));

The current RLS policy restricts users ensuring them to view data related to their own organisation.

I am now facing issue while INSERTing a new org into the Database where it throws error saying according to the above policy restriction, since no such organisation_ids are available for the user in the org_user_map_table the operation is not permitted. So, I cannot INSERT the new organisation, let alone access it at a later point.

While a suggestion for adding new roles and policies to perform INSERTS could be added, I have to work with the existing system and I am not sure how to split the policy differently for SELECTS and INSERTS.

CodePudding user response：

Since you didn't explicitly specify it, you created the policy FOR ALL statements. If you need different conditions for, say, INSERT and SELECT, create two policies instead:

CREATE POLICY table_policy_sel ON organisation
  FOR SELECT
  TO role_A 
 USING (...);

CREATE POLICY table_policy_ins ON organisation
  FOR INSERT
  TO role_A 
 WITH CHECK (...);

The condition for the FOR INSERT policy can be different from the one FOR SELECT.