Re: Only owners can ANALYZE tables...seems overly restrictive

On Sun, Feb 28, 2016 at 9:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Given the amount of damage a person with write access to a table can get
> > into it seems pointless to not allow them to analyze the table after
> their
> > updates - since best practices would say that normal work with a table
> > should not be performed by an owner.
>
> > I should the check for whether a given user can or cannot analyze a table
> > should be whether the user has INSERT, UPDATE, or DELETE privileges.
>
> By that argument, we should allow anyone with any write access to do
> TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.

​TRUNCATE indeed also seems overly restrictive. But in any case we have a
GRANT for TRUNCATE. If you are saying that you'd be OK with adding a GRANT
for ANALYZE that would probably suffice.

I'd place DROP TABLE into a different category simply because it alters the
design of the database - some only owners in the database should be allowed
to do. We also don't tell people to "run DROP TABLE" so that the planner
can choose better query plans. We do tell people that after inserting or
deleting lots of data they should run ANALYZE to ensure subsequent queries
have good info to work with. Telling them to pray that (and/or wait an
indefinite period of time for) the auto-vacuum process ran - or to call
their DBA - it not a good solution to that problem.

As much as I respect your opinion I was hoping for something less trite.

ANALYZE, even on a large table (though statistics target would influence
this), is seemingly fast due to the random sampling. It also operates
fully concurrently with other activity.

David J.
​