Thread: Question on TRUNCATE privleges

Question on TRUNCATE privleges

From
"Keith Worthington"
Date:
Hi All,

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? :-)

Is there a way to have multiple owners of a table or otherwise achive this
behavior?

Do I get to post my first user comment on the documentation pages?  Do I?
Hunh? Hunh? Can I? :-)

Kind Regards,
Keith

Re: Question on TRUNCATE privleges

From
Tom Lane
Date:
"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

Re: [HACKERS] Question on TRUNCATE privleges

From
Robert Treat
Date:
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


Re: Question on TRUNCATE privleges

From
Thomas Hallgren
Date:
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


Re: Question on TRUNCATE privleges

From
Bruce Momjian
Date:
Thomas Hallgren wrote:
> > 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.

Uh, that seems like it adds extra complexity just for this single case.

Why don't we allow TRUNCATE by non-owners only if no triggers are
defined, and if they are defined, we throw an error and mention it is
because triggers/contraints exist?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Question on TRUNCATE privleges

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Uh, that seems like it adds extra complexity just for this single case.

Yeah.  I've dropped the idea personally -- the suggestion that the table
owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if he
wants to allow others to do it seems to me to cover the problem.

> Why don't we allow TRUNCATE by non-owners only if no triggers are
> defined, and if they are defined, we throw an error and mention it is
> because triggers/contraints exist?

I don't think we should put weird special cases in the rights checking
to allow this -- that's usually a recipe for introducing unintended
security holes.

            regards, tom lane

Re: Question on TRUNCATE privleges

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Uh, that seems like it adds extra complexity just for this single case.
>
> Yeah.  I've dropped the idea personally -- the suggestion that the table
> owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if he
> wants to allow others to do it seems to me to cover the problem.
>
> > Why don't we allow TRUNCATE by non-owners only if no triggers are
> > defined, and if they are defined, we throw an error and mention it is
> > because triggers/contraints exist?
>
> I don't think we should put weird special cases in the rights checking
> to allow this -- that's usually a recipe for introducing unintended
> security holes.

Yea, good point.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Question on TRUNCATE privleges

From
"Keith Worthington"
Date:
On Thu, 24 Feb 2005 17:15:42 -0500, Tom Lane wrote
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Uh, that seems like it adds extra complexity just for this single case.
>
> Yeah.  I've dropped the idea personally -- the suggestion that the table
> owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if
> he wants to allow others to do it seems to me to cover the problem.

Could someone point me in the direction of documentation on this SECURITY
DEFINER feature?

Kind Regards,
Keith

Re: Question on TRUNCATE privleges

From
Tom Lane
Date:
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> On Thu, 24 Feb 2005 17:15:42 -0500, Tom Lane wrote
>> Yeah.  I've dropped the idea personally -- the suggestion that the table
>> owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if
>> he wants to allow others to do it seems to me to cover the problem.

> Could someone point me in the direction of documentation on this SECURITY
> DEFINER feature?

See CREATE FUNCTION.  Something like (untested)

create function truncate_my_table() returns void as
$$ truncate my_table $$ language sql security definer;

You'd probably then revoke the default public EXECUTE rights on this
function, and grant EXECUTE only to selected users.

            regards, tom lane

Boolean

From
Date:
hi all,

i have a question about booleans.  i heard that a
boolean takes more processing power than setting an
integer as 1 or 0 and coding around those values.  is
this really an issue anyone will notice?

also, i understand that db portablility is somewhat
compromised when one uses pg's boolean data type.

are these really issues or am i over analyzing here?

tia...



__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail

Re: Boolean

From
Tom Lane
Date:
<operationsengineer1@yahoo.com> writes:
> i have a question about booleans.  i heard that a
> boolean takes more processing power than setting an
> integer as 1 or 0 and coding around those values.

Whoever told you that is completely clueless.

> also, i understand that db portablility is somewhat
> compromised when one uses pg's boolean data type.

It is true that there are still DBs that don't have the SQL standard
boolean type.  IIRC that was added to the standard in SQL99.  I think
this is likely to be the least of your portability concerns, however.

            regards, tom lane