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.
