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

On Mon, Feb 29, 2016 at 10:39 AM, 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:
> > On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> > wrote:
> >> See http://www.postgresql.org/docs/current/static/planner-stats.html
> >> "The amount of information stored in pg_statistic by ANALYZE, in
> >> particular the
> >> maximum number of entries in the most_common_vals and histogram_bounds
> >> arrays
> >> for each column, can be set on a column-by-column basis using the
> >> ALTER TABLE SET STATISTICS command, or globally by setting the
> >> default_statistics_target configuration variable."
>
> > Being able to run ANALYZE on a table in no way implies that ​I should be
> > allowed to run ALTER TABLE SET STATISTICS on the same.
>
> You're missing the point. If the table owner has *not* run ALTER TABLE
> SET STATISTICS, which surely is the typical situation, then whoever runs
> ANALYZE can control the volume of stats generated by setting
> default_statistics_target locally in his session. Thus, if we allow
> non-owners to run ANALYZE, they'd be able to mess things up by setting
> the stats target either much lower or much higher than the table owner
> expected. ("Much higher" would be bad in a different way than "much
> lower", but still bad.)
>

​​Yes, this particular implementation detail escaped me at first. I've
since posted new thoughts having taking this mis-feature into account.

> I imagine this could be addressed by some rule about how if you don't
> own the table then your default_statistics_target is overridden by
> the global setting, but that would be a mess both conceptually and
> implementation-wise.
>

​It seems easy enough to simply disallow session-local changes to this
GUC...but barring that this does weigh the decision toward having a GRANT
ANALYZE on TABLE since the issue isn't running the ANALYZE but rather its
interaction with SET default_statistics_target. An explicit permission
allows the table and/or database to choose to provide a user this
capability if desired without also requiring the user to become a
full-blown owner of the table and thus able to make even bigger and more
permanent changes - including altering the default_statistics_target for
the table permanently.

David J.
​