Re: [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
Stephen Frost <[EMAIL PROTECTED]> writes: > GRANT ADD COLUMN, etc, aren't maintenance commands, they're DDL > statements and as such should be the purview of the owner. TRUNCATE, > VACUUM and ANALYZE are DML commands and are commands a user of > the table would use through the normal course of inserting, updating or > deleteing data in the table. I find this reasoning fairly dubious. In particular, it's hard to argue that there is no DDL component to TRUNCATE when it effectively does an implicit disable-triggers operation. Another thing setting TRUNCATE apart from run-of-the-mill DDL operations is that it inherently violates MVCC rules (by deleting rows that should still be visible to concurrent transactions). But my real problem with the approach is that I don't see where it stops. If you're allowed to do ANALYZE, why not ALTER TABLE SET STATISTICS? If you're allowed to do TRUNCATE, why not the recently-discussed ALTER TABLE SET RELIABILITY? And how about CLUSTER? All of these could be pretty useful for some applications not too far removed from yours. And there will be someone wanting a bit for DISABLE/ENABLE TRIGGER coming along right afterwards. Must we implement a separate nonstandard privilege bit for every operation that someone comes up and wants a bit for, if they have the necessary cut-and-paste skill to submit a patch for it? I'd feel happier about an approach that adds *one* privilege bit covering a range of operations that we agree to be useful. This will avoid chewing a disproportionate amount of ACL storage space, and it will force us to confront the decision about which operations are out as well as which are in. One last point: -patches is not the place for this type of discussion. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
* daveg ([EMAIL PROTECTED]) wrote: > We rely heavily on truncate as delete for large numbers of rows is very > costly. An example, we copy_in batches of rows from several sources through > the day to a "pending work" table, with another process periodically > processing the rows and sweeping them into a history table. The sweep > leaves an empty "pending work" table. Truncate is very efficient for this > pattern. > > However it means that all our jobs have to run with more permissions than > they really should have as there is no way to grant "truncate". If giving > truncate its very own permission is too wasteful of permission bits, perhaps > having truncate be the same as "delete" for permissions purposes would work. Sounds very similar to my use-case, except my users just have to suffer with delete because I don't want to grant them additional permissions. Having truncate act off of delete isn't actually an option unfortunately. This is because truncate skips triggers (probably not an issue for you, certainly not one for me, but a problem with doing it in the general case). I'm not sure about you, but I know that I'd like to be able to do: TRUNCATE, insert/copy data, ANALYZE without having to give all the other permissions associated with ownership. > Alternatively a separate "whole table operations" permision might cover > truncate and some of the alter type things too. Of course table owner does > this, but that is what I don't want everyone to be require to have. I'm not entirely sure if that'd be better or not.. It would involve changing the structure of the ACLs to have two sets for each relation and you'd have to sometimes look at one, sometimes at the other, and possible both in some cases... Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > The following patch implements individual privileges for TRUNCATE, > > VACUUM and ANALYZE. Includes documentation and regression test > > updates. Resolves TODO item 'Add a separate TRUNCATE permission'. > > > At least the 'no one interested has written a patch' argument is gone > > now, fire away with other comments/concerns. :) > > I have a very serious problem with the idea of inventing individual > privilege bits for every maintenance command in sight. That does not > scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN > as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences", > or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch, > or a dozen other cases that I could name without stopping for breath? GRANT ADD COLUMN, etc, aren't maintenance commands, they're DDL statements and as such should be the purview of the owner. TRUNCATE, VACUUM and ANALYZE are DML commands and are commands a user of the table would use through the normal course of inserting, updating or deleteing data in the table. > The proposed patch eats three of the five available privilege bits (that > is, available without accepting the distributed cost of enlarging ACL > bitmasks), and you've made no case at all why we should spend that > limited resource in this particular fashion. I've shown a specific use-case for this. It's been asked for before by others. I've shown why these particular ones make sense (while 'ADD COLUMN', etc, don't). If we come up with more Postgres-specific DML statements which aren't covered by other grants (which doesn't seem terribly likely at this point) then we should add those. I could see making VACUUM and ANALYZE use the same bit (since one implies the other) but I'm not really a big fan of that and I don't see any other need for these bits coming down the line anytime soon. Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
On Tue, Jan 03, 2006 at 11:32:01PM -0500, Tom Lane wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > The following patch implements individual privileges for TRUNCATE, > > VACUUM and ANALYZE. Includes documentation and regression test > > updates. Resolves TODO item 'Add a separate TRUNCATE permission'. > > > At least the 'no one interested has written a patch' argument is gone > > now, fire away with other comments/concerns. :) > > I have a very serious problem with the idea of inventing individual > privilege bits for every maintenance command in sight. That does not > scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN > as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences", > or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch, > or a dozen other cases that I could name without stopping for breath? > > The proposed patch eats three of the five available privilege bits (that > is, available without accepting the distributed cost of enlarging ACL > bitmasks), and you've made no case at all why we should spend that > limited resource in this particular fashion. We rely heavily on truncate as delete for large numbers of rows is very costly. An example, we copy_in batches of rows from several sources through the day to a "pending work" table, with another process periodically processing the rows and sweeping them into a history table. The sweep leaves an empty "pending work" table. Truncate is very efficient for this pattern. However it means that all our jobs have to run with more permissions than they really should have as there is no way to grant "truncate". If giving truncate its very own permission is too wasteful of permission bits, perhaps having truncate be the same as "delete" for permissions purposes would work. Alternatively a separate "whole table operations" permision might cover truncate and some of the alter type things too. Of course table owner does this, but that is what I don't want everyone to be require to have. -dg -- David Gould [EMAIL PROTECTED] If simplicity worked, the world would be overrun with insects. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
Stephen Frost <[EMAIL PROTECTED]> writes: > The following patch implements individual privileges for TRUNCATE, > VACUUM and ANALYZE. Includes documentation and regression test > updates. Resolves TODO item 'Add a separate TRUNCATE permission'. > At least the 'no one interested has written a patch' argument is gone > now, fire away with other comments/concerns. :) I have a very serious problem with the idea of inventing individual privilege bits for every maintenance command in sight. That does not scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences", or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch, or a dozen other cases that I could name without stopping for breath? The proposed patch eats three of the five available privilege bits (that is, available without accepting the distributed cost of enlarging ACL bitmasks), and you've made no case at all why we should spend that limited resource in this particular fashion. regards, tom lane ---(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
[PATCHES] TRUNCATE, VACUUM, ANALYZE privileges
Greetings, The following patch implements individual privileges for TRUNCATE, VACUUM and ANALYZE. Includes documentation and regression test updates. Resolves TODO item 'Add a separate TRUNCATE permission'. Created off of current (2005/01/03) CVS TIP. At least the 'no one interested has written a patch' argument is gone now, fire away with other comments/concerns. :) Thanks, Stephen Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.301 diff -c -r1.301 func.sgml *** doc/src/sgml/func.sgml 28 Dec 2005 01:29:58 - 1.301 --- doc/src/sgml/func.sgml 4 Jan 2006 03:38:01 - *** *** 8961,8968 The desired access privilege type is specified by a text string, which must evaluate to one of the values SELECT, INSERT, UPDATE, ! DELETE, RULE, REFERENCES, or ! TRIGGER. (Case of the string is not significant, however.) An example is: SELECT has_table_privilege('myschema.mytable', 'select'); --- 8961,8969 The desired access privilege type is specified by a text string, which must evaluate to one of the values SELECT, INSERT, UPDATE, ! DELETE, RULE, REFERENCES, ! TRIGGER, TRUNCATE, VACUUM, or ! ANALYZE. (Case of the string is not significant, however.) An example is: SELECT has_table_privilege('myschema.mytable', 'select'); Index: doc/src/sgml/information_schema.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v retrieving revision 1.23 diff -c -r1.23 information_schema.sgml *** doc/src/sgml/information_schema.sgml8 Dec 2005 20:48:10 - 1.23 --- doc/src/sgml/information_schema.sgml4 Jan 2006 03:38:01 - *** *** 2395,2401 Type of the privilege: SELECT, DELETE, INSERT, UPDATE, REFERENCES, !RULE, or TRIGGER --- 2395,2403 Type of the privilege: SELECT, DELETE, INSERT, UPDATE, REFERENCES, !RULE, TRIGGER, !TRUNCATE, VACUUM, or !ANALYZE. *** *** 3643,3649 Type of the privilege: SELECT, DELETE, INSERT, UPDATE, REFERENCES, !RULE, or TRIGGER --- 3645,3653 Type of the privilege: SELECT, DELETE, INSERT, UPDATE, REFERENCES, !RULE, TRIGGER, !TRUNCATE, VACUUM, or !ANALYZE. Index: doc/src/sgml/user-manag.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/user-manag.sgml,v retrieving revision 1.33 diff -c -r1.33 user-manag.sgml *** doc/src/sgml/user-manag.sgml20 Oct 2005 19:18:00 - 1.33 --- doc/src/sgml/user-manag.sgml4 Jan 2006 03:38:01 - *** *** 296,301 --- 296,302 There are several different kinds of privilege: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, +TRUNCATE, VACUUM, ANALYZE, CREATE, TEMPORARY, EXECUTE, and USAGE. For more information on the different types of privileges supported by Index: doc/src/sgml/ref/grant.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v retrieving revision 1.50 diff -c -r1.50 grant.sgml *** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 - 1.50 --- doc/src/sgml/ref/grant.sgml 4 Jan 2006 03:38:02 - *** *** 20,26 ! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] --- 20,26 ! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | TRUNCATE | VACUUM | ANALYZE } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] *** *** 205,210 --- 205,243 + TRUNCATE + + +Allows the truncation of the specified table. NOTE: This will NOT execute +triggers defined on the table. Additionally, this requires locking the table +exclusivly because it does not follow normal MVCC rules. See the statement. + + + + + + VACUUM + + +Allows the vacuuming of the specified table. Note: This implies ANALYZE +rights. See the +statement. + + + + + + ANALYZE + + +Allows the analyzing of the specified table. See the statement. +