Re: Why is materialized view creation a "security-restricted operation"?

Поиск
Список
Период
Сортировка
Искать

Re: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@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 
To:	Albe Laurenz 
Cc:	"Joshua Chamberlain *EXTERN*" , "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     2017-01-24 11:18:34 from Albe Laurenz      2017-01-24 17:55:40 from Joshua Chamberlain          
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 
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
>



Re: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
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 
> To:	Albe Laurenz 
> Cc:	"Joshua Chamberlain *EXTERN*" , "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     2017-01-24 11:18:34 from Albe Laurenz      2017-01-24 17:55:40 from Joshua Chamberlain 
> 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 
> 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


Re: Why is materialized view creation a "security-restricted operation"?

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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


Re: Why is materialized view creation a "security-restricted operation"?

От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
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.

Re: Why is materialized view creation a "security-restricted operation"?

От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
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.


Re: Why is materialized view creation a "security-restricted operation"?

От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
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!

Re: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/2/26 3:19 AM, Ron Johnson wrote:
> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston 
> > wrote:
> 
>     On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson      > 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 , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Why is materialized view creation a "security-restricted operation"?

От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
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!

Re: Why is materialized view creation a "security-restricted operation"?

От:
Thiemo Kellner <thiemo@gelassene-pferde.biz>
Дата:
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

Re: Why is materialized view creation a "security-restricted operation"?

От:
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


Re: Why is materialized view creation a "security-restricted operation"?

От:
Thiemo Kellner <thiemo@gelassene-pferde.biz>
Дата:
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

AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Are you sure the extension pgtt would work in my case?

https://github.com/darold/pgtt#how-the-extension-really-works says:

"the first access to the table using a SELECT, UPDATE or DELETE statement will produce the creation of a temporary table"

Well, my original point was: Creation of a temporary table is prohibited due to "security-restricted operation".


Regards,
Franz-Josef Färber


-----Ursprüngliche Nachricht-----
Von: Adrian Klaver  
Gesendet: Freitag, 2. Oktober 2026 16:55
An: Ron Johnson ; pgsql-general 
Betreff: Re: Why is materialized view creation a "security-restricted operation"?

On 10/2/26 3:19 AM, Ron Johnson wrote:
> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston 
> > wrote:
> 
>     On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson      > 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 , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Why is materialized view creation a "security-restricted operation"?

От:
Karsten Hilbert <Karsten.Hilbert@gmx.net>
Дата:
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


Re: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:


On 10/6/26 1:20 AM, Färber, Franz-Josef (StMUK) wrote:
> Are you sure the extension pgtt would work in my case?
> 
> https://github.com/darold/pgtt#how-the-extension-really-works says:
> 
> "the first access to the table using a SELECT, UPDATE or DELETE statement will produce the creation of a temporary table"
> 
> Well, my original point was: Creation of a temporary table is prohibited due to "security-restricted operation".

I tried your MATERIALIZED VIEW with pgtt and ran into the temporary 
table issue. I tried also against the 'template' table behind the 
temporary tables and got a little further but the behavior was unstable. 
I suspect a bug that allowed me to get that far.


Questions:

1) Why the MATERIALIZED VIEW?

2) Can you provide an overview in pseudo code, including the part in the 
function, of the process you are trying create?

> 
> 
> Regards,
> Franz-Josef Färber
> 
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver 
> Gesendet: Freitag, 2. Oktober 2026 16:55
> An: Ron Johnson ; pgsql-general 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> On 10/2/26 3:19 AM, Ron Johnson wrote:
>> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston
>> > wrote:
>>
>>      On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson >      > 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 , and butter sauce.
>> Don't boil me, I'm still alive.
>>  lobster!
> 
> 



Re: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:


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 :
> 
>> 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



Re: Why is materialized view creation a "security-restricted operation"?

От:
Thiemo Kellner <thiemo@gelassene-pferde.biz>
Дата:
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.

AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Thanks for trying that out.
Well, failed, as I expected.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver  
Gesendet: Dienstag, 6. Oktober 2026 23:38
An: Färber, Franz-Josef (StMUK) ; pgsql-general@postgresql.org
Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?



On 10/6/26 1:20 AM, Färber, Franz-Josef (StMUK) wrote:
> Are you sure the extension pgtt would work in my case?
> 
> https://github.com/darold/pgtt#how-the-extension-really-works says:
> 
> "the first access to the table using a SELECT, UPDATE or DELETE statement will produce the creation of a temporary table"
> 
> Well, my original point was: Creation of a temporary table is prohibited due to "security-restricted operation".

I tried your MATERIALIZED VIEW with pgtt and ran into the temporary table issue. I tried also against the 'template' table behind the temporary tables and got a little further but the behavior was unstable. 
I suspect a bug that allowed me to get that far.


Questions:

1) Why the MATERIALIZED VIEW?

2) Can you provide an overview in pseudo code, including the part in the function, of the process you are trying create?

> 
> 
> Regards,
> Franz-Josef Färber
> 
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver 
> Gesendet: Freitag, 2. Oktober 2026 16:55
> An: Ron Johnson ; pgsql-general 
> 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> On 10/2/26 3:19 AM, Ron Johnson wrote:
>> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston 
>> > wrote:
>>
>>      On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson >      > 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 , and butter sauce.
>> Don't boil me, I'm still alive.
>>  lobster!
> 
> 

AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Sorry for my late reply. Thanks for pointing some stuff out.

> Other than that, you could use a variable that is an array of table rows (declared as my_table[]).
A bit clumsy but yes, thank you.

I appreciate PostgreSQL, but what sometimes baffles me is that SQL (or is it PostgreSQL) is ... what should I call it? Lacking modularity / orthogonality? Simply put: When you want to combine different parts, you will meet obstacles. More obstacles than one is accustomed to when coming from other languages. Imperative or functional programming languages, that is.

If I need some temporary space for structured data and am restricted to avoid a temp table; if I am then forced to use a global table (which not only can be seen in other connections, but might introduce concurrency issues). Then work around using arrays. If even applicable.

Because what I haven't said yet and should admit now, the temp table gets filled with COPY ... FROM PROGRAM.

The next obstacle which occurs strangely to me is that COPY ... FROM PROGRAM only can dump data into tables. One cannot use it like other table-like objects, like a view, One cannot COPY ... FROM PROGRAM into an array.

Postgres offers again no feasible way for what I was trying to archieve.


Ok, I think some people get the point that I claim SQL / Postgres is lacking modularity / orthogonality in the way as I tried to define above.

And I think some people want to dive into my special use case, perhaps consider it as kind-of-insane. So let me explain. And perhaps somebody can give good advice.

* I am running Postgres in a container.
* I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
* I took it as good advice to pass environment variables into the container, and to pass passwords as single files, in mounted container directory /run/secrets/ .
* Inside postgres I am trying to load that data, using memoization pattern. For example using CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'. This part is encapsulated as a function.

* Now there was one point where I was using a MATERIALIZED VIEW accessing that function. And well, that failed.


What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN TABLE, but this way it isn't parametrizable. I would have to create as many foreign tables as I have passwords / environment variables, I think.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe  
Gesendet: Donnerstag, 1. Oktober 2026 22:13
An: Färber, Franz-Josef (StMUK) ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: Why is materialized view creation a "security-restricted operation"?

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__-t5EjSzbY2068S
> 01pcTg%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

Re: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:


On 10/12/26 7:59 AM, Färber, Franz-Josef (StMUK) wrote:
> Sorry for my late reply. Thanks for pointing some stuff out.
> 
>> Other than that, you could use a variable that is an array of table rows (declared as my_table[]).
> A bit clumsy but yes, thank you.
> 
> I appreciate PostgreSQL, but what sometimes baffles me is that SQL (or is it PostgreSQL) is ... what should I call it? Lacking modularity / orthogonality? Simply put: When you want to combine different parts, you will meet obstacles. More obstacles than one is accustomed to when coming from other languages. Imperative or functional programming languages, that is.
> 
> If I need some temporary space for structured data and am restricted to avoid a temp table; if I am then forced to use a global table (which not only can be seen in other connections, but might introduce concurrency issues). Then work around using arrays. If even applicable.
> 
> Because what I haven't said yet and should admit now, the temp table gets filled with COPY ... FROM PROGRAM.
> 
> The next obstacle which occurs strangely to me is that COPY ... FROM PROGRAM only can dump data into tables. One cannot use it like other table-like objects, like a view, One cannot COPY ... FROM PROGRAM into an array.
> 
> Postgres offers again no feasible way for what I was trying to archieve.
> 
> 
> Ok, I think some people get the point that I claim SQL / Postgres is lacking modularity / orthogonality in the way as I tried to define above.
> 
> And I think some people want to dive into my special use case, perhaps consider it as kind-of-insane. So let me explain. And perhaps somebody can give good advice.

Something like this:

cat test.csv
id,type,active
1,dog,t
2,cat,f
3,fish,t

duckdb
DuckDB v1.4.4 (Andium) 6ddac802ff
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
D create temp table test_tbl as select * from 'test.csv';
D select * from test_tbl;
┌───────┬─────────┬─────────┐
│  id   │  type   │ active  │
│ int64 │ varchar │ boolean │
├───────┼─────────┼─────────┤
│     1 │ dog     │ true    │
│     2 │ cat     │ false   │
│     3 │ fish    │ true    │
└───────┴─────────┴─────────┘

Not exactly sure how this might fit into your scheme, see below for more 
information:

https://duckdb.org/docs/current/data/overview
https://duckdb.org/docs/current/data/csv/overview

https://duckdb.org/docs/current/clients/overview

https://duckdb.org/docs/current/core_extensions/postgres/overview

> 
> * I am running Postgres in a container.
> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
> * I took it as good advice to pass environment variables into the container, and to pass passwords as single files, in mounted container directory /run/secrets/ .
> * Inside postgres I am trying to load that data, using memoization pattern. For example using CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'. This part is encapsulated as a function.
> 
> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function. And well, that failed.
> 
> 
> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN TABLE, but this way it isn't parametrizable. I would have to create as many foreign tables as I have passwords / environment variables, I think.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe 
> Gesendet: Donnerstag, 1. Oktober 2026 22:13
> An: Färber, Franz-Josef (StMUK) ; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> 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__-t5EjSzbY2068S
>> 01pcTg%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



AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Thanks for the suggestion.
But what exactly is the suggestion?

At least I understand DuckDB can directly read without having to craete a temp table:
* SELECT * FROM 'test.csv.gz';
* SELECT * FROM read_csv('test.csv', header = false);

... which is what Postgres is missing.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver  
Gesendet: Montag, 12. Oktober 2026 18:17
An: Färber, Franz-Josef (StMUK) ; Laurenz Albe ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?



On 10/12/26 7:59 AM, Färber, Franz-Josef (StMUK) wrote:
> Sorry for my late reply. Thanks for pointing some stuff out.
> 
>> Other than that, you could use a variable that is an array of table rows (declared as my_table[]).
> A bit clumsy but yes, thank you.
> 
> I appreciate PostgreSQL, but what sometimes baffles me is that SQL (or is it PostgreSQL) is ... what should I call it? Lacking modularity / orthogonality? Simply put: When you want to combine different parts, you will meet obstacles. More obstacles than one is accustomed to when coming from other languages. Imperative or functional programming languages, that is.
> 
> If I need some temporary space for structured data and am restricted to avoid a temp table; if I am then forced to use a global table (which not only can be seen in other connections, but might introduce concurrency issues). Then work around using arrays. If even applicable.
> 
> Because what I haven't said yet and should admit now, the temp table gets filled with COPY ... FROM PROGRAM.
> 
> The next obstacle which occurs strangely to me is that COPY ... FROM PROGRAM only can dump data into tables. One cannot use it like other table-like objects, like a view, One cannot COPY ... FROM PROGRAM into an array.
> 
> Postgres offers again no feasible way for what I was trying to archieve.
> 
> 
> Ok, I think some people get the point that I claim SQL / Postgres is lacking modularity / orthogonality in the way as I tried to define above.
> 
> And I think some people want to dive into my special use case, perhaps consider it as kind-of-insane. So let me explain. And perhaps somebody can give good advice.

Something like this:

cat test.csv
id,type,active
1,dog,t
2,cat,f
3,fish,t

duckdb
DuckDB v1.4.4 (Andium) 6ddac802ff
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
D create temp table test_tbl as select * from 'test.csv'; D select * from test_tbl; ┌───────┬─────────┬─────────┐
│  id   │  type   │ active  │
│ int64 │ varchar │ boolean │
├───────┼─────────┼─────────┤
│     1 │ dog     │ true    │
│     2 │ cat     │ false   │
│     3 │ fish    │ true    │
└───────┴─────────┴─────────┘

Not exactly sure how this might fit into your scheme, see below for more
information:

https://duckdb.org/docs/current/data/overview
https://duckdb.org/docs/current/data/csv/overview

https://duckdb.org/docs/current/clients/overview

https://duckdb.org/docs/current/core_extensions/postgres/overview

> 
> * I am running Postgres in a container.
> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
> * I took it as good advice to pass environment variables into the container, and to pass passwords as single files, in mounted container directory /run/secrets/ .
> * Inside postgres I am trying to load that data, using memoization pattern. For example using CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'. This part is encapsulated as a function.
> 
> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function. And well, that failed.
> 
> 
> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN TABLE, but this way it isn't parametrizable. I would have to create as many foreign tables as I have passwords / environment variables, I think.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe 
> Gesendet: Donnerstag, 1. Oktober 2026 22:13
> An: Färber, Franz-Josef (StMUK) ; 
> pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> 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__-t5EjSzbY2068
>> S
>> 01pcTg%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

AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Yes, Thank you. I mentioned that here, with its disadvantage: https://www.postgresql.org/message-id/f69b47dbbe374bac8088887c21193f26%40stmuk.bayern.de :

> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN TABLE, but this way it isn't parametrizable. I would have to create as many foreign tables as I have passwords / environment variables, I think.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Christophe Pettus  
Gesendet: Dienstag, 13. Oktober 2026 08:19
An: Färber, Franz-Josef (StMUK) 
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?

Hi,

> On Oct 12, 2026, at 23:12, Färber, Franz-Josef (StMUK)  wrote:
> 
> Thanks for the suggestion.
> But what exactly is the suggestion?
> 
> At least I understand DuckDB can directly read without having to craete a temp table:
> * SELECT * FROM 'test.csv.gz';
> * SELECT * FROM read_csv('test.csv', header = false);
> 
> ... which is what Postgres is missing.

PostgreSQL implements this using foreign data wrappers.

AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
> ... because PostgreSQL is more statically typed ...
> You might not think of that as a feature, but it's the way PostgreSQL works.
I disagree.

It is not a statically-typing problem. My use case would easily be feasible if ... 

* ... the PostgreSQL COPY ... FROM PROGRAM statement could be used like a query, without having to use a temp table. Due to typing, one would have to use a FROM clause with column names and types defined, just like tablefunc: https://www.postgresql.org/docs/current/tablefunc.html
* ... or there would be some chance to create a temp table inside a plpgsql function ... which in the end is used in my use case inside a materialized view ... which is forbidden and which can still be seen in the subject of this mail as the first question I had.


Regards,
Franz-Josef Färber


-----Ursprüngliche Nachricht-----
Von: Christophe Pettus  
Gesendet: Dienstag, 13. Oktober 2026 08:34
An: Färber, Franz-Josef (StMUK) 
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?



> On Oct 12, 2026, at 23:23, Färber, Franz-Josef (StMUK)  wrote:
> 
> Yes, Thank you. I mentioned that here, with its disadvantage: https://www.postgresql.org/message-id/f69b47dbbe374bac8088887c21193f26%40stmuk.bayern.de :

I'm not sure why scripting CREATE FOREIGN TABLE is that complex, but you know your application best.  PostgreSQL cannot easily do what DuckDB does and just have a .csv thrown at it as a table, because PostgreSQL is more statically typed than DuckDB, and would need a way to be told what types the columns are.  You might not think of that as a feature, but it's the way PostgreSQL works.

PostgreSQL is not the right tool for every single job, and this may be a case where a different data engine will suit you better.

AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
> It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function. 
Because contrary to a function a foreign table is not parametrizable, I claimed I would have to create multiple foreign tables - compared to just one function.

Concerning the DRY (don't repeat yourself) principle, I thought a function would be the better choice.


As I somewhere said, I miss modularity / orthogonality in SQL / Postgres. We already mentioned multiple places in this thread. This is the recurring theme here.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Christophe Pettus  
Gesendet: Dienstag, 13. Oktober 2026 08:55
An: Färber, Franz-Josef (StMUK) 
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?



> On Oct 12, 2026, at 23:54, Färber, Franz-Josef (StMUK)  wrote:
> * ... the PostgreSQL COPY ... FROM PROGRAM statement could be used like a query, without having to use a temp table. Due to typing, one would have to use a FROM clause with column names and types defined, just like tablefunc: https://www.postgresql.org/docs/current/tablefunc.html
> * ... or there would be some chance to create a temp table inside a plpgsql function ... which in the end is used in my use case inside a materialized view ... which is forbidden and which can still be seen in the subject of this mail as the first question I had.

It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function.

Re: Why is materialized view creation a "security-restricted operation"?

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:

> [rant about PostgreSQL security restrictions]
>
> Ok, I think some people get the point that I claim SQL / Postgres is lacking
> modularity / orthogonality in the way as I tried to define above.

I don't.  Just because the features you are stacking don't work the way you'd
like them to, it's not a shortcoming of orthogonality or modularity.

> So let me explain. And perhaps somebody can give good advice.
> 
> * I am running Postgres in a container.
> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
> * I took it as good advice to pass environment variables into the container, and to pass
>   passwords as single files, in mounted container directory /run/secrets/ .
> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>   CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>   This part is encapsulated as a function.
> 
> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>   And well, that failed.
> 
> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN TABLE, but this
> way it isn't parametrizable. I would have to create as many foreign tables as I have
> passwords / environment variables, I think.

If the security restrictions of materialized views prevent you from using
them, why don't you use a normal table instead, TRUNCATE it and INSERT
into it?

Yours,
Laurenz Albe


AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
>> [rant about PostgreSQL security restrictions]
No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.

> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.

* I can replace it with a view; I didn't do that for performance reasons.
* I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought. 


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe  
Gesendet: Dienstag, 13. Oktober 2026 13:06
An: Färber, Franz-Josef (StMUK) ; Adrian Klaver ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: Why is materialized view creation a "security-restricted operation"?

On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:

> [rant about PostgreSQL security restrictions]
>
> Ok, I think some people get the point that I claim SQL / Postgres is 
> lacking modularity / orthogonality in the way as I tried to define above.

I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.

> So let me explain. And perhaps somebody can give good advice.
> 
> * I am running Postgres in a container.
> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
> * I took it as good advice to pass environment variables into the container, and to pass
>   passwords as single files, in mounted container directory /run/secrets/ .
> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>   CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>   This part is encapsulated as a function.
> 
> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>   And well, that failed.
> 
> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw 
> FOREIGN TABLE, but this way it isn't parametrizable. I would have to 
> create as many foreign tables as I have passwords / environment variables, I think.

If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?

Yours,
Laurenz Albe

Re: AW: Why is materialized view creation a "security-restricted operation"?

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Tue, 2026-10-13 at 11:36 +0000, Färber, Franz-Josef (StMUK) wrote:
> > > [rant about PostgreSQL security restrictions]
>
> No no no, not about security restrictions. The rant is about lacking orthogonality.
> About often NOT being able to combine language features. Doesn't anybody share that
> view? We COULD do better, I think.

Well, you want to use a materialized view for something that it cannot be used
for, for security reasons.  We tried to explain that in this thread.

> > If the security restrictions of materialized views prevent you from using them,
> > why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>
> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
> 
> * I can replace it with a view; I didn't do that for performance reasons.
> * I can replace it with a persistent table, a stored procedure, and custom caching
>   logic. That'll work. But this is what a MAT VIEW is for, I thought. 

You could write a procedure that truncates the table and populates it with
a query, using a temporary table if you want.  Calling that procedure is just
as convenient as calling REFRESH MATERIALIZED VIEW.

Are you searching for a usable solution for a problem?  Then that should do.

Yours,
Laurenz Albe


AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
>Well, you want to use a materialized view for something that it cannot be used for, for security reasons.  We tried to explain that in this thread.
Yes, you can see it this way: One way would be: Using Temp Tables. This way is blocked due to security reasons. Discussion finished.

Another point of view is:
0) The security problem is not inherent for what I was trying to archieve; but due to lack of expressiveness one is forced into this dead end.
1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something like SELECT * FROM COPY FROM ...
2) or: Postgres lacks table variables.


> Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
Well this is my very definition of orthogonality / modularity, This is what I'm out for: Language features should be generally combinable, with very few limits.


> You could write a procedure that truncates the table and populates it with a query, using a temporary table if you want.  Calling that procedure is just as convenient as calling REFRESH MATERIALIZED VIEW.
Yes, that's what I meant in my last post.
But this leads to: I need to adjust all callings points, notify users of the previously-MATVIEW to adjust, and so on.

> Are you searching for a usable solution for a problem?  Then that should do.
I am searching for both
* A solution that does not feel like a workaround. (Well I WILL be able to live with the function solution. Somehow.)
* Some common ground that there is some lack of modularity / orthogonality / expressiveness in Postgres. Addressing the problem is the first step.


Thank You.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe  
Gesendet: Dienstag, 13. Oktober 2026 15:48
An: Färber, Franz-Josef (StMUK) ; Adrian Klaver ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?

On Tue, 2026-10-13 at 11:36 +0000, Färber, Franz-Josef (StMUK) wrote:
> > > [rant about PostgreSQL security restrictions]
>
> No no no, not about security restrictions. The rant is about lacking orthogonality.
> About often NOT being able to combine language features. Doesn't 
> anybody share that view? We COULD do better, I think.

Well, you want to use a materialized view for something that it cannot be used for, for security reasons.  We tried to explain that in this thread.

> > If the security restrictions of materialized views prevent you from 
> > using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>
> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
> 
> * I can replace it with a view; I didn't do that for performance reasons.
> * I can replace it with a persistent table, a stored procedure, and custom caching
>   logic. That'll work. But this is what a MAT VIEW is for, I thought. 

You could write a procedure that truncates the table and populates it with a query, using a temporary table if you want.  Calling that procedure is just as convenient as calling REFRESH MATERIALIZED VIEW.

Are you searching for a usable solution for a problem?  Then that should do.

Yours,
Laurenz Albe

Re: Why is materialized view creation a "security-restricted operation"?

От:
Rob Sargent <robjsargent@gmail.com>
Дата:


> On Oct 13, 2026, at 08:17, Färber, Franz-Josef (StMUK)  wrote:
> 
> 
>> 
>> Well, you want to use a materialized view for something that it cannot be used for, for security reasons.  We tried to explain that in this thread.
> Yes, you can see it this way: One way would be: Using Temp Tables. This way is blocked due to security reasons. Discussion finished.
> 
> Another point of view is:
> 0) The security problem is not inherent for what I was trying to archieve; but due to lack of expressiveness one is forced into this dead end.
> 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something like SELECT * FROM COPY FROM ...
> 2) or: Postgres lacks table variables.
> 

I am not clear on your workflow so these questions come to mind:
What do you do with the undefined “*” (if it were allowed)?  
Would you not have to predefine the columns of the table variable?

Re: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/12/26 11:12 PM, Färber, Franz-Josef (StMUK) wrote:
> Thanks for the suggestion.
> But what exactly is the suggestion?

That you also read the links included, where it shows that DuckDb is 
available from a multitude of languages and can connect to a Postgres 
database to share information. Think it of as in-memory database to do 
the scratch work that you then pass into Postgres. It is definitely a 
round about way of tackling the problem.

> 
> At least I understand DuckDB can directly read without having to craete a temp table:
> * SELECT * FROM 'test.csv.gz';
> * SELECT * FROM read_csv('test.csv', header = false);
> 
> ... which is what Postgres is missing.
> 
> 
> Regards,
> Franz-Josef Färber
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>> [rant about PostgreSQL security restrictions]
> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.

Quick and dirty example.

cat test.csv
1,dog,t
2,cat,f
3,fish,t

CREATE OR REPLACE FUNCTION public.temp_table()
  RETURNS TABLE(id integer, type character varying, active boolean)
  LANGUAGE plpgsql
AS $function$
BEGIN
     DROP TABLE IF EXISTS test_csv;
     CREATE TABLE test_csv(id integer, type varchar, active boolean);
     COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
     RETURN QUERY EXECUTE
         'SELECT id, type, active FROM test_csv';
END;
$function$


select * from temp_table();
  id | type | active
----+------+--------
   1 | dog  | t
   2 | cat  | f
   3 | fish | t


> 
>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
> 
> * I can replace it with a view; I didn't do that for performance reasons.
> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe 
> Gesendet: Dienstag, 13. Oktober 2026 13:06
> An: Färber, Franz-Josef (StMUK) ; Adrian Klaver ; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
> 
>> [rant about PostgreSQL security restrictions]
>>
>> Ok, I think some people get the point that I claim SQL / Postgres is
>> lacking modularity / orthogonality in the way as I tried to define above.
> 
> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
> 
>> So let me explain. And perhaps somebody can give good advice.
>>
>> * I am running Postgres in a container.
>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>> * I took it as good advice to pass environment variables into the container, and to pass
>>    passwords as single files, in mounted container directory /run/secrets/ .
>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>    CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>    This part is encapsulated as a function.
>>
>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>    And well, that failed.
>>
>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw
>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to
>> create as many foreign tables as I have passwords / environment variables, I think.
> 
> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
> 
> Yours,
> Laurenz Albe


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/13/26 9:23 AM, Adrian Klaver wrote:
> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>> [rant about PostgreSQL security restrictions]
>> No no no, not about security restrictions. The rant is about lacking 
>> orthogonality. About often NOT being able to combine language 
>> features. Doesn't anybody share that view? We COULD do better, I think.
> 
> Quick and dirty example.
> 
> cat test.csv
> 1,dog,t
> 2,cat,f
> 3,fish,t
> 
> CREATE OR REPLACE FUNCTION public.temp_table()

Forgive the function name, it was created before I realized I was not 
really going with a temporary table.

>   RETURNS TABLE(id integer, type character varying, active boolean)
>   LANGUAGE plpgsql
> AS $function$
> BEGIN
>      DROP TABLE IF EXISTS test_csv;
>      CREATE TABLE test_csv(id integer, type varchar, active boolean);
>      COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>      RETURN QUERY EXECUTE
>          'SELECT id, type, active FROM test_csv';
> END;
> $function$
> 
> 
> select * from temp_table();
>   id | type | active
> ----+------+--------
>    1 | dog  | t
>    2 | cat  | f
>    3 | fish | t
> 
> 
>>



-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Tue, 2026-10-13 at 14:17 +0000, Färber, Franz-Josef (StMUK) wrote:
> > Well, you want to use a materialized view for something that it cannot be used for,
> > for security reasons.  We tried to explain that in this thread.
>
> Yes, you can see it this way: One way would be: Using Temp Tables. This way is blocked due
> to security reasons. Discussion finished.
> 
> Another point of view is:
> 0) The security problem is not inherent for what I was trying to archieve; but due to
>    lack of expressiveness one is forced into this dead end.
> 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something
>    like SELECT * FROM COPY FROM ...
> 2) or: Postgres lacks table variables.

Point 0 is incomprehensible to my limited brain.  Point 1 is a missing feature.
Point 2 is easily solved with array variables, which arguably are pretty much that.

I understand your unease with the situation, but I am surprised by your stubbornness.
There are limitations with every architecture.  You just hit one in PostgreSQL.
Work around it and move on.

> > Just because the features you are stacking don't work the way you'd like them to,
> > it's not a shortcoming of orthogonality or modularity.
>
> Well this is my very definition of orthogonality / modularity, This is what I'm out for:
> Language features should be generally combinable, with very few limits.

One of these limits being security.  Your setup doesn't care for security
considerations.  That's okay, but security might be relevant in other situations.

If you want to hear my opinion of what the problem underlying the annoying limitation
you are hitting is: it is PostgreSQL's "search_path" parameter.  It is very powerful,
but by virtue of that it has opened the way to many privilege escalation attacks.
I'd assume that with perfect hindsight, PostgreSQL would never have introduced
that parameter.  The annoying security restriction you are hitting is supposed to
prevent privilege escalation attacks when a superuser restores a dump with a
maliciously crafted materialized view in it, I believe.

> > You could write a procedure that truncates the table and populates it with a
> > query, using a temporary table if you want.  Calling that procedure is just
> > as convenient as calling REFRESH MATERIALIZED VIEW.
>
> Yes, that's what I meant in my last post.
> But this leads to: I need to adjust all callings points, notify users of the
> previously-MATVIEW to adjust, and so on.

I understand that that can be annoying.  That's why you test the functionality
of your application ahead of time, right?

Yours,
Laurenz Albe


AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Thanks for the suggestion. Could work.

* Then we have a table at global scope.
* However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
* Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?

Quite a bunch of things to consider, but sounds safe enough for me. Thank you.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver  
Gesendet: Dienstag, 13. Oktober 2026 18:23
An: Färber, Franz-Josef (StMUK) ; Laurenz Albe ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?

On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>> [rant about PostgreSQL security restrictions]
> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.

Quick and dirty example.

cat test.csv
1,dog,t
2,cat,f
3,fish,t

CREATE OR REPLACE FUNCTION public.temp_table()
  RETURNS TABLE(id integer, type character varying, active boolean)
  LANGUAGE plpgsql
AS $function$
BEGIN
     DROP TABLE IF EXISTS test_csv;
     CREATE TABLE test_csv(id integer, type varchar, active boolean);
     COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
     RETURN QUERY EXECUTE
         'SELECT id, type, active FROM test_csv'; END; $function$


select * from temp_table();
  id | type | active
----+------+--------
   1 | dog  | t
   2 | cat  | f
   3 | fish | t


> 
>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
> 
> * I can replace it with a view; I didn't do that for performance reasons.
> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe 
> Gesendet: Dienstag, 13. Oktober 2026 13:06
> An: Färber, Franz-Josef (StMUK) ; 
> Adrian Klaver ; 
> pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> 
> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
> 
>> [rant about PostgreSQL security restrictions]
>>
>> Ok, I think some people get the point that I claim SQL / Postgres is 
>> lacking modularity / orthogonality in the way as I tried to define above.
> 
> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
> 
>> So let me explain. And perhaps somebody can give good advice.
>>
>> * I am running Postgres in a container.
>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>> * I took it as good advice to pass environment variables into the container, and to pass
>>    passwords as single files, in mounted container directory /run/secrets/ .
>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>    CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>    This part is encapsulated as a function.
>>
>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>    And well, that failed.
>>
>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw 
>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to 
>> create as many foreign tables as I have passwords / environment variables, I think.
> 
> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
> 
> Yours,
> Laurenz Albe


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote:
> Thanks for the suggestion. Could work.
> 
> * Then we have a table at global scope.
> * However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
> * Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?

As I indicated in a follow on post, my naming the function temp_table() 
was a mistake. This actually creates a 'permanent' table that survives a 
run until dropped on next run. Like I said this was a quick and dirty 
example. Without more information on what your process flow and security 
expectations this is about as in depth as I can get.

> 
> Quite a bunch of things to consider, but sounds safe enough for me. Thank you.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver 
> Gesendet: Dienstag, 13. Oktober 2026 18:23
> An: Färber, Franz-Josef (StMUK) ; Laurenz Albe ; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?
> 
> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>> [rant about PostgreSQL security restrictions]
>> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.
> 
> Quick and dirty example.
> 
> cat test.csv
> 1,dog,t
> 2,cat,f
> 3,fish,t
> 
> CREATE OR REPLACE FUNCTION public.temp_table()
>    RETURNS TABLE(id integer, type character varying, active boolean)
>    LANGUAGE plpgsql
> AS $function$
> BEGIN
>       DROP TABLE IF EXISTS test_csv;
>       CREATE TABLE test_csv(id integer, type varchar, active boolean);
>       COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>       RETURN QUERY EXECUTE
>           'SELECT id, type, active FROM test_csv'; END; $function$
> 
> 
> select * from temp_table();
>    id | type | active
> ----+------+--------
>     1 | dog  | t
>     2 | cat  | f
>     3 | fish | t
> 
> 
>>
>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
>>
>> * I can replace it with a view; I didn't do that for performance reasons.
>> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
>>
>>
>> Regards,
>> Franz-Josef Färber
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Laurenz Albe 
>> Gesendet: Dienstag, 13. Oktober 2026 13:06
>> An: Färber, Franz-Josef (StMUK) ;
>> Adrian Klaver ;
>> pgsql-general@postgresql.org
>> Cc: Haupt, Matthias (StMUK) 
>> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>>
>> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
>>
>>> [rant about PostgreSQL security restrictions]
>>>
>>> Ok, I think some people get the point that I claim SQL / Postgres is
>>> lacking modularity / orthogonality in the way as I tried to define above.
>>
>> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
>>
>>> So let me explain. And perhaps somebody can give good advice.
>>>
>>> * I am running Postgres in a container.
>>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>>> * I took it as good advice to pass environment variables into the container, and to pass
>>>     passwords as single files, in mounted container directory /run/secrets/ .
>>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>>     CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>>     This part is encapsulated as a function.
>>>
>>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>>     And well, that failed.
>>>
>>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw
>>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to
>>> create as many foreign tables as I have passwords / environment variables, I think.
>>
>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>
>> Yours,
>> Laurenz Albe
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com


AW: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Yes I read your follow-up post. I understood the table is "global". My remaining question simply was:

Your function temp_table (as ill-named as it might be),

Can it run concurrently and will the contents be isolated? I suppose so.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver  
Gesendet: Mittwoch, 14. Oktober 2026 16:30
An: Färber, Franz-Josef (StMUK) ; Laurenz Albe ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?

On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote:
> Thanks for the suggestion. Could work.
> 
> * Then we have a table at global scope.
> * However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
> * Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?

As I indicated in a follow on post, my naming the function temp_table() was a mistake. This actually creates a 'permanent' table that survives a run until dropped on next run. Like I said this was a quick and dirty example. Without more information on what your process flow and security expectations this is about as in depth as I can get.

> 
> Quite a bunch of things to consider, but sounds safe enough for me. Thank you.
> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver 
> Gesendet: Dienstag, 13. Oktober 2026 18:23
> An: Färber, Franz-Josef (StMUK) ; 
> Laurenz Albe ; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?
> 
> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>> [rant about PostgreSQL security restrictions]
>> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.
> 
> Quick and dirty example.
> 
> cat test.csv
> 1,dog,t
> 2,cat,f
> 3,fish,t
> 
> CREATE OR REPLACE FUNCTION public.temp_table()
>    RETURNS TABLE(id integer, type character varying, active boolean)
>    LANGUAGE plpgsql
> AS $function$
> BEGIN
>       DROP TABLE IF EXISTS test_csv;
>       CREATE TABLE test_csv(id integer, type varchar, active boolean);
>       COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>       RETURN QUERY EXECUTE
>           'SELECT id, type, active FROM test_csv'; END; $function$
> 
> 
> select * from temp_table();
>    id | type | active
> ----+------+--------
>     1 | dog  | t
>     2 | cat  | f
>     3 | fish | t
> 
> 
>>
>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
>>
>> * I can replace it with a view; I didn't do that for performance reasons.
>> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
>>
>>
>> Regards,
>> Franz-Josef Färber
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Laurenz Albe 
>> Gesendet: Dienstag, 13. Oktober 2026 13:06
>> An: Färber, Franz-Josef (StMUK) 
>> ;
>> Adrian Klaver ; 
>> pgsql-general@postgresql.org
>> Cc: Haupt, Matthias (StMUK) 
>> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>>
>> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
>>
>>> [rant about PostgreSQL security restrictions]
>>>
>>> Ok, I think some people get the point that I claim SQL / Postgres is 
>>> lacking modularity / orthogonality in the way as I tried to define above.
>>
>> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
>>
>>> So let me explain. And perhaps somebody can give good advice.
>>>
>>> * I am running Postgres in a container.
>>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>>> * I took it as good advice to pass environment variables into the container, and to pass
>>>     passwords as single files, in mounted container directory /run/secrets/ .
>>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>>     CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>>     This part is encapsulated as a function.
>>>
>>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>>     And well, that failed.
>>>
>>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw 
>>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to 
>>> create as many foreign tables as I have passwords / environment variables, I think.
>>
>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>
>> Yours,
>> Laurenz Albe
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: AW: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/14/26 7:56 AM, Färber, Franz-Josef (StMUK) wrote:
> Yes I read your follow-up post. I understood the table is "global". My remaining question simply was:
> 
> Your function temp_table (as ill-named as it might be),
> 
> Can it run concurrently and will the contents be isolated? I suppose so.

I have not tested, though I suspect the DROP TABLE will not play nice 
with other transactions.  I'm going to say that something along the 
lines of what Laurenz Albe proposed below would work better:

https://www.postgresql.org/message-id/4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel%40cybertec.at

"If the security restrictions of materialized views prevent you from 
using them, why don't you use a normal table instead, TRUNCATE it and 
INSERT into it?"

To that I would add, take a look at Row Level Security:

https://www.postgresql.org/docs/current/sql-createpolicy.html


If you want a more thought out solution you will need to provide a more 
detailed explanation of what the data flow is in your process and your 
expectations of security and throughput.

> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver 
> Gesendet: Mittwoch, 14. Oktober 2026 16:30
> An: Färber, Franz-Josef (StMUK) ; Laurenz Albe ; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) 
> Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
> 
> On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote:
>> Thanks for the suggestion. Could work.
>>
>> * Then we have a table at global scope.
>> * However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
>> * Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?
> 
> As I indicated in a follow on post, my naming the function temp_table() was a mistake. This actually creates a 'permanent' table that survives a run until dropped on next run. Like I said this was a quick and dirty example. Without more information on what your process flow and security expectations this is about as in depth as I can get.
> 
>>
>> Quite a bunch of things to consider, but sounds safe enough for me. Thank you.
>>
>>
>> Regards,
>> Franz-Josef Färber
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Adrian Klaver 
>> Gesendet: Dienstag, 13. Oktober 2026 18:23
>> An: Färber, Franz-Josef (StMUK) ;
>> Laurenz Albe ; pgsql-general@postgresql.org
>> Cc: Haupt, Matthias (StMUK) 
>> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?
>>
>> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>>> [rant about PostgreSQL security restrictions]
>>> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.
>>
>> Quick and dirty example.
>>
>> cat test.csv
>> 1,dog,t
>> 2,cat,f
>> 3,fish,t
>>
>> CREATE OR REPLACE FUNCTION public.temp_table()
>>     RETURNS TABLE(id integer, type character varying, active boolean)
>>     LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>        DROP TABLE IF EXISTS test_csv;
>>        CREATE TABLE test_csv(id integer, type varchar, active boolean);
>>        COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>>        RETURN QUERY EXECUTE
>>            'SELECT id, type, active FROM test_csv'; END; $function$
>>
>>
>> select * from temp_table();
>>     id | type | active
>> ----+------+--------
>>      1 | dog  | t
>>      2 | cat  | f
>>      3 | fish | t
>>
>>
>>>
>>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
>>>
>>> * I can replace it with a view; I didn't do that for performance reasons.
>>> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
>>>
>>>
>>> Regards,
>>> Franz-Josef Färber
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: Laurenz Albe 
>>> Gesendet: Dienstag, 13. Oktober 2026 13:06
>>> An: Färber, Franz-Josef (StMUK)
>>> ;
>>> Adrian Klaver ;
>>> pgsql-general@postgresql.org
>>> Cc: Haupt, Matthias (StMUK) 
>>> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>>>
>>> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
>>>
>>>> [rant about PostgreSQL security restrictions]
>>>>
>>>> Ok, I think some people get the point that I claim SQL / Postgres is
>>>> lacking modularity / orthogonality in the way as I tried to define above.
>>>
>>> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
>>>
>>>> So let me explain. And perhaps somebody can give good advice.
>>>>
>>>> * I am running Postgres in a container.
>>>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>>>> * I took it as good advice to pass environment variables into the container, and to pass
>>>>      passwords as single files, in mounted container directory /run/secrets/ .
>>>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>>>      CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>>>      This part is encapsulated as a function.
>>>>
>>>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>>>      And well, that failed.
>>>>
>>>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw
>>>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to
>>>> create as many foreign tables as I have passwords / environment variables, I think.
>>>
>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>>
>>> Yours,
>>> Laurenz Albe
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Is there a postgres/postgis job near Warwick or Leamington Spa?

От:
Shaozhong SHI <shishaozhong@gmail.com>
Дата:
Dear All,
Is there a job near Warwick or Leamington Spa?

Regards,  Shao

On Wed, 14 Oct 2026, 17:17 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 10/14/26 7:56 AM, Färber, Franz-Josef (StMUK) wrote:
> Yes I read your follow-up post. I understood the table is "global". My remaining question simply was:
>
> Your function temp_table (as ill-named as it might be),
>
> Can it run concurrently and will the contents be isolated? I suppose so.

I have not tested, though I suspect the DROP TABLE will not play nice
with other transactions.  I'm going to say that something along the
lines of what Laurenz Albe proposed below would work better:

https://www.postgresql.org/message-id/4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel%40cybertec.at

"If the security restrictions of materialized views prevent you from
using them, why don't you use a normal table instead, TRUNCATE it and
INSERT into it?"

To that I would add, take a look at Row Level Security:

https://www.postgresql.org/docs/current/sql-createpolicy.html


If you want a more thought out solution you will need to provide a more
detailed explanation of what the data flow is in your process and your
expectations of security and throughput.

>
>
> Regards,
> Franz-Josef Färber
>
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian.klaver@aklaver.com>
> Gesendet: Mittwoch, 14. Oktober 2026 16:30
> An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@postgresql.org
> Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
> Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
>
> On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote:
>> Thanks for the suggestion. Could work.
>>
>> * Then we have a table at global scope.
>> * However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
>> * Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?
>
> As I indicated in a follow on post, my naming the function temp_table() was a mistake. This actually creates a 'permanent' table that survives a run until dropped on next run. Like I said this was a quick and dirty example. Without more information on what your process flow and security expectations this is about as in depth as I can get.
>
>>
>> Quite a bunch of things to consider, but sounds safe enough for me. Thank you.
>>
>>
>> Regards,
>> Franz-Josef Färber
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Adrian Klaver <adrian.klaver@aklaver.com>
>> Gesendet: Dienstag, 13. Oktober 2026 18:23
>> An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>;
>> Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@postgresql.org
>> Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
>> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?
>>
>> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>>> [rant about PostgreSQL security restrictions]
>>> No no no, not about security restrictions. The rant is about lacking orthogonality. About often NOT being able to combine language features. Doesn't anybody share that view? We COULD do better, I think.
>>
>> Quick and dirty example.
>>
>> cat test.csv
>> 1,dog,t
>> 2,cat,f
>> 3,fish,t
>>
>> CREATE OR REPLACE FUNCTION public.temp_table()
>>     RETURNS TABLE(id integer, type character varying, active boolean)
>>     LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>        DROP TABLE IF EXISTS test_csv;
>>        CREATE TABLE test_csv(id integer, type varchar, active boolean);
>>        COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>>        RETURN QUERY EXECUTE
>>            'SELECT id, type, active FROM test_csv'; END; $function$
>>
>>
>> select * from temp_table();
>>     id | type | active
>> ----+------+--------
>>      1 | dog  | t
>>      2 | cat  | f
>>      3 | fish | t
>>
>>
>>>
>>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>> The MAT VIEW is used from "outside". From other views, or from a client that connects to the database.
>>>
>>> * I can replace it with a view; I didn't do that for performance reasons.
>>> * I can replace it with a persistent table, a stored procedure, and custom caching logic. That'll work. But this is what a MAT VIEW is for, I thought.
>>>
>>>
>>> Regards,
>>> Franz-Josef Färber
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: Laurenz Albe <laurenz.albe@cybertec.at>
>>> Gesendet: Dienstag, 13. Oktober 2026 13:06
>>> An: Färber, Franz-Josef (StMUK)
>>> <Franz-Josef.Faerber@stmuk.bayern.de>;
>>> Adrian Klaver <adrian.klaver@aklaver.com>;
>>> pgsql-general@postgresql.org
>>> Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
>>> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>>>
>>> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
>>>
>>>> [rant about PostgreSQL security restrictions]
>>>>
>>>> Ok, I think some people get the point that I claim SQL / Postgres is
>>>> lacking modularity / orthogonality in the way as I tried to define above.
>>>
>>> I don't.  Just because the features you are stacking don't work the way you'd like them to, it's not a shortcoming of orthogonality or modularity.
>>>
>>>> So let me explain. And perhaps somebody can give good advice.
>>>>
>>>> * I am running Postgres in a container.
>>>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>>>> * I took it as good advice to pass environment variables into the container, and to pass
>>>>      passwords as single files, in mounted container directory /run/secrets/ .
>>>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>>>>      CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>>>>      This part is encapsulated as a function.
>>>>
>>>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>>>>      And well, that failed.
>>>>
>>>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw
>>>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to
>>>> create as many foreign tables as I have passwords / environment variables, I think.
>>>
>>> If the security restrictions of materialized views prevent you from using them, why don't you use a normal table instead, TRUNCATE it and INSERT into it?
>>>
>>> Yours,
>>> Laurenz Albe
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


