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

On 02/29/2016 08:13 AM, David G. Johnston wrote:
> On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost <sfrost(at)snowman(dot)net
> <mailto:sfrost(at)snowman(dot)net>>wrote:
>
> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto: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 <mailto:sfrost(at)snowman(dot)net>> wrote:
> >
> > > * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com <mailto: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.​

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$

>
> > 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.​

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com