From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: AW: AW: Why is materialized view creation a "security-restricted operation"?
Date: Tue, 13 Oct 2026 06:12:44 +0000
Message-ID: <569054683acf4de7a3f2de80391b4e71@stmuk.bayern.de> (raw)
In-Reply-To: <9f30126c-9e72-4ced-83e2-3f8e5da183d3@aklaver.com>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
<2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
<f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
<9f30126c-9e72-4ced-83e2-3f8e5da183d3@aklaver.com>
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.klaver@aklaver.com>
Gesendet: Montag, 12. Oktober 2026 18:17
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/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/overviewhttps://duckdb.org/docs/current/data/csv/overviewhttps://duckdb.org/docs/current/clients/overviewhttps://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__-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Franz-Josef.Faerber@stmuk.bayern.de, adrian.klaver@aklaver.com
Subject: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
In-Reply-To: <569054683acf4de7a3f2de80391b4e71@stmuk.bayern.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox