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

On Mon, Feb 29, 2016 at 10:35 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>
wrote:

> On 02/29/2016 09:09 AM, David G. Johnston wrote:
>
>> ​
>> 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.
>> ​
>>
>> Only table owners should be allowed to execute ALTER TABLE while, in my
>> opinion, anyone with write capabilities on a table should be allowed to
>> execute ANALYZE.​ I would accept a GRANT permission if that could get
>> committed but I find the status-quo mildly annoying.
>>
>
> I think a better question at this point is: What is the problem you are
> trying to solve? Think about the following:
>
> 1. When you run ANALYZE it will update the statistics.
> 2. Anyone can run SET, which means that if any user can run ANALYZE, any
> user can greatly modify the statistics.
>

​OK - so the true problem has been identified.​

3. This can already be handled by GRANT:
>
> * psql -U jd -h localhost;
> * create table foo (id text);
> * create role jd_role;
> * alter table foo owner to jd_role;
> * grant jd_role to boo;
> * \c jd boo
> * analyze foo;

​I dislike the fact that this solution involves giving someone who only
cares about DML the capability to also perform DDL.

Given these two things it seems the least difficult solution that doesn't
make things any worse is to make "ANALYZE" grantable. If you were going to
give the user owner permissions anyway then having a less-inclusive
permission cannot hurt.

​The whole allowing session-local statistic targets seems suspect but I
suppose it could be useful for development.​ I don't imagine it being very
helpful in production since the autovacuum process is eventually just going
to reset them back using the server default target at some point. Maybe
for temporary tables which themselves are session-local.

David J.