Thread: Have REFRESH MATERIALIZED VIEW run as the MV owner

REFRESH MATERIALIZED VIEW should temporarily switch the current user ID to the
MV owner.  REINDEX and VACUUM do so to let privileged users safely maintain
objects owned by others, and REFRESH MATERIALIZED VIEW belongs in that class
of commands.  The MV query then runs as a "security-restricted operation",
which forbids a few commands.  Most, e.g. UNLISTEN, are unlikely to arise in
practice.  The most interesting restriction is probably CREATE TEMP TABLE.
Consider a function that creates and later drops a temporary table that it
uses for intermediate storage during a complicated calculation.  That function
will no longer work in a MV query.  As a workaround, modify the function to
use a permanent table as its work area.

See attached patch.  The similar behavior of REINDEX et al. is undocumented.
Users are a bit more likely to notice limitations in the context of MVs, so I
added a brief documentation mention.  Seeing that this narrows the range of
valid MV queries, I bring it up now so MVs can debut with the restrictions
already in place.

Thanks,
nm

--
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com

On Fri, Jul 5, 2013 at 9:45 AM, Noah Misch <noah@leadboat.com> wrote:
> REFRESH MATERIALIZED VIEW should temporarily switch the current user ID to the
> MV owner.  REINDEX and VACUUM do so to let privileged users safely maintain
> objects owned by others, and REFRESH MATERIALIZED VIEW belongs in that class
> of commands.

I was trying to understand why this is safe for a while.  REINDEX and
VACUUM make sense to me because they never contain side-effect as far
as I know, but MV can contain some volatile functions which could have
some unintended operation that shouldn't be invoked by no one but the
owner.  For example, if the function creates a permanent table per
call and doesn't clean it up, but later some other maintenance
operation is supposed to clean it up, and the owner schedules REFRESH
and maintenance once a day.  A non-owner user now can refresh it so
many times until the disk gets full.  Or is that operation supposed to
be restricted by the security context you are adding?

--
Hitoshi Harada

On Fri, Jul 05, 2013 at 11:18:50PM -0700, Hitoshi Harada wrote:
> On Fri, Jul 5, 2013 at 9:45 AM, Noah Misch <noah@leadboat.com> wrote:
> > REFRESH MATERIALIZED VIEW should temporarily switch the current user ID to the
> > MV owner.  REINDEX and VACUUM do so to let privileged users safely maintain
> > objects owned by others, and REFRESH MATERIALIZED VIEW belongs in that class
> > of commands.
> 
> I was trying to understand why this is safe for a while.  REINDEX and
> VACUUM make sense to me because they never contain side-effect as far
> as I know, but MV can contain some volatile functions which could have
> some unintended operation that shouldn't be invoked by no one but the
> owner.  For example, if the function creates a permanent table per
> call and doesn't clean it up, but later some other maintenance
> operation is supposed to clean it up, and the owner schedules REFRESH
> and maintenance once a day.  A non-owner user now can refresh it so
> many times until the disk gets full.

I'm not proposing to expand the set of people *permitted* to refresh the MV.
That's still limited to the owning role (including other roles acquiring that
role by membership) and superusers.  My goal is to make it safe for a
superuser to refresh any MV, much like we've made it safe for a superuser to
REINDEX any index.

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com