Re: [HACKERS] Row Level Security UPDATE Confusion
| От | Rod Taylor |
|---|---|
| Тема | Re: [HACKERS] Row Level Security UPDATE Confusion |
| Дата | |
| Msg-id | CAHz80e5rgT-DUbGnsxZnPA5=Do4Jm7e4SzQXoBM0h537D=Xstw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] Row Level Security UPDATE Confusion (Stephen Frost <sfrost@snowman.net>) |
| Ответы |
Re: [HACKERS] Row Level Security UPDATE Confusion
|
| Список | pgsql-hackers |
On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost <sfrost@snowman.net> wrote:
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:
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
В списке pgsql-hackers по дате отправления: