CODEWITHSUNDEEP
postgresqlrow-level-security
Lokesh1024
Lokesh1024

Reputation: 115

can we insert or update data in table from RLS policy in Postgres?

I've created table "clientID_dbUser_mapping" which contains client id (e.g 1,2,3) and DB User name (u1,u2,u3)

Now created another table "test_data" which contains id(PK), data(text), client_id(FK)

created RLS policy for "test_data" to access the appropriate logged in db_user data

here is policy :

CREATE POLICY client_id_testdata_policy ON test_data
FOR ALL
USING (client_id =
              (SELECT client_id
               FROM client_dbuser_mapping
               WHERE db_user_name = current_user)
      )
WITH CHECK (client_id =
              (SELECT client_id
               FROM client_dbuser_mapping
               WHERE db_user_name = current_user)
           );

This is working fine for SELECT/UPDATE/DELETE

for INSERT I want to add default data of client_id in "test_data" from "clientID_dbUser_mapping" table coz it contains client id as per db_user_name

so how can I achieve this through RLS policy (do i need to change something in my existing policy) or need to create new policy ?

is it possible or not?

Upvotes: 3

Views: 938

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

I am not sure if I understood the requirement correctly, but if you want to force the client_id to a certain value upon INSERT, use a trigger:

CREATE FUNCTION set_client_id() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   SELECT cdm.client_id INTO NEW.client_id
   FROM client_dbuser_mapping AS cdm
   WHERE cdm.db_user_name = current_user;

   RETURN NEW;
END;$$;

CREATE TRIGGER set_client_id BEFORE INSERT ON test_data
   FOR EACH ROW EXECUTE PROCEDURE set_client_id();

Upvotes: 3

Related Questions