Re: [HACKERS] Row Level Security UPDATE Confusion

Rod,

* Rod Taylor (rod.taylor@gmail.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.