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