Re: Have REFRESH MATERIALIZED VIEW run as the MV owner

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(at)leadboat(dot)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