On 9 December 2012 06:08, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
> 2012/12/7 Simon Riggs <simon@2ndquadrant.com>:
>> On 5 December 2012 11:16, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>>
>>>> * TRUNCATE works, and allows you to remove all rows of a table, even
>>>> ones you can't see to run a DELETE on. Er...
>>>>
>>> It was my oversight. My preference is to rewrite TRUNCATE command
>>> with DELETE statement in case when row-security policy is active on
>>> the target table.
>>> In this case, a NOTICE message may be helpful for users not to assume
>>> the table is always empty after the command.
>>
>> I think the default must be to throw an ERROR, since part of the
>> contract with TRUNCATE is that it is fast and removes storage.
>>
> OK. Does the default imply you are suggesting configurable
> behavior using GUC or something?
> I think both of the behaviors are reasonable from security point
> of view, as long as user cannot remove unprivileged rows.
Hmm, its difficult one that. I guess this raises the question as to
whether users know they are accessing a table with RLS enabled. If
they don't and we want to keep it that way, then changing TRUNCATE
into DELETE makes sense.
To issue TRUNCATE you need the correct privilege, which is separate from DELETE.
If they have TRUNCATE privilege they should be allowed to remove all
rows, bypassing the row level security.
If that behavious isn't wanted, then the table owner can create an
INSTEAD OF TRUNCATE trigger that turns the action into a DELETE, which
is then subject to RLS rules.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services