[HACKERS] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
* Gevik Babakhani ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE
On Wed, 2006-04-26 at 13:31 -0400, Stephen Frost wrote: * Gevik Babakhani ([EMAIL PROTECTED]) 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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
* Gevik Babakhani ([EMAIL PROTECTED]) wrote: On Wed, 2006-04-26 at 13:31 -0400, Stephen Frost wrote: * Gevik Babakhani ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE
Gevik Babakhani [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
Stephen Frost [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE
* Tom Lane ([EMAIL PROTECTED]) wrote: Gevik Babakhani [EMAIL PROTECTED] 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
Tom Lane wrote: Stephen Frost [EMAIL PROTECTED] 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 EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
* Alvaro Herrera ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE
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 :) :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
* Stephen Frost ([EMAIL PROTECTED]) wrote: * Alvaro Herrera ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
Stephen Frost wrote: * Alvaro Herrera ([EMAIL PROTECTED]) 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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Regarding TODO item %Add a separate TRUNCATE permission
Bruce Momjian pgman@candle.pha.pa.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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
