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

On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> >
> > > * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > > > Given the amount of damage a person with write access to a table can
> get
> > > > into it seems pointless to not allow them to analyze the table after
> > > their
> > > > updates - since best practices would say that normal work with a
> table
> > > > should not be performed by an owner.
> > > >
> > > > 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.
> > >
> > > Realistically, ANALYZE is a background/maintenance task that autovacuum
> > > should be handling for you.
> >
> > ​Then my recent experience of adding a bunch of records and having the
> > subsequent select query take forever because the table wasn't analyzed is
> > not supposed to happen? What am I doing wrong then that autovacuum
> didn't
> > run for me?​
>
> Perhaps nothing. Making autovacuum more aggressive is a trade-off and
> evidently there weren't enough changes or perhaps not enough time for
> autovacuum to realize it needed to kick in and re-analyze the table.
> One thought about how to address that might be to have a given backend,
> which is already sending stats info to the statistic collector, somehow
> also bump autovacuum to wake it up from its sleep to go analyze the
> tables just modified. This is all very hand-wavy as I don't have time
> at the moment to run it down, but I do think it'd be good to reduce the
> need to run ANALYZE by hand after every data load.
>

​Improving it is desirable but it wouldn't preclude this desire.​

> > > > I suppose row-level-security might come into play here...
> > >
> > > Yes, you may only have access to a subset of the table.
> > >
> > >
> > ​TBH, since you cannot see the data being analyzed I don't see a security
> > implication here if you allow someone to ANALYZE the whole table even
> when
> > RLS is in place.​
>
> I wasn't looking at it from a security implication standpoint as I
> suspect that any issue there could actually be addressed, if any exist.
>
> What I was getting at is that you're making an assumption that any user
> with DML rights on the table also has enough information about the table
> overall to know when it makes sense to ANALYZE the table or not. That's
> a bit of a stretch to begin with, but when you consider that RLS may be
> involved and the user may only have access to 1% (or less) of the
> overall table, it's that much more of a reach.
>
>
​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.​

> If we had plenty more bits to allow ANALYZE to be independently
> > > GRANT'able, then maybe, but those are a limited resource.
> > >
> >
> > ​The planner and system performance seems important enough to give it
> such
> > a resource. But as I stated initially I personally believe that a user
> > with INSERT/DELETE/UPDATE permissions on a table (maybe require all
> three)
> > should also be allowed to ANALYZE said table.​
>
> I don't think requiring all three would make any sense and would,
> instead, simply be confusing. I'm not completely against your general
> idea, but let's keep it simple.
>

​Agreed.

David J.​