Re: Only owners can ANALYZE tables...seems overly restrictive - Mailing list pgsql-general

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