Re: Row Level Security UPDATE Confusion

On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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:
>

Yeah, think of it like a delete. Once a record is deleted you want it to
disappear. From the users point of view, who doesn't have time-travel
privileges, an UPDATE to upper() = CURRENT_TIMESTAMP should disappear from
any actions that take place later in the transaction.

A more common way of implementing this is an archive table. Have a DELETE
trigger and shuffle the record to another storage area but with many
dependent tuples via foreign key this can be very time consuming. Flipping
a time period is consistently fast with the caveat that SELECT pays a price.

If you decide Pg shouldn't allow a user to make a tuple disappear, I would
probably make a DO INSTEAD SECURITY DEFINER function that triggers on
DELETE for that role only and changes the time range. Reality is after
about 1 week for customers to contact their account administrator and say
"I accidentally deleted X" it would likely be moved to an archive structure.

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'...?
>

Diskspace. NULL works (almost) the same as infinity but the storage is
quite a bit smaller.

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

Yep. It's equivalent to a DELETE or DEACTIVATE. RLS may not be the right
facility but it was very close to working exactly the way I wanted in FOR
ALL mode.

--
Rod Taylor