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

StackExchange Database Administrators Q#281533, answer score: 2