On 10/1/26 6:11 AM, Färber, Franz-Josef (StMUK) wrote:
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.
Postgres version?
When writing an arbitrarily complex function my_func, I claim there are cases
when you want to store intermediate results into variables. And what if the
intermediate results are tables? Well, Postgres/plpgsql does not support
table-valued variables, so the next best choice are temp tables.
Code example of what you are trying to achieve.
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 this the bigger side effect:
Creating a table?
It actually does not make sense to me, restricting one effect, while allowing
the much bigger effect.
From previous post:
"
/*
* 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.
*/
[...]
In this case, a new temporary table with the same name as a normal table
might suddenly get used by one of your queries.
"
So the effect is different.
* 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.
?:
BEGIN;
CREATE TEMP TABLE some_table ...
CREATE MATERIALIZED VIEW some_view AS SELECT * FROM my_func();
--Where function uses the table.
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 using just 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-3mvhw68c58rpjwpebbcbehpmv0slb...@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
--
Adrian Klaver
[email protected]
