Re: Question on TRUNCATE privleges

From: Thomas Hallgren <thhal(at)mailblocks(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Question on TRUNCATE privleges
Date: 2005-02-23 14:03:39
Message-ID: thhal-0vyX4AivaxicT6Be61FFX18HqjaKyNg@mailblocks.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-novice

Tom Lane wrote:
> "Keith Worthington" <keithw(at)narrowpathinc(dot)com> writes:
>
>>I have just discovered that I can speed up one of my functions by a factor of
>>600 by changing an unqualified DELETE to a TRUNCATE. Unfortunately, the
>>function is run by multiple users and I get the error message
>> "TESTDB=> TRUNCATE inventory.tbl_item;
>> ERROR: must be owner of relation tbl_item
>
>
>>There is nothing in the documentation
>>(http://www.postgresql.org/docs/8.0/interactive/sql-truncate.html) about this
>>restriction ( You see Michael I am still reading the documentation. ;-) ) Do
>>I get to post my first user comment on the documentation pages? Do I? Hunh?
>>Can I? :-)
>
>
> Yup ;-)
>
>
>>Is there a way to have multiple owners of a table or otherwise achive this
>>behavior?
>
>
> I'm not entirely sure that requiring ownership of the table is the
> appropriate restriction for TRUNCATE. It made some sense back when
> TRUNCATE wasn't transaction-safe, but now that it is, you could almost
> argue that ordinary DELETE privilege should allow TRUNCATE.
>
> Almost. The hole in the argument is that TRUNCATE doesn't run ON DELETE
> triggers and so it could possibly be used to bypass things the table
> owner wants to have happen. You could equate TRUNCATE to DROP TRIGGER(s),
> DELETE, CREATE TRIGGER(s) ... but DROP TRIGGER requires ownership.
>
> CREATE TRIGGER only requires TRIGGER privilege which is grantable.
> So one answer is to change DROP TRIGGER to require TRIGGER privilege
> (which would mean user A could remove a trigger installed by user B,
> if both have TRIGGER privileges on the table) and then say you can
> TRUNCATE if you have both DELETE and TRIGGER privileges.
>
> It looks to me like the asymmetry between CREATE TRIGGER and DROP
> TRIGGER is actually required by SQL99, though, so changing it would
> be a hard sell (unless SQL2003 fixes it?).
>
> Comments anyone?
>
Why not say that TRUNCATE requires the same privilige as a DELETE and
add a trigger type that fires (once) on a TRUNCATE? That would give an
owner a chance to prevent it. Such a trigger would probably be useful
for other things too.

Regards,
Thomas Hallgren

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Berényi Gábor 2005-02-23 15:53:34 problem with function loading
Previous Message Kenneth Marshall 2005-02-23 13:42:12 Re: left-deep plans?

Browse pgsql-novice by date

  From Date Subject
Next Message Frank Bax 2005-02-23 14:08:23 Re: Can Clipper connect Postgresql
Previous Message Kjetil Haaland 2005-02-23 13:01:36 user defined types and strings