Re: Why is materialized view creation a "security-restricted operation"?

Thank you for the explanation! That's extremely helpful. It also makes
sense now why my function can create a regular table even if not a
temporary one. It seems a little strange that it doesn't apply to VIEWs as
well, as I imagine selecting from a view would have the same potential for
unexpected side-effects. But if REFRESH MATERIALIZED VIEW is generally used
in higher-privilege session, I guess that could make sense. I'll just have
to adjust my code a bit.

Thanks,
Joshua Chamberlain

On Tue, Jan 24, 2017 at 3:18 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> Joshua Chamberlain wrote:
> > I see this has been discussed briefly before[1], but I'm still not clear
> on what's happening and why.
> >
> > I wrote a function that uses temporary tables in generating a result
> set. I can use it when creating
> > tables or views, e.g.,
> > CREATE TABLE some_table AS SELECT * FROM my_func();
> > CREATE VIEW some_view AS SELECT * FROM my_func();
> >
> > But creating a materialized view fails:
> > CREATE MATERIALIZED VIEW some_view AS SELECT * FROM my_func();
> >
> > ERROR: cannot create temporary table within security-restricted
> operation
> >
> >
> > The docs explain that this is expected[2], but not why. On the contrary,
> this is actually quite
> > surprising to me, given that tables and views work just fine. What makes
> a materialized view so
> > different? Are there any plans to make this more consistent?
>
> There is a comment in the source that explains it quite well:
>
> /*
> * Security check: disallow creating temp tables from
> security-restricted
> * code. This is needed because calling code might not expect
> untrusted
> * tables to appear in pg_temp at the front of its search path.
> */
>
> "Security-restricted" is explained in this comment:
>
> * SECURITY_RESTRICTED_OPERATION indicates that we are inside an operation
> * that does not wish to trust called user-defined functions at all. This
> * bit prevents not only SET ROLE, but various other changes of session
> state
> * that normally is unprotected but might possibly be used to subvert the
> * calling session later. An example is replacing an existing prepared
> * statement with new code, which will then be executed with the outer
> * session's permissions when the prepared statement is next used. Since
> * these restrictions are fairly draconian, we apply them only in contexts
> * where the called functions are really supposed to be side-effect-free
> * anyway, such as VACUUM/ANALYZE/REINDEX.
>
>
> The idea here is that if you run REFRESH MATERIALIZED VIEW,
> you don't want it to change the state of your session.
> In this case, a new temporary table with the same name as a normal table
> might suddenly get used by one of your queries.
>
> I guess that the problem is probably more relevant here that in other
> places
> because REFRESH MATERIALIZED VIEW is likely to be regularly called in
> sessions
> with high privileges.
>
> Yours,
> Laurenz Albe
>