From: =?iso-8859-1?Q?F=E4rber=2C_Franz-Josef_=28StMUK=29?=
	<Franz-Josef.Faerber@stmuk.bayern.de>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
CC: "Haupt, Matthias (StMUK)" <Matthias.Haupt@stmuk.bayern.de>
Subject: Re: Why is materialized view creation a "security-restricted
 operation"?
Thread-Topic: Re: Why is materialized view creation a "security-restricted
 operation"?
Thread-Index: Ad1Roy5bDQ+Mv6wTTTiUHeJSrQLucQ==
Date: Thu, 1 Oct 2026 13:11:52 +0000
Message-ID: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
Accept-Language: de-DE, en-US
Content-Language: de-DE
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/77826d7580a9488ca28eb4bcfe94aa37%40stmuk.bayern.de>
Precedence: bulk

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 cas=
es when you want to store intermediate results into variables. And what if =
the intermediate results are tables? Well, Postgres/plpgsql does not suppor=
t table-valued variables, 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 sta=
te"): The creation of this very temp table.

What to do now? Well it turns out I actually CAN create a NON-temp table. I=
s that what you want me to do? Really? Isn't this the bigger side effect: C=
reating a table?

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

* What I actually needed is a table-valued variable. One I can use inside m=
y function. Which shall also be local/unique (i. e. not being used by concu=
rrent 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 a=
bove, 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=E4rber


P.S.: Yes, I know SQL queries are Turing complete, using CASE and WITH RECU=
RSIVE. So in theory you could solve anything using just one SQL query, with=
out need for any variable. But such code in general can get incomprehensibl=
e 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 opera=
tion"?
Date:	2017-01-24 17:55:40
Message-ID:	CAFBoRzdU5tiJOBZW5-3MVHW68C58rpjwpeBBcBEhpMv0SLBJsA@mail.gmail.=
com

Views:=09
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)zephyr=
i(dot)co>        =20
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 clea=
r
> 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 make=
s
> 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
>