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

Question

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?

@LaurenzAlbe need little more help. [can you check code](https://codeshare.io/r9QVLE) I have called function `clientid_by_dbuser()` as DEFAULT value in my `client_id` column of `test_data` table. need confirmation that can I use (is this correct way?) DEFAULT value from function as given in link or Trigger is only option. — Lokesh1024, Apr 11 '22 at 09:29

score 3 · Answer 1 · answered Apr 08 '22 at 16:59

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();

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

1 Answers1