Re: [HACKERS] Row Level Security UPDATE Confusion

My actual use-case involves a range. Most users can see and manipulate the record when CURRENT_TIMESTAMP is within active_period. Some users (staff/account admins) can see recently dead records too. And a 3rd group (senior staff) have no time restriction, though there are a few customers they cannot see due to their information being a touch more sensitive.  I've simplified the below rules to just deal with active_period and the majority of user view (@> CURRENT_TIMESTAMP).

NOTE: the active_period range is '[)' by default, so records with upper() = CURRENT_TIMESTAMP are not visible with @> CURRENT_TIMESTAMP restriction.




The below policy is allowed but requires that 1ms slop to accommodate the wi

Updated record invisible to USING but requires a trigger to enforce specific upper
and starting values. I have a trigger enforcing specific upper/lower values for the range
for specific ROLEs. So I had the thought that I might move ROLE specific trigger logic into
the RLS mechanism.




I tried to tighten the above rules, so INSERT must have upper of NULL and UPDATE must
set upper to exactly CURRENT_TIMESTAMP. Clearly I can achieve this using triggers for
enforcement but I tried to abuse RLS instead because it is a role specific restriction.

I was surprised when hide_old_select->USING was preventing the UPDATE when the simple single policy version let it through.




I'm happy to help with testing and documentation but first I need to understand what the intended functionality was. Right now it seems inconsistent between the simple single policy version and the multi policy version; the docs imply the single policy version is correct (it only seems to mention SELECT checks on RETURNING clauses).