Re: TRUNCATE, VACUUM, ANALYZE privileges

On Tue, Jan 03, 2006 at 11:32:01PM -0500, Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)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(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.