PostgreSQL – Create RLS Policy Based on Table Values

postgresqlrow-level-security

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?

Best Answer

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));
Related Question