Обсуждение: Re: [NOVICE] Question on TRUNCATE privleges
"Keith Worthington" <keithw@narrowpathinc.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? regards, tom lane
On Tue, 2005-02-22 at 14:00, Tom Lane wrote: > "Keith Worthington" <keithw@narrowpathinc.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? Isn't this a case for a SECURITY DEFINER function? Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
The author doesn't mention why he got a 600x increase- perhaps he bypassed the delete triggers which was OK for his situation. I don't like the notion that an optimization requires additional privileges...why not detect an unqualified delete and call truncate instead IFF there are no delete triggers on the table? > 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? > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match
Tom Lane wrote: > "Keith Worthington" <keithw@narrowpathinc.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