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

On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> John R Pierce wrote:
> > On 2/28/2016 8:58 PM, Tom Lane wrote:
> >>>> 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.
>
> > I don't see why anyone with delete privileges shouldn't be able to
> > truncate (after all, thats the same as deleting all records).
> >
> > analyze has arguably fewer side effects, its a performance enhancement,
> > its neither altering the schema or changing the data.
>
> In a production environment you don't want a user to change your table
> statistics.
>
> They could just set default_statistics_target to something stupid,
> run ANALYZE and wreck the statistics for everyone.
> And then come back to the DBA and complain that things don't work.
>
> We have a policy that users are not table owners, and with the
> current behaviour we can be certain that any bad table statistics
> are the fault of the DBA or wrong configuration.
>

​Setting default_statistics_target and running ANALYZE are two entirely
different things.

As it stands, from the standpoint of a DBA, it isn't much different for
autovacuum to run ANALYZE compared to having a user run ANALYZE. In both
cases the DBA's job is to ensure that the act of running ANALYZE is
properly configured. All this does is allows an informed user to run
ANALYZE when they suspect they performed sufficient changes to the table -
just in case the thresholds for autovacuum are not met.​

​I really don't care to prevent legitimate uses of ANALYZE just because
someone might do something stupid like.

INSERT INTO table VALUES (1);
ANALYZE;
INSERT INTO table VALUES(2);
ANALYZE;
etc...

There is enough gap between the volume of changes needed for auto-ANALYZE
to kick in and a sufficiently large insertion of unique data to cause the
planner to fail that allowing a user to act has merit. I'm looking for
downsides and still haven't seen any that are serious enough that a
well-meaning user can accidentally perform to hurt the system.

The only obvious thing they can do is run:

ANALYZE;
ANALYZE;
ANALYZE;

over and over again - but I'd call that malicious and they can already do
worse.

For me it boils down to that we already have an auto-vacuum daemon so we've
given up strict control of when the statistics are refreshed. We should
acknowledge that such a mechanism is not perfect - which we do by having
ANALYZE - but then also recognize that the user causing the statistics to
become stale, if permissions are securely provisioned, has no way to
correct the deficiency themselves but must rely upon a heuristic or a DBA.
To me that is overly restrictive, discourages good security practices, and
gains little to nothing in way of protecting the system.

David J.