From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
Subject: AW: AW: Why is materialized view creation a "security-restricted operation"?
Date: Tue, 13 Oct 2026 14:17:25 +0000
Message-ID: <3b50a3393fd84d56b40cfddd67c98dbc@stmuk.bayern.de> (raw)
In-Reply-To: <c9f4e4a615b076199779deb84203c8a0dd095e3e.camel@cybertec.at>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
<2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
<f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
<4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel@cybertec.at>
<5628a96712e14b9fac594b34b0eb9161@stmuk.bayern.de>
<c9f4e4a615b076199779deb84203c8a0dd095e3e.camel@cybertec.at>
>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.
> 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.
> 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.
> Are you searching for a usable solution for a problem? Then that should do.
I am searching for both
* A solution that does not feel like a workaround. (Well I WILL be able to live with the function solution. Somehow.)
* Some common ground that there is some lack of modularity / orthogonality / expressiveness in Postgres. Addressing the problem is the first step.
Thank You.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Dienstag, 13. Oktober 2026 15:48
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
Betreff: 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Franz-Josef.Faerber@stmuk.bayern.de, laurenz.albe@cybertec.at, adrian.klaver@aklaver.com, Matthias.Haupt@stmuk.bayern.de
Subject: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
In-Reply-To: <3b50a3393fd84d56b40cfddd67c98dbc@stmuk.bayern.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox