Re: Only owners can ANALYZE tables...seems overly restrictive - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: Only owners can ANALYZE tables...seems overly restrictive |
| Date | |
| Msg-id | CAKFQuwaV_MudBtwR9zpjkmBPbtHBR46DqFZgfLu80-cg3unpww@mail.gmail.com Whole thread Raw |
| In response to | Re: Only owners can ANALYZE tables...seems overly restrictive ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-general |
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz <laurenz.albe@wien.gv.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.
pgsql-general by date: