Re: [GENERAL] Why is materialized view creation a "security-restrictedoperation"?

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