> If you want to hear my opinion of what the problem underlying the annoying > limitation you are hitting is: it is PostgreSQL's "search_path" parameter. Yes, one can frame it like that. And 'pg_temp' being in front, and in the consequence, the MAT VIEW refresh security limitation.
> That's why you test the functionality of your application ahead of time, > right? Yes. In this case, it was a CHANGE which now affects the MATVIEW, and it looks like I now have to replace the MATVIEW (which already existed for some time) with a FUNCTION, which has additional consequences (modifing every calling point of that MATVIEW to now call a FUNCTION). > I understand your unease with the situation, but I am surprised by your > stubbornness. > There are limitations with every architecture. You just hit one in > PostgreSQL. > Work around it and move on. Yes. I can work around and move on. It was surprising how many obstacles there are in my way. I previously wanted to point out that it needn't be that limited, especially at last the "COPY ... FROM ... " limitation that a table is needed (DuckDB doesn't have that). I will explore the possibility of a non-temp TABLE here but was a bit afraid about concurrency issues on concurrent execution of CREATING/DROPPING/TRUNCATING that table, as talked about in the concurrent [pun intended] discussion with Adrian. Thank You. Regards, Franz-Josef Färber -----Ursprüngliche Nachricht----- Von: Laurenz Albe <[email protected]> Gesendet: Mittwoch, 14. Oktober 2026 08:24 An: Färber, Franz-Josef (StMUK) <[email protected]>; Adrian Klaver <[email protected]>; [email protected] Cc: Haupt, Matthias (StMUK) <[email protected]> Betreff: Re: AW: AW: Why is materialized view creation a "security-restricted operation"? On Tue, 2026-10-13 at 14:17 +0000, Färber, Franz-Josef (StMUK) wrote: > > Well, you want to use a materialized view for something that it > > cannot be used for, for security reasons. We tried to explain that in this > > thread. > > Yes, you can see it this way: One way would be: Using Temp Tables. > This way is blocked due to security reasons. Discussion finished. > > Another point of view is: > 0) The security problem is not inherent for what I was trying to archieve; > but due to > lack of expressiveness one is forced into this dead end. > 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, > something > like SELECT * FROM COPY FROM ... > 2) or: Postgres lacks table variables. Point 0 is incomprehensible to my limited brain. Point 1 is a missing feature. Point 2 is easily solved with array variables, which arguably are pretty much that. I understand your unease with the situation, but I am surprised by your stubbornness. There are limitations with every architecture. You just hit one in PostgreSQL. Work around it and move on. > > 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. > > Well this is my very definition of orthogonality / modularity, This is what > I'm out for: > Language features should be generally combinable, with very few limits. One of these limits being security. Your setup doesn't care for security considerations. That's okay, but security might be relevant in other situations. If you want to hear my opinion of what the problem underlying the annoying limitation you are hitting is: it is PostgreSQL's "search_path" parameter. It is very powerful, but by virtue of that it has opened the way to many privilege escalation attacks. I'd assume that with perfect hindsight, PostgreSQL would never have introduced that parameter. The annoying security restriction you are hitting is supposed to prevent privilege escalation attacks when a superuser restores a dump with a maliciously crafted materialized view in it, I believe. > > You could write a procedure that truncates the table and populates > > it with a query, using a temporary table if you want. Calling that > > procedure is just as convenient as calling REFRESH MATERIALIZED VIEW. > > Yes, that's what I meant in my last post. > But this leads to: I need to adjust all callings points, notify users > of the previously-MATVIEW to adjust, and so on. I understand that that can be annoying. That's why you test the functionality of your application ahead of time, right? Yours, Laurenz Albe
