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

On Mon, Feb 29, 2016 at 11:50 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>
​Pondering further there is likely quite a bit more to the GUC dynamics
that make re-working them then that desirable.

But I'm also still not totally convinced that our level of prohibition buys
us much safety. I recognize how this specific combination could be made to
cause problems but my gut reaction is that anyone we'd give write access to
a table would implicitly have a sufficient enough level of trust to allow
them to do both SET and ANALYZE in the rare instance they felt doing so was
necessary - and likewise would trust them not to issue SET arbitrarily but
rather just use the more useful ANALYZE command.

​We've basically prohibited ANALYZE because we cannot prohibit SETting the
complementary GUC - one which I agree ordinary users have no business
messing with​.

Are there any security (data exposure, denial of service) protections that
this prohibition is also manifesting? I'm inclined to disallow the
non-argumented (i.e., database-wide) version of ANALYZE - in fact I'd
probably make it superuser-only - as a form of parental guidance.

David J.