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.
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.