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

>> [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 <laurenz(dot)albe(at)cybertec(dot)at>
Gesendet: Dienstag, 13. Oktober 2026 13:06
An: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>; Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; 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 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