Thread: 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
Attachment
Stephen Frost <sfrost@snowman.net> 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
On Tue, Jan 03, 2006 at 11:32:01PM -0500, Tom Lane wrote: > Stephen Frost <sfrost@snowman.net> 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 daveg@sonic.net If simplicity worked, the world would be overrun with insects.
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> 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
Attachment
* daveg (daveg@sonic.net) 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
Attachment
Stephen Frost <sfrost@snowman.net> 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