pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy is materialized view creation a "security-restricted operation"?
17+ messages / 9 participants
[nested] [flat]
* Why is materialized view creation a "security-restricted operation"?
@ 2017-01-23 19:06 Joshua Chamberlain <josh@zephyri.co>
0 siblings, 1 reply; 17+ messages in thread
From: Joshua Chamberlain @ 2017-01-23 19:06 UTC (permalink / raw)
To: pgsql-general
Hello,
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?
Thanks for any help you can provide.
Regards,
Joshua Chamberlain
[1]
https://www.postgresql.org/message-id/CAFjFpRcz3qKQFQo3RynfPinXdOp_42Tz%2BxCqBQdAoe061bMRSw%40mail.g...
[2]
https://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2017-01-24 11:18 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Joshua Chamberlain <josh@zephyri.co>
0 siblings, 1 reply; 17+ messages in thread
From: Albe Laurenz @ 2017-01-24 11:18 UTC (permalink / raw)
To: 'Joshua Chamberlain *EXTERN*' <josh@zephyri.co>; pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2017-01-24 17:55 Joshua Chamberlain <josh@zephyri.co>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 17+ messages in thread
From: Joshua Chamberlain @ 2017-01-24 17:55 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Joshua Chamberlain *EXTERN* <josh@zephyri.co>; 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.albe@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-01 13:11 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 3 replies; 17+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-01 13:11 UTC (permalink / raw)
To: pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
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 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.
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.
* 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 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-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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-01 15:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2026-10-01 15:23 UTC (permalink / raw)
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
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-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
>>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-01 20:13 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2 siblings, 0 replies; 17+ messages in thread
From: Laurenz Albe @ 2026-10-01 20:13 UTC (permalink / raw)
To: Färber, "Franz-Josef (StMUK)" <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
On Thu, 2026-10-01 at 13:11 +0000, Färber, Franz-Josef (StMUK) wrote:
> some questions about this 9-year-old post below.
> [ https://postgr.es/m/flat/CAFBoRzf6HwFg1jovdOrbtC6x4xKV__-t5EjSzbY2068S01pcTg%40mail.gmail.com ]
>
> 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 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.
> 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.
Creating a temporary table and creating a permanent table are not the same thing:
- it requires different permissions: TEMP on the database (which is granted to PUBLIC
by default) and CREATE on a schema (which only the owner has by default)
- the temporary schema by default is at the beginning of the search_path, so it can
easily shadow objects in other schemas
> * 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?
If you use a permanent table instead, I recommend an UNLOGGED table.
Other than that, you could use a variable that is an array of table rows
(declared as my_table[]).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-01 23:55 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron Johnson @ 2026-10-01 23:55 UTC (permalink / raw)
To: pgsql-general
On Thu, Oct 1, 2026 at 11:23 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 10/1/26 6:11 AM, Färber, Franz-Josef (StMUK) wrote:
> > * 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.
>
A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP TABLE once
(so that CREATE TEMP TABLE some_table everywhere that my_func() is
called) would also solve OP's problem.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-02 00:34 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2026-10-02 00:34 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP TABLE once
> (so that CREATE TEMP TABLE some_table everywhere that my_func() is
> called) would also solve OP's problem.
>
Per the create table docs:
"Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This
presently makes no difference in PostgreSQL and is deprecated; see
Compatibility below."
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-02 10:19 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron Johnson @ 2026-10-02 10:19 UTC (permalink / raw)
To: pgsql-general
On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP TABLE
>> once (so that CREATE TEMP TABLE some_table everywhere that my_func()
>> is called) would also solve OP's problem.
>>
>
> Per the create table docs:
>
> "Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP.
> This presently makes no difference in PostgreSQL and is deprecated; see
> Compatibility below."
>
And I'm praying for "since future versions of PostgreSQL might adopt a more
standard-compliant interpretation of their meaning" in the
Compatibility section you referenced.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-02 14:54 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Adrian Klaver @ 2026-10-02 14:54 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
On 10/2/26 3:19 AM, Ron Johnson wrote:
> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP
> TABLE once (so that CREATE TEMP TABLE some_table everywhere
> that my_func() is called) would also solve OP's problem.
>
>
> Per the create table docs:
>
> "Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
> TEMP. This presently makes no difference in PostgreSQL and is
> deprecated; see Compatibility below."
>
> And I'm praying for "since future versions of PostgreSQLmight adopt a
> more standard-compliant interpretation of their meaning" in the
> Compatibility section you referenced.
As an extension there is:
https://github.com/darold/pgtt
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-02 15:18 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 17+ messages in thread
From: Ron Johnson @ 2026-10-02 15:18 UTC (permalink / raw)
To: pgsql-general
On Fri, Oct 2, 2026 at 10:54 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 10/2/26 3:19 AM, Ron Johnson wrote:
> > On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston
> > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> >
> > On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com
> > <mailto:ronljohnsonjr@gmail.com>> wrote:
> >
> > A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP
> > TABLE once (so that CREATE TEMP TABLE some_table everywhere
> > that my_func() is called) would also solve OP's problem.
> >
> >
> > Per the create table docs:
> >
> > "Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
> > TEMP. This presently makes no difference in PostgreSQL and is
> > deprecated; see Compatibility below."
> >
> > And I'm praying for "since future versions of PostgreSQLmight adopt a
> > more standard-compliant interpretation of their meaning" in the
> > Compatibility section you referenced.
>
> As an extension there is:
>
> https://github.com/darold/pgtt
>
>
Excellent. Thanks!
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-04 19:27 Thiemo Kellner <thiemo@gelassene-pferde.biz>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2 siblings, 1 reply; 17+ messages in thread
From: Thiemo Kellner @ 2026-10-04 19:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi
Giving my two dimes. I apologise if some else has given it already. And it is quite an operations point of view and mostly based on Oracle. To the best of my knowledge, it applies to Postgres even more.
The data of DB temp tables are not visible outside of the session that has put it in. In the case of trying to find the problem of code of functions storing intermediary results, they are just not visible for the investigator such that one has to do all the steps manually to detect the point where things go wrong. In my opinion a real pita.
Cheers
Thiemo
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-04 20:24 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2026-10-04 20:24 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org
On 10/4/26 12:27 PM, Thiemo Kellner wrote:
> Hi
>
> Giving my two dimes. I apologise if some else has given it already. And
> it is quite an operations point of view and mostly based on Oracle. To
> the best of my knowledge, it applies to Postgres even more.
>
> The data of DB temp tables are not visible outside of the session that
> has put it in. In the case of trying to find the problem of code of
> functions storing intermediary results, they are just not visible for
> the investigator such that one has to do all the steps manually to
> detect the point where things go wrong. In my opinion a real pita.
I am not quite following the above.
Do you mean:
1) Not having access to the function code ?
2) Having access to function code, but not the source of data?
>
> Cheers
>
> Thiemo
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-06 06:02 Thiemo Kellner <thiemo@gelassene-pferde.biz>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 17+ messages in thread
From: Thiemo Kellner @ 2026-10-06 06:02 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
I am referring to "Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction" (https://www.postgresql.org/docs/18/sql-createtable.html). I.e that if you have to debug a production system, you cannot just look into the temptables to to pin-point where data is not the way expected. You have to recreate every intermediary result.
04.10.2026 22:24:47 Adrian Klaver <adrian.klaver@aklaver.com>:
> On 10/4/26 12:27 PM, Thiemo Kellner wrote:
>> Hi
>> Giving my two dimes. I apologise if some else has given it already. And it is quite an operations point of view and mostly based on Oracle. To the best of my knowledge, it applies to Postgres even more.
>> The data of DB temp tables are not visible outside of the session that has put it in. In the case of trying to find the problem of code of functions storing intermediary results, they are just not visible for the investigator such that one has to do all the steps manually to detect the point where things go wrong. In my opinion a real pita.
>
> I am not quite following the above.
>
> Do you mean:
>
> 1) Not having access to the function code ?
>
> 2) Having access to function code, but not the source of data?
>
>
>
>
>> Cheers
>> Thiemo
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-06 08:42 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 17+ messages in thread
From: Karsten Hilbert @ 2026-10-06 08:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Am Tue, Oct 06, 2026 at 08:20:14AM +0000 schrieb Färber@pop.gmx.net:
> Are you sure the extension pgtt would work in my case?
Did you try ?
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-06 21:50 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2026-10-06 21:50 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general <pgsql-general@lists.postgresql.org>
On 10/5/26 11:02 PM, Thiemo Kellner wrote:
> I am referring to "Temporary tables are automatically dropped at the end
> of a session, or optionally at the end of the current
> transaction" (https://www.postgresql.org/docs/18/sql-createtable.html).
> I.e that if you have to debug a production system, you cannot just look
> into the temptables to to pin-point where data is not the way expected.
> You have to recreate every intermediary result.
Does that issue not also exist with persistent tables, where the data
you are looking in the debugging stage maybe not be what was present at
the error stage?
>
> 04.10.2026 22:24:47 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 10/4/26 12:27 PM, Thiemo Kellner wrote:
>>> Hi
>>> Giving my two dimes. I apologise if some else has given it already.
>>> And it is quite an operations point of view and mostly based on
>>> Oracle. To the best of my knowledge, it applies to Postgres even more.
>>> The data of DB temp tables are not visible outside of the session
>>> that has put it in. In the case of trying to find the problem of code
>>> of functions storing intermediary results, they are just not visible
>>> for the investigator such that one has to do all the steps manually
>>> to detect the point where things go wrong. In my opinion a real pita.
>>
>> I am not quite following the above.
>>
>> Do you mean:
>>
>> 1) Not having access to the function code ?
>>
>> 2) Having access to function code, but not the source of data?
>>
>>
>>
>>
>>> Cheers
>>> Thiemo
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-07 05:51 Thiemo Kellner <thiemo@gelassene-pferde.biz>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 17+ messages in thread
From: Thiemo Kellner @ 2026-10-07 05:51 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
06.10.2026 23:50:14 Adrian Klaver <adrian.klaver@aklaver.com>:
>
> Does that issue not also exist with persistent tables, where the data you are looking in the debugging stage maybe not be what was present at the error stage?
>
To a lesser extent, depending on the design of the process. At least one has got some chance.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2026-10-07 05:51 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-23 19:06 Why is materialized view creation a "security-restricted operation"? Joshua Chamberlain <josh@zephyri.co>
2017-01-24 11:18 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2017-01-24 17:55 ` Joshua Chamberlain <josh@zephyri.co>
2026-10-01 13:11 Re: Why is materialized view creation a "security-restricted operation"? Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-01 15:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-01 23:55 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-02 00:34 ` David G. Johnston <david.g.johnston@gmail.com>
2026-10-02 10:19 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-02 14:54 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-02 15:18 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-06 08:42 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2026-10-01 20:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-04 19:27 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2026-10-04 20:24 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-06 06:02 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2026-10-06 21:50 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-07 05:51 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox