Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Question on TRUNCATE privleges

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: KeithW(at)narrowpathinc(dot)com,PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: [NOVICE] Question on TRUNCATE privleges
Date: 2005-02-22 20:37:25
Message-ID: 1109104646.20250.14090.camel@camel (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice
On Tue, 2005-02-22 at 14:00, 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?

Isn't this a case for a SECURITY DEFINER function?


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


In response to

pgsql-novice by date

Next:From: brewDate: 2005-02-22 22:00:16
Subject: Re: Perl DBI connection to Postgres
Previous:From: A.M.Date: 2005-02-22 20:01:43
Subject: Re: [NOVICE] Question on TRUNCATE privleges

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2005-02-22 20:50:10
Subject: Re: [pgsql-hackers-win32] UNICODE/UTF-8 on win32
Previous:From: A.M.Date: 2005-02-22 20:01:43
Subject: Re: [NOVICE] Question on TRUNCATE privleges

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group