Re: [HACKERS] Must be owner to truncate?
On Wed, 24 Aug 2005 07:01:00 +0200, Andreas Seltenreich [EMAIL PROTECTED] wrote: However, a question arose quickly: According to the standard, revoking INSERT, UPDATE and DELETE after GRANT ALL PRIVILEGES would leave the relation read-only, but with the TRUNCATE privilege lying around, this would no longer be true for PostgreSQL. I'd say that the TRUNCATE privilege includes DELETE, so that REVOKE DELETE implicitly revokes TRUNCATE and GRANT TRUNCATE implicitly grants DELETE. Servus Manfred ---(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] Must be owner to truncate?
* Andreas Seltenreich ([EMAIL PROTECTED]) wrote: Bruce Momjian schrob: Added to TODO: * Add TRUNCATE permission Currently only the owner can TRUNCATE a table because triggers are not called, and the table is locked in exclusive mode. Is anyone working on this yet? I looked at the code involved, and it seems there are just a couple of lines needed, some regression test and documentation updates, and most importantly, tab-completion updates. I'm not working on it, though I agree that it really shouldn't be very difficult to add. I'd certainly like to see it done. While you're in there I'd really like to see analyze and vacuum as grantable permissions too... Of course, eliminating the need for them would be even better... :) However, a question arose quickly: According to the standard, revoking INSERT, UPDATE and DELETE after GRANT ALL PRIVILEGES would leave the relation read-only, but with the TRUNCATE privilege lying around, this would no longer be true for PostgreSQL. Would this open a security hole or is it okay as far as extensions to the standard go? Hrm, I'm not really sure about this one. I could see linking TRUNCATE with DELETE (ie: you must have both DELETE and TRUNCATE permissions on a table to TRUNCATE it, ala SELECT/UPDATE), or perhaps excluding TRUNCATE from GRANT ALL PRIVILEGES. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Manfred Koizar ([EMAIL PROTECTED]) wrote: On Wed, 24 Aug 2005 07:01:00 +0200, Andreas Seltenreich [EMAIL PROTECTED] wrote: However, a question arose quickly: According to the standard, revoking INSERT, UPDATE and DELETE after GRANT ALL PRIVILEGES would leave the relation read-only, but with the TRUNCATE privilege lying around, this would no longer be true for PostgreSQL. I'd say that the TRUNCATE privilege includes DELETE, so that REVOKE DELETE implicitly revokes TRUNCATE and GRANT TRUNCATE implicitly grants DELETE. I disagree with implicitly granting/revokeing. Rather, if we're going to go this route, we should require both DELETE and TRUNCATE rights on the object in order to TRUNCATE it but otherwise have TRUNCATE privileges and DELETE privileges be distinct from each other in terms of being granted/revoked. This follows the SELECT/UPDATE relationship. Granting UPDATE doesn't implicitly grant SELECT, and revoking SELECT doesn't implicitly revoke UPDATE; but in order to actually UPDATE you need SELECT rights. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
Bruce Momjian schrob: Stephen Frost wrote: -- Start of PGP signed section. * Jim C. Nasby ([EMAIL PROTECTED]) wrote: On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a truncate permission? I would find it useful, as it seems would others. That would be acceptable for me as well. I'd prefer it just work off delete, but as long as I can grant truncate to someone w/o giving them ownership rights on the table I'd be happy. Added to TODO: * Add TRUNCATE permission Currently only the owner can TRUNCATE a table because triggers are not called, and the table is locked in exclusive mode. Is anyone working on this yet? I looked at the code involved, and it seems there are just a couple of lines needed, some regression test and documentation updates, and most importantly, tab-completion updates. However, a question arose quickly: According to the standard, revoking INSERT, UPDATE and DELETE after GRANT ALL PRIVILEGES would leave the relation read-only, but with the TRUNCATE privilege lying around, this would no longer be true for PostgreSQL. Would this open a security hole or is it okay as far as extensions to the standard go? regards, Andreas -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Must be owner to truncate?
Stephen Frost wrote: -- Start of PGP signed section. * Jim C. Nasby ([EMAIL PROTECTED]) wrote: On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a truncate permission? I would find it useful, as it seems would others. That would be acceptable for me as well. I'd prefer it just work off delete, but as long as I can grant truncate to someone w/o giving them ownership rights on the table I'd be happy. Added to TODO: * Add 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 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
On Sat, Jul 09, 2005 at 11:48:35AM -0400, Tom Lane wrote: Stephen Frost [EMAIL PROTECTED] writes: * Mike Mascari (mascarm@mascari.com) wrote: And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? The newer transactions would have to check for that situation. How would they do that? They might be long gone by the time the truncating transaction rolls back. It might be possible to do something that preserves full MVCC-ness for concurrent readers, but I don't believe there is any choice but to lock out concurrent writers until the truncate commits. If you try to allow that, there's no way to keep straight whose change goes into which file. What about if inserts go into both files until the truncate commits? Updates are a bit trickier; AFAICS they would have to block until the truncate commits because they would have different effects depending on if the commit happenend or not. But I don't see this as an issue. Likewise, inserts that would violate a unique constraint would have to wait until the truncate committed, but inserts that are unique could commit. Of course something else to consider is that just being able to support concurrent reads against truncate would be a step forward, even if writes were still blocked. That alone means this is probably worth-while, although it might require a slightly different syntax since it would behave differently than the existing truncate does. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
Does truncate not being MVCC-safe cause problems in your situation? It certainly doesn't in mine and I expect the same is true for alot of others in the same situation. Well, it is done inside a transaction, plus has concurrent use... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Must be owner to truncate?
Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? Mike Mascari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Must be owner to truncate?
* Mike Mascari (mascarm@mascari.com) wrote: Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? The newer transactions would have to check for that situation. It's not completely thought through, but at the same time I don't necessairly think it's something that would be completely impossible to do and still retain most of the performance benefits, at least in the most common case. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
Stephen Frost [EMAIL PROTECTED] writes: * Mike Mascari (mascarm@mascari.com) wrote: And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? The newer transactions would have to check for that situation. How would they do that? They might be long gone by the time the truncating transaction rolls back. It might be possible to do something that preserves full MVCC-ness for concurrent readers, but I don't believe there is any choice but to lock out concurrent writers until the truncate commits. If you try to allow that, there's no way to keep straight whose change goes into which file. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Must be owner to truncate?
On L, 2005-07-09 at 09:47 -0400, Mike Mascari wrote: Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? should be the same as when newer transactions had used a file after a DELETE ; had been issued. Could the new file not be made to cover the next available 1GB of file space, that is a new physical file ? This could made using of same kind of machinery my proposal for concurrent index does (i.e. locks that forbid putting new tuples in certain tuple ranges) Then, if the truncating transaction commits, the N first pgysical 1GB files are removed, and just the remaining ones are used. if it aborts, the first files stay, and we just have some tuples placed sparcely starting at the next 1GB boundary. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
On Sat, Jul 09, 2005 at 11:43:52PM +0300, Hannu Krosing wrote:
Could the new file not be made to cover the next available 1GB of file
space, that is a new physical file ?
This could made using of same kind of machinery my proposal for
concurrent index does (i.e. locks that forbid putting new tuples in
certain tuple ranges)
I think your proposals are too handwavy, but there is a similar
mechanism outlined for on-line index reorganizarion, whereby new tuples
can be inserted concurrently with the reorganization, being stored on a
spill area. See
@inproceedings{DBLP:conf/sigmod/ZouS96,
author= {C. Zou and B. Salzberg},
editor= {H. V. Jagadish and Inderpal Singh Mumick},
title = {On-line Reorganization of Sparsely-populated B+trees},
booktitle = {Proceedings of the 1996 ACM SIGMOD International Conference on
Management of Data, Montreal, Quebec, Canada, June 4-6, 1996},
publisher = {ACM Press},
year = {1996},
pages = {115-124},
bibsource = {DBLP, \url{http://dblp.uni-trier.de}}
}
--
Alvaro Herrera (alvherre[a]alvh.no-ip.org)
Para tener más hay que desear menos
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: I'm strongly in favour of this patch. I am currently in this situation: 1. Web db user runs as non-superuser, non-owner. 2. I have a table of a tens of thousands of rows that I must delete entirely and rebuild every day at least (pg_trgm word list) 3. It just gets slow over time, even with autovac. 4. I can't vacuum it as i'm not the owner, and I cannot truncate it either. 5. Table has no triggers or FK's whatsoever. So, stephen frost's suggestion would be fantastic. This is a very similar situation to what I'm in, which is why I was asking for the change. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. Ah. I didn't realise that 2nd point. I don't care so much about the stronger lock in my application. Does truncate not being MVCC-safe cause problems in your situation? It certainly doesn't in mine and I expect the same is true for alot of others in the same situation. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: It's not MVCC-safe even with the AccessExclusive lock; This seems like something which should probably be fixed, You've missed the point entirely: this *cannot* be fixed, at least not without giving up the performance advantages that make TRUNCATE interesting. Alright, can we give that rather significant performance advantage to non-owners in some way then? Perhaps as an extra grant right? This is along the lines of what I was thinking, though I do see that it gets more complicated when dealing with transactions which started before the one committing the truncate (Not a problem in my case, but would have to be dealt with to be MVCC-safe): TRUNCATE is fast because it knows that it's delete'ing everything and so it just creates a new (empty) file and deletes the old file. DELETE goes through the entire file marking each record for deletion and then the system has to wait around for the vacuum'er to come through and clean up the file. New transactions using that file have to scan past all of the deleted tuples until they get vacuumed though. My thinking is along these lines: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
On Thu, 7 Jul 2005, Stephen Frost wrote: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots that were taken before the truncate operation but which don't have a lock on the table yet. The only reason it doesn't break pg_dump is that the first thing that pg_dump does is to take AccessShare locks on every table that it's going to dump. This seems like something which should probably be fixed, but which is probably too late to fix for 8.1. Of course, if we could fix this then it seems like it would be possible for us to just change 'delete from x' to behave as truncate does now given appropriate conditions. I'm not as Doesn't the lock difference between delete and truncate mean that suddenly deletes on x may or may not block concurrent selects to x (depending on whether it's a full table delete and whether x has delete triggers)? Or are you thinking that after making it MVCC safe the lock could be lessened? With the current truncate lock, it seems bad to me for users who want to do: begin; delete from x; -- do inserts and other stuff to the now empty x while still allowing access to x. Especially if whether or not you have access depends on whether there are delete triggers on x. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Must be owner to truncate?
Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be something easy enough to check for. There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Must be owner to truncate?
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be something easy enough to check for. There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. What permissions are required to lock a table? With just select, insert, update and delete on a table I can LOCK TABLE it, which acquires an ACCESS EXCLUSIVE on it and will therefore hold off anyone else from using the table till the end of my transaction anyway. So I don't see this as being a reason to disallow non-owners use of truncate. * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. Truncate is exactly a quick DELETE, in fact, DELETE could stand to learn some thing from truncate to make it suck a little less to 'delete from x;' when x is a reasonably large table. This probably wouldn't actually be all that difficult to do if there's a way to keep the old file around until all the transactions using it have completed that's not too expensive, etc. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots that were taken before the truncate operation but which don't have a lock on the table yet. The only reason it doesn't break pg_dump is that the first thing that pg_dump does is to take AccessShare locks on every table that it's going to dump. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Must be owner to truncate?
On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be something easy enough to check for. There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a truncate permission? I would find it useful, as it seems would others. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Must be owner to truncate?
* Jim C. Nasby ([EMAIL PROTECTED]) wrote: On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a truncate permission? I would find it useful, as it seems would others. That would be acceptable for me as well. I'd prefer it just work off delete, but as long as I can grant truncate to someone w/o giving them ownership rights on the table I'd be happy. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Andrew - Supernews ([EMAIL PROTECTED]) wrote: On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots that were taken before the truncate operation but which don't have a lock on the table yet. The only reason it doesn't break pg_dump is that the first thing that pg_dump does is to take AccessShare locks on every table that it's going to dump. This seems like something which should probably be fixed, but which is probably too late to fix for 8.1. Of course, if we could fix this then it seems like it would be possible for us to just change 'delete from x' to behave as truncate does now given appropriate conditions. I'm not as familiar with that area as others are; is this a very difficult thing to do? If not then I may take a look at it, it'd be a very nice improvement. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be something easy enough to check for. My thinking is to replace the existing ownercheck with: Must have delete permissions on the relation If the relation has triggers: Check that the caller is the owner of the relation, if so, then issue a NOTICE that those triggers won't be called and perform the truncate. If not the owner, then error out saying there are ON DELETE triggers and that you're not the owner. I'm strongly in favour of this patch. I am currently in this situation: 1. Web db user runs as non-superuser, non-owner. 2. I have a table of a tens of thousands of rows that I must delete entirely and rebuild every day at least (pg_trgm word list) 3. It just gets slow over time, even with autovac. 4. I can't vacuum it as i'm not the owner, and I cannot truncate it either. 5. Table has no triggers or FK's whatsoever. So, stephen frost's suggestion would be fantastic. Chris ---(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] Must be owner to truncate?
There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. Ah. I didn't realise that 2nd point. I don't care so much about the stronger lock in my application. Chris ---(end of broadcast)--- TIP 3: 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] Must be owner to truncate?
Stephen Frost [EMAIL PROTECTED] writes: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: It's not MVCC-safe even with the AccessExclusive lock; This seems like something which should probably be fixed, You've missed the point entirely: this *cannot* be fixed, at least not without giving up the performance advantages that make TRUNCATE interesting. regards, tom lane ---(end of broadcast)--- TIP 3: 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
