* David G. Johnston (david.g.johnston@xxxxxxxxx) wrote: > On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > > > * David G. Johnston (david.g.johnston@xxxxxxxxx) 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. > > > 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. > 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. Thanks! Stephen
Attachment:
signature.asc
Description: Digital signature
