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

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