1

I'd like to create a policy that depends on other records from the same table. For example, let's say there's the following junction table:

 project_id | user_id
------------+----------
     1      |     1
------------+----------
     1      |     2   
------------+----------
     2      |     2   

I'd like to prevent a user from selecting records related to projects it's not related to, and allow the selection of the rest.

Something like:

CREATE POLICY project_user_read ON project_user FOR SELECT 
USING(
      project_id IN(
      SELECT project_id 
      FROM project_user 
      WHERE user_id = current_setting('app.current_user_id')::bigint ---> I use runtime variables
   )
);

That will result this way:

SET app.current_user_id = 1;
SELECT * FROM project_user;

--  project_id | user_id
-- ------------+----------
--      1      |     1
-- ------------+----------
--      1      |     2   

This obviously doesn't work because it's a circular condition.

Is there any way to implement such logic with RLS?

1 Answer 1

1

You could create a function that avoids the endless loop:

CREATE FUNCTION is_ok(project_user) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   /* avoid recursion if the "user_id" is correct */
   IF ($1).user_id = current_setting('app.current_user_id')::bigint THEN
      RETURN TRUE;
   END IF;
   /* otherwise, recurse */
   RETURN EXISTS (SELECT 1 FROM project_user AS pu
                  WHERE ($1).project_id = pu.project_id
                    AND pu.user_id = current_setting('app.current_user_id')::bigint);
END;$$;

Then create the policy like this:

CREATE POLICY project_user_read ON project_user FOR SELECT TO PUBLIC
   USING (is_ok(project_user));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.