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 | CAKFQuwbJOe4qy5xsCeeQ+556fssN-Y6S9TMv-FMzqoKvCJ4Hcw@mail.gmail.com Whole thread Raw |
| In response to | Re: Only owners can ANALYZE tables...seems overly restrictive (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| Responses |
Re: Only owners can ANALYZE tables...seems overly
restrictive
(Albe Laurenz <laurenz.albe@wien.gv.at>)
|
| List | pgsql-general |
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.
pgsql-general by date: