>> [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 <[email protected]> Gesendet: Dienstag, 13. Oktober 2026 13:06 An: Färber, Franz-Josef (StMUK) <[email protected]>; Adrian Klaver <[email protected]>; [email protected] Cc: Haupt, Matthias (StMUK) <[email protected]> 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
