DKIM2-Signature: i=1; m=1; t=1791821848; d=aklaver.com;
	mf=PGFkcmlhbi5rbGF2ZXJAYWtsYXZlci5jb20+;
	rt=PHBnc3FsLWdlbmVyYWxAcG9zdGdyZXNxbC5vcmc+;
	s=fm1:rsa-sha256:VOWPwp1T2XXR8a2SzTU2zku4BuZfoOivwiAuEOra3KcxB4Q
	1ujWt1hsGPFpb04t+EsNKo0ua+zJ+AG9SVMuuL6zGLm4+8BvT2e4W2nFKf5+2Lrt
	vnkeni2m0co3rdkSqytuJ/Za/N787tGEITX6rmUFGuXPR3wlcWqun7rQ/u1PumRc
	FgosjibCdm3d0qa322VLTWEzjgyf8FhLx3owQ4j9Dztm+866+1R9pUd6YXm5GSi2
	F8SKf+TtsDZsH+bacCC8sL/wlXO0SwYnjgJebGxhOlKLsd1bidW5phdpYpqPQdNZ
	yO5Pw+MteW5hvq05fmUiIarZZpIaPiTOdCYztcw==;
Message-Instance: m=1;
 h=sha256:T+4jNHRRyuof1B+9eKpOpF+d/N+cs81URPDtDuJdLkI=:wxU5KVI3TEfpYCsAnyDv7fzfEbPOMU1TAB+9VodOuss=;
Feedback-ID: i76984098:Fastmail
Message-ID: <9f30126c-9e72-4ced-83e2-3f8e5da183d3@aklaver.com>
Date: Mon, 12 Oct 2026 09:17:26 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: AW: Why is materialized view creation a "security-restricted
 operation"?
To: =?UTF-8?Q?F=C3=A4rber=2C_Franz-Josef_=28StMUK=29?=
 <Franz-Josef.Faerber@stmuk.bayern.de>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Cc: "Haupt, Matthias (StMUK)" <Matthias.Haupt@stmuk.bayern.de>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
 <2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
 <f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9f30126c-9e72-4ced-83e2-3f8e5da183d3%40aklaver.com>
Precedence: bulk


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 <laurenz.albe@cybertec.at>
> Gesendet: Donnerstag, 1. Oktober 2026 22:13
> An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; 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 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