Re: AW: Why is materialized view creation a "security-restricted operation"?

On Tue, 2026-10-13 at 11:36 +0000, 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.

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.

> > 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.

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.

Are you searching for a usable solution for a problem? Then that should do.

Yours,
Laurenz Albe