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.a...@wien.gv.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 >
