Yes I read your follow-up post. I understood the table is "global". My remaining question simply was:
Your function temp_table (as ill-named as it might be), Can it run concurrently and will the contents be isolated? I suppose so. Regards, Franz-Josef Färber -----Ursprüngliche Nachricht----- Von: Adrian Klaver <[email protected]> Gesendet: Mittwoch, 14. Oktober 2026 16:30 An: Färber, Franz-Josef (StMUK) <[email protected]>; Laurenz Albe <[email protected]>; [email protected] Cc: Haupt, Matthias (StMUK) <[email protected]> Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"? On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote: > 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? As I indicated in a follow on post, my naming the function temp_table() was a mistake. This actually creates a 'permanent' table that survives a run until dropped on next run. Like I said this was a quick and dirty example. Without more information on what your process flow and security expectations this is about as in depth as I can get. > > 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] -- Adrian Klaver [email protected]
