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

Re: [NOVICE] Question on TRUNCATE privleges

From: "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [NOVICE] Question on TRUNCATE privleges
Date: 2005-02-22 20:01:43
Message-ID: 92F260F7-850C-11D9-B159-0050E41ED24B@themactionfaction.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice
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


In response to

pgsql-novice by date

Next:From: Robert TreatDate: 2005-02-22 20:37:25
Subject: Re: [NOVICE] Question on TRUNCATE privleges
Previous:From: Tom LaneDate: 2005-02-22 19:00:56
Subject: Re: [NOVICE] Question on TRUNCATE privleges

pgsql-hackers by date

Next:From: Robert TreatDate: 2005-02-22 20:37:25
Subject: Re: [NOVICE] Question on TRUNCATE privleges
Previous:From: Tom LaneDate: 2005-02-22 19:00:56
Subject: Re: [NOVICE] Question on TRUNCATE privleges

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