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

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 <adrian(dot)klaver(at)aklaver(dot)com>
Gesendet: Montag, 12. Oktober 2026 18:17
An: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>; Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-general(at)postgresql(dot)org
Cc: Haupt, Matthias (StMUK) <Matthias(dot)Haupt(at)stmuk(dot)bayern(dot)de>
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 <laurenz(dot)albe(at)cybertec(dot)at>
> Gesendet: Donnerstag, 1. Oktober 2026 22:13
> An: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>;
> pgsql-general(at)postgresql(dot)org
> Cc: Haupt, Matthias (StMUK) <Matthias(dot)Haupt(at)stmuk(dot)bayern(dot)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__-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