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