Thread: Restrictive combination of GRANT and POLICY

Hi!

The permissions given by GRANT and POLICY statements seem to always be combined "permissively". In other words, if role `foo` inherits from roles `can_access_all_columns_but_no_rows` and `can_access_all_rows_but_no_columns`, then `foo` would be able to access all rows and all columns of the table in question. I wonder, what it would take to extend Postgres to allow to combine them "restrictively". 

One hacky way to do so would be to apply the following logic when evaluating a query:

1) Use the RLS policies to filter out the rows that should be visible to the given user. On each row, record the set roles that allow the operation.

2) For each row and for each column, iterate through the intersection of (recorded roles, roles the current roles inherits from) to see if the column should be given access to. If not, return a null in that position. (For updates/inserts error out).

Obviously, this would be a departure from SQL standard. But other than that, is this a valid feature idea? I am not a fan of shoehorning nulls for this, but given that the database can omit rows when using RLS, nulls don't seem to be too far from that.

The reason I'm bringing it up is that it seems to solve the following problem nicely: imagine you have a table `people`, and an association table between two people called `friends`. Each person should see their own data in `people` and a subset of columns of `people` if they are friends. (Please refer to the attached file for definitions). 

If there's an easier solution that's possible today I'd be curious to learn about it. The best I could come up with (for queries only) is defining views that do this "null-masking".Something like this:

CREATE VIEW people_for_person AS
SELECT
  id,
  CASE WHEN roles.is_self OR roles.is_friend THEN email END AS email,
  CASE WHEN roles.is_self THEN password END AS password
FROM people p
JOIN LATERAL (
  SELECT p.id = current_setting('app.user_id')::INTEGER AS is_self,
  EXISTS (
    SELECT true
    FROM friends f
    WHERE f.person_id = p.id
      AND f.friend_id = current_setting('app.user_id')::INTEGER
  ) AS is_friend
) roles ON true;

Cheers,

Bakhtiyar