Re: TRUNCATE, VACUUM, ANALYZE privileges

* daveg (daveg(at)sonic(dot)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