Row Level Security Enabled update is not working while standard update does using the same logic

Question

Working on postgres 10.4 (on RDS if it makes a difference) I am trying to enforce application user permissions using Row Level Security.

I have a permissions table which looks something like

user_group_id | entity1 | entity2 | entity3 | permission
==============|=========|=========|=========|============
1             |1        |null     |null     | write
1             |1        |1        |null     | read

1. entity1(root)-entity2-entity3(leaf) is a hierarchy of items stored in different tables. entity1 contains entity2 items, entity2 items contains entity3 items.

2. permission are inherited meaning entity3 inherits the permissions from entity2, entity2 inherits the permissions from entity1, unless:

3. Rows with matching entity3 override rows with matching entity2 which override rows with matching entity1.

In the above example user group 1 has write on all entity2/entity3 under entity1=1 (1st row) except for entity2=1 and all entity3 under entity2=1 on which it has read (2nd row) etc.

I already wrote the logic (i think it's not relevant to the actual question hence not pasting it) as a single query using dense_rank - which is a window function.

When I use this logic with an UPDATE directly - it works flawlessly.

When I embed the same exact logic as the WITH CHECK of a row level security policy - update refuses to update the rows (I enabled ROW LEVEL SECURITY on the relevant tables - so that's not it).

I tried to embed my logic in a function instead of directly in the policy - but got the same result, meaning this works:

--not as table owner
update entity1
set col1=1
where entity1_id=10
and check_access(entity1_id); --updates 1 row

but this doesn't:

--as table owner
alter table entity1 enable row level security;

CREATE POLICY entity1_update
ON entity1 
AS permissive
FOR UPDATE
TO some_role
WITH CHECK ( check_access(entity1_id) );

--not as table owner
update entity1
set col1=1
where entity1_id=10; --updates no rows

Reading the documentation: The conditional expression cannot contain any aggregate or window functions - is that the reason? If so - I would have expected an error to be thrown when trying to create the policy or when actually running the update - but nothing happens.

I also tried re-writing my access check logic using a subquery with ORDER BY and wrapping it with a LIMIT - did not help.

Am I missing something? Any way around this?

Answer 1

I think I found the problem - I have another FOR SELECT policy on the same table and I assumed its USING clause would be sufficient (there's an AND between policies of different FOR types) - so I did not include a USING clause in the FOR UPDATE policy. Once I added a USING (true) on the FOR UPDATE policy things started working normally (for now at least - I need to do more experiments).

Seems like it has no relation to using a window function.

1. I assume postgres allowed me to create a FOR UPDATE policy without a USING clause since it allows multiple policies of the same type - but maybe the documentation should have a note that when having a single FOR UPDATE policy it MUST include both clauses? UPDATE: I'm not the only one who's update failed for the same reason - PostgreSQL, unable to update row ( with row level security )

2. This leads me to ask what does the The conditional expression cannot contain any aggregate or window functions restriction mean - if it seems to work with using dense_rank() over (order by...)... raised Aggregate/Window functions restriction in Postgres Row Level Security Policy conditions