--
Adrian Klaver
adrian.klaver@aklaver.com


AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
Yes, I would have to predefine the columns.
In my case it's just one column, one row, which i wanted to load from an external file.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Rob Sargent  
Gesendet: Dienstag, 13. Oktober 2026 17:37
An: Färber, Franz-Josef (StMUK) 
Cc: Laurenz Albe ; Adrian Klaver ; pgsql-general@postgresql.org; Haupt, Matthias (StMUK) 
Betreff: Re: Why is materialized view creation a "security-restricted operation"?



> On Oct 13, 2026, at 08:17, Färber, Franz-Josef (StMUK)  wrote:
> 
> 
>> 
>> Well, you want to use a materialized view for something that it cannot be used for, for security reasons.  We tried to explain that in this thread.
> Yes, you can see it this way: One way would be: Using Temp Tables. This way is blocked due to security reasons. Discussion finished.
> 
> Another point of view is:
> 0) The security problem is not inherent for what I was trying to archieve; but due to lack of expressiveness one is forced into this dead end.
> 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something like SELECT * FROM COPY FROM ...
> 2) or: Postgres lacks table variables.
> 

I am not clear on your workflow so these questions come to mind:
What do you do with the undefined “*” (if it were allowed)?  
Would you not have to predefine the columns of the table variable?

Re: Why is materialized view creation a "security-restricted operation"?

От:
Rob Sargent <robjsargent@gmail.com>
Дата:


> On Oct 14, 2026, at 11:38, Färber, Franz-Josef (StMUK)  wrote:
> 
> Yes, I would have to predefine the columns.
> In my case it's just one column, one row, which i wanted to load from an external file.
> 
> 
Excuse me?  One column, one row is a single value. Where does the need for a view/table/relation come from?



AW: AW: AW: Why is materialized view creation a "security-restricted operation"?

От:
Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Дата:
> If you want to hear my opinion of what the problem underlying the annoying limitation you are hitting is: it is PostgreSQL's "search_path" parameter. 
Yes, one can frame it like that. And 'pg_temp' being in front, and in the consequence, the MAT VIEW refresh security limitation.

> That's why you test the functionality of your application ahead of time, right?
Yes. In this case, it was a CHANGE which now affects the MATVIEW, and it looks like I now have to replace the MATVIEW (which already existed for some time) with a FUNCTION, which has additional consequences (modifing every calling point of that MATVIEW to now call a FUNCTION).

> I understand your unease with the situation, but I am surprised by your stubbornness.
> There are limitations with every architecture.  You just hit one in PostgreSQL.
> Work around it and move on.
Yes.
I can work around and move on.
It was surprising how many obstacles there are in my way.
I previously wanted to point out that it needn't be that limited, especially at last the "COPY ... FROM ... " limitation that a table is needed (DuckDB doesn't have that).

I will explore the possibility of a non-temp TABLE here but was a bit afraid about concurrency issues on concurrent execution of CREATING/DROPPING/TRUNCATING that table, as talked about in the concurrent [pun intended] discussion with Adrian.


Thank You.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe  
Gesendet: Mittwoch, 14. Oktober 2026 08:24
An: Färber, Franz-Josef (StMUK) ; Adrian Klaver ; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) 
Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?

On Tue, 2026-10-13 at 14:17 +0000, Färber, Franz-Josef (StMUK) wrote:
> > Well, you want to use a materialized view for something that it 
> > cannot be used for, for security reasons.  We tried to explain that in this thread.
>
> Yes, you can see it this way: One way would be: Using Temp Tables. 
> This way is blocked due to security reasons. Discussion finished.
> 
> Another point of view is:
> 0) The security problem is not inherent for what I was trying to archieve; but due to
>    lack of expressiveness one is forced into this dead end.
> 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something
>    like SELECT * FROM COPY FROM ...
> 2) or: Postgres lacks table variables.

Point 0 is incomprehensible to my limited brain.  Point 1 is a missing feature.
Point 2 is easily solved with array variables, which arguably are pretty much that.

I understand your unease with the situation, but I am surprised by your stubbornness.
There are limitations with every architecture.  You just hit one in PostgreSQL.
Work around it and move on.

> > Just because the features you are stacking don't work the way you'd 
> > like them to, it's not a shortcoming of orthogonality or modularity.
>
> Well this is my very definition of orthogonality / modularity, This is what I'm out for:
> Language features should be generally combinable, with very few limits.

One of these limits being security.  Your setup doesn't care for security considerations.  That's okay, but security might be relevant in other situations.

If you want to hear my opinion of what the problem underlying the annoying limitation you are hitting is: it is PostgreSQL's "search_path" parameter.  It is very powerful, but by virtue of that it has opened the way to many privilege escalation attacks.
I'd assume that with perfect hindsight, PostgreSQL would never have introduced that parameter.  The annoying security restriction you are hitting is supposed to prevent privilege escalation attacks when a superuser restores a dump with a maliciously crafted materialized view in it, I believe.

> > You could write a procedure that truncates the table and populates 
> > it with a query, using a temporary table if you want.  Calling that 
> > procedure is just as convenient as calling REFRESH MATERIALIZED VIEW.
>
> Yes, that's what I meant in my last post.
> But this leads to: I need to adjust all callings points, notify users 
> of the previously-MATVIEW to adjust, and so on.

I understand that that can be annoying.  That's why you test the functionality of your application ahead of time, right?

Yours,
Laurenz Albe
FAQ