Is it possible to create a PostgreSQL RLS Policy that depends on values from the table the policy applied on? 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 ---&gt; 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?
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 &quot;user_id&quot; 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));