Re: Row Level Security UPDATE Confusion

Rod,

* Rod Taylor (rod(dot)taylor(at)gmail(dot)com) wrote:
> 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).

Interesting.

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

Is that really what you intend/want though? For records with
upper() = CURRENT_TIMESTAMP to not be visible? You are able to change
the range returned from tstzrange by specifying what you want, eg:

select tstzrange(current_timestamp, current_timestamp, '[]');

> CREATE A TABLE t (id integer, active_period tstzrange NOT NULL DEFAULT
> tstzrange(current_timestamp, NULL));

Why NULL instead of 'infinity'...?

> -- Disallowed due to hide_old_select policy.
> UPDATE t SET active_period = tstzrange(lower(active_period),
> CURRENT_TIMESTAMP);

Guess I'm still trying to figure out if you really intend for this to
make the records invisible to the 'most users' case.

> 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).

I agree that the documentation could be improved here.

Thanks!

Stephen