Re: Why is materialized view creation a "security-restricted operation"? - Mailing list pgsql-general

Dear Postgres Community,

some questions about this 9-year-old post below.

I also stumbled over a similar case as the failing

CREATE MATERIALIZED VIEW some_view AS SELECT * FROM my_func();

. where my_func tries to create a temp table.


When writing an arbitrarily complex function my_func, I claim there are cases when you want to store intermediate
resultsinto variables. And what if the intermediate results are tables? Well, Postgres/plpgsql does not support
table-valuedvariables, so the next best choice are temp tables. 
But here we have: Creating temp tables is forbidden inside a mat view, see the mail below. Because we might have a side
effect("change of seesion state"): The creation of this very temp table. 

What to do now? Well it turns out I actually CAN create a NON-temp table. Is that what you want me to do? Really? Isn't
thisthe bigger side effect: Creating a table? 

It actually does not make sense to me, restricting one effect, while allowing the much bigger effect.

* What I actually needed is a table-valued variable. One I can use inside my function. Which shall also be local/unique
(i.e. not being used by concurrent users or sessions, or even in the call stack of the very same session). 

* The next best thing would be a temp table, local/unique in the sense as above, that gets destroyed when leaving the
function.
Invent some CREATE TEMP TABLE . ON EXIT FUNCTION DROP?
(.and wouldn't that be quite equivalent to table-valued variables?)

Any suggestions?

Thank You.


Regards,
Franz-Josef Färber


P.S.: Yes, I know SQL queries are Turing complete, using CASE and WITH RECURSIVE. So in theory you could solve anything
usingjust one SQL query, without need for any variable. But such code in general can get incomprehensible and/or
inperformant,I think. 



Re: Why is materialized view creation a "security-restricted operation"?
From:    Joshua Chamberlain <josh(at)zephyri(dot)co>
To:    Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc:    "Joshua Chamberlain *EXTERN*" <josh(at)zephyri(dot)co>, "pgsql-general(at)postgresql(dot)org"
<pgsql-general(at)postgresql(dot)org>
Subject:    Re: Why is materialized view creation a "security-restricted operation"?
Date:    2017-01-24 17:55:40
Message-ID:    CAFBoRzdU5tiJOBZW5-3MVHW68C58rpjwpeBBcBEhpMv0SLBJsA@mail.gmail.com

Views:
Thread:       2017-01-23 19:06:19 from Joshua Chamberlain <josh(at)zephyri(dot)co>    2017-01-24 11:18:34 from Albe
Laurenz<laurenz(dot)albe(at)wien(dot)gv(dot)at>     2017-01-24 17:55:40 from Joshua Chamberlain
<josh(at)zephyri(dot)co>         
Lists:    pgsql-general

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
>