Dear All, Is there a job near Warwick or Leamington Spa? Regards, Shao
On Wed, 14 Oct 2026, 17:17 Adrian Klaver, <[email protected]> wrote: > On 10/14/26 7:56 AM, Färber, Franz-Josef (StMUK) wrote: > > 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. > > I have not tested, though I suspect the DROP TABLE will not play nice > with other transactions. I'm going to say that something along the > lines of what Laurenz Albe proposed below would work better: > > > https://www.postgresql.org/message-id/4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel%40cybertec.at > > "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?" > > To that I would add, take a look at Row Level Security: > > https://www.postgresql.org/docs/current/sql-createpolicy.html > > > If you want a more thought out solution you will need to provide a more > detailed explanation of what the data flow is in your process and your > expectations of security and throughput. > > > > > > > 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] > > > -- > Adrian Klaver > [email protected] > > >
