Regarding TODO item "%Add a separate TRUNCATE permission"

I would like to start a discussion regarding the TODO item
“%Add a separate TRUNCATE permission” to gain more information.

The new TRUNCATE permission:
Is it meant to be a general truncating permission on all tables,
schema's like: “I, the DBA give you the privilege to TRUNCATE”
Or is this a per-table, per-schema truncate privilege.

Could someone provide more information about this?

Thank you.

* Gevik Babakhani (pgdev(at)xs4all(dot)nl) wrote:
> The new TRUNCATE permission:
> Is it meant to be a general truncating permission on all tables,
> schema's like: ???I, the DBA give you the privilege to TRUNCATE???
> Or is this a per-table, per-schema truncate privilege.
>
> Could someone provide more information about this?

It would be a per-table, table-level privilege.

Thanks,

Stephen

On Wed, 2006-04-26 at 13:31 -0400, Stephen Frost wrote:
> * Gevik Babakhani (pgdev(at)xs4all(dot)nl) wrote:
> > The new TRUNCATE permission:
> > Is it meant to be a general truncating permission on all tables,
> > schema's like: ???I, the DBA give you the privilege to TRUNCATE???
> > Or is this a per-table, per-schema truncate privilege.

> It would be a per-table, table-level privilege.

Would the privilege apply to the table depending on the table being
truncated?

* Gevik Babakhani (pgdev(at)xs4all(dot)nl) wrote:
> On Wed, 2006-04-26 at 13:31 -0400, Stephen Frost wrote:
> > * Gevik Babakhani (pgdev(at)xs4all(dot)nl) wrote:
> > > The new TRUNCATE permission:
> > > Is it meant to be a general truncating permission on all tables,
> > > schema's like: ???I, the DBA give you the privilege to TRUNCATE???
> > > Or is this a per-table, per-schema truncate privilege.
>
> > It would be a per-table, table-level privilege.
>
> Would the privilege apply to the table depending on the table being
> truncated?

eh? It's just like 'select', 'update', 'delete', etc. Either you have
permission to truncate the table(s), or you don't. The main problem
you'll run into here is not the implementation (it's trivial and I've
already done it actually) for this specific permission but that we need
to redesign the permission system to allow for more permission bits
because otherwise we'll run out soon.

My initial thought on how to do this was to split the permissions into
"use" permissions and "admin" permissions. There's already a split
along these lines built into the system (lower-order bits are "use" and
higher-order bits are "admin", or the other way around) but *alot* of
things currently expect to be able to pass permissions around in 4
bytes. I'd be happy to look into this some more (and had planned to)
but I've been rather busy lately (finals coming up).

I think the use/admin split is the correct split because the "admin"
permissions aren't checked very frequently (mainly by grants and people
looking at the permission information). The "use" permissions are
checked very frequently and so need to be kept fast. I don't think that
would be very difficult to do though.

Thanks,

Stephen

Gevik Babakhani <pgdev(at)xs4all(dot)nl> writes:
> Would the privilege apply to the table depending on the table being
> truncated?

I think the idea is to require TRUNCATE privilege on all the tables
being truncated in the command. This would substitute for the existing
ownership check.

I do have a concern here, which is that GRANT ALL on a table didn't use
to convey TRUNCATE, but now it will. However, since GRANT ALL does
confer the right to do "DELETE FROM tab", maybe this isn't an issue.

regards, tom lane

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> we need
> to redesign the permission system to allow for more permission bits
> because otherwise we'll run out soon.

Only if we keep inventing separate privileges for things as specific
as TRUNCATE. I was just about to raise this point as a possible reason
why not to invent a separate TRUNCATE bit. (There are other problems,
eg both 't' and 'T' letters are already taken.)

The question that really ought to be answered before doing any of this
is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.

In any case, I don't feel it necessary to panic about running out of
permission bits when the space is only 75% used... with a little care
it'll last us a long time yet, and I'm not eager to pay any performance
price whatsoever just so we can invent the Joe Hacker Memorial Privilege
Bit.

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Gevik Babakhani <pgdev(at)xs4all(dot)nl> writes:
> > Would the privilege apply to the table depending on the table being
> > truncated?
>
> I think the idea is to require TRUNCATE privilege on all the tables
> being truncated in the command. This would substitute for the existing
> ownership check.

Right, definitely agree about this.

> I do have a concern here, which is that GRANT ALL on a table didn't use
> to convey TRUNCATE, but now it will. However, since GRANT ALL does
> confer the right to do "DELETE FROM tab", maybe this isn't an issue.

Hmmm, I have to agree that this an interesting question. I don't tend
to use "GRANT ALL" so I'm not really sure what people are thinking when
they use it. It seems to me that it'd make sense to include TRUNCATE in
'GRANT ALL' (since it includes the abilities to create triggers and
references, etc, which I wouldn't generally consider to be "normal",
where "normal" would be select/insert/update/delete).

Thanks,

Stephen

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > we need
> > to redesign the permission system to allow for more permission bits
> > because otherwise we'll run out soon.
>
> Only if we keep inventing separate privileges for things as specific
> as TRUNCATE. I was just about to raise this point as a possible reason
> why not to invent a separate TRUNCATE bit. (There are other problems,
> eg both 't' and 'T' letters are already taken.)

Unfortunately the things which (I feel anyway) we should be allowing
as grantable permissions really do fall into different categorizations
(imv). TRUNCATE violates MVCC so is more than just DELETE (and I could
definitely see where you might want to allow DELETE and *not* TRUNCATE).
Additionally, I think you need more then SELECT for 'ANALYZE' or
'VACUUM'. I could maybe see associating ANALYZE/VACUUM privileges with
privileges which can modify the table or with a new bit for both of
them. I could also see TRUNCATE having that ability but I do believe
that it'd be useful to be able to grant ANALYZE/VACUUM without granting
TRUNCATE...

> The question that really ought to be answered before doing any of this
> is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.

TRUNCATE doesn't follow MVCC...

> In any case, I don't feel it necessary to panic about running out of
> permission bits when the space is only 75% used... with a little care
> it'll last us a long time yet, and I'm not eager to pay any performance
> price whatsoever just so we can invent the Joe Hacker Memorial Privilege
> Bit.

Splitting the privileges I don't think would incur any real performance
hit at all but I'd rather use up the bits we have before changing
things. I got the impression previously that the privilege system would
need to be changed before adding more things to the current system would
be allowed though.

Thanks,

Stephen

Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > we need
> > to redesign the permission system to allow for more permission bits
> > because otherwise we'll run out soon.
>
> Only if we keep inventing separate privileges for things as specific
> as TRUNCATE. I was just about to raise this point as a possible reason
> why not to invent a separate TRUNCATE bit. (There are other problems,
> eg both 't' and 'T' letters are already taken.)
>
> The question that really ought to be answered before doing any of this
> is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.

TODO has:

* %Add a separate TRUNCATE permission

Currently only the owner can TRUNCATE a table because triggers are not
called, and the table is locked in exclusive mode.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Stephen Frost wrote:

> > The question that really ought to be answered before doing any of this
> > is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.
>
> TRUNCATE doesn't follow MVCC...

We can certainly talk about fixing that. (And CLUSTER at the same time,
I think.)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, 2006-04-26 at 13:54 -0400, Tom Lane wrote:
> The question that really ought to be answered before doing any of this
> is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.

This is actually a very good one I think. Perhaps we shouldn't code and
overkill making things more complex. The only reason I can think of
having a separate TRUNCATE permission is when one could not ROLLBACK a
TRUNCATE. But this is not the case. We can rollback a TRUNCATE :) :)

* Alvaro Herrera (alvherre(at)commandprompt(dot)com) wrote:
> Stephen Frost wrote:
>
> > > The question that really ought to be answered before doing any of this
> > > is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.
> >
> > TRUNCATE doesn't follow MVCC...
>
> We can certainly talk about fixing that. (And CLUSTER at the same time,
> I think.)

The issue is that it seems to be intractable to retain MVCC-ness *and*
provide the performance savings TRUNCATE gives. If you can solve that
problem then we could get rid of TRUNCATE and implement
DELETE-without-WHERE using that magic.

Thanks,

Stephen

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> * Alvaro Herrera (alvherre(at)commandprompt(dot)com) wrote:
> > Stephen Frost wrote:
> >
> > > > The question that really ought to be answered before doing any of this
> > > > is why DELETE privilege shouldn't be sufficient to allow TRUNCATE.
> > >
> > > TRUNCATE doesn't follow MVCC...
> >
> > We can certainly talk about fixing that. (And CLUSTER at the same time,
> > I think.)
>
> The issue is that it seems to be intractable to retain MVCC-ness *and*
> provide the performance savings TRUNCATE gives. If you can solve that
> problem then we could get rid of TRUNCATE and implement
> DELETE-without-WHERE using that magic.

Let me qualify that- in cases where there aren't row-level triggers or
other things which would prevent it from being possible anyway.

Thanks,

Stephen

Stephen Frost wrote:
> * Alvaro Herrera (alvherre(at)commandprompt(dot)com) wrote:
> > Stephen Frost wrote:
> > > TRUNCATE doesn't follow MVCC...
> >
> > We can certainly talk about fixing that. (And CLUSTER at the same time,
> > I think.)
>
> The issue is that it seems to be intractable to retain MVCC-ness *and*
> provide the performance savings TRUNCATE gives. If you can solve that
> problem then we could get rid of TRUNCATE and implement
> DELETE-without-WHERE using that magic.

Doh, sorry, I was thinking in CLUSTER :-(

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Currently only the owner can TRUNCATE a table because triggers are not
> called, and the table is locked in exclusive mode.

Doh. Of course the point about not calling ON DELETE triggers is why
this has to be considered a special privilege.

Never mind me, I've still got a bad head-cold :-(

regards, tom lane