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

On Mon, Feb 29, 2016 at 10:36 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> ​So the typical user doesn't know or even care that what they just did
>> needs to be analyzed. The situation is no worse than it is today. But
>> as someone who writes many scripts and applications to perform bulk
>> writing and data analysis I'd like those scripts to use restricted
>> authorization credentials while still being able to run ANALYZE between
>> performing the bulk DML and the running the SELECT statements needed to
>> get the newly generated data out of the database.​
>>
>
> Maybe?:
>
> CREATE OR REPLACE FUNCTION public.analyze_test(tbl_name character varying)
> RETURNS void
> LANGUAGE plpgsql
> SECURITY DEFINER
> AS $function$
> BEGIN
> EXECUTE 'ANALYZE ' || quote_ident(tbl_name);
> END;
> $function$
>
>
​Yes, a security definer function - and setting execute permissions
appropriately - would work. But it is a hack to work around a restriction
that, in theory, need not exist. I understand that our implementation -
namely the presence of a publically visible GUC and uninhibitied SET usage​

​- may make reality more complicated than this.

I guess I don't see why anyone other than the database owner and superuser
(if that, it probably could be made to be fixed at startup) should be
allowed to SET default_statistics_target. If a table owner wants to play
with different levels they can always just ALTER TABLE SET - which has the
benefit (though maybe this is undesirable in some instances...) of making
the alteration effective during auto-vacuum runs.​

ANALYZE is something that needs to happen frequently and commonly on a
running system for proper operation. In comparison the statistic targets
are basically frozen values aside from periods of experimentation. We have
our priorities backwards if SET is preventing more user-friendly usage of
ANALYZE.

David J.