Thanks for the suggestion. Could work. * Then we have a table at global scope. * However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance? * Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?
Quite a bunch of things to consider, but sounds safe enough for me. Thank you. Regards, Franz-Josef Färber -----Ursprüngliche Nachricht----- Von: Adrian Klaver <[email protected]> Gesendet: Dienstag, 13. Oktober 2026 18:23 An: Färber, Franz-Josef (StMUK) <[email protected]>; Laurenz Albe <[email protected]>; [email protected] Cc: Haupt, Matthias (StMUK) <[email protected]> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"? On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote: >>> [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. Quick and dirty example. cat test.csv 1,dog,t 2,cat,f 3,fish,t CREATE OR REPLACE FUNCTION public.temp_table() RETURNS TABLE(id integer, type character varying, active boolean) LANGUAGE plpgsql AS $function$ BEGIN DROP TABLE IF EXISTS test_csv; CREATE TABLE test_csv(id integer, type varchar, active boolean); COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV; RETURN QUERY EXECUTE 'SELECT id, type, active FROM test_csv'; END; $function$ select * from temp_table(); id | type | active ----+------+-------- 1 | dog | t 2 | cat | f 3 | fish | t > >> 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 -- Adrian Klaver [email protected]
