Message-ID: <c9f4e4a615b076199779deb84203c8a0dd095e3e.camel@cybertec.at>
Subject: Re: AW: Why is materialized view creation a "security-restricted
 operation"?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?ISO-8859-1?Q?F=E4rber=2C?= "Franz-Josef (StMUK)"	
 <Franz-Josef.Faerber@stmuk.bayern.de>, Adrian Klaver
 <adrian.klaver@aklaver.com>,  "pgsql-general@postgresql.org"	
 <pgsql-general@postgresql.org>
Cc: "Haupt, Matthias (StMUK)" <Matthias.Haupt@stmuk.bayern.de>
Date: Tue, 13 Oct 2026 15:47:40 +0200
In-Reply-To: <5628a96712e14b9fac594b34b0eb9161@stmuk.bayern.de>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
		 <2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
		 <f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
	 <4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel@cybertec.at>
	 <5628a96712e14b9fac594b34b0eb9161@stmuk.bayern.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.60.2 (3.60.2-2.fc44) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c9f4e4a615b076199779deb84203c8a0dd095e3e.camel%40cybertec.at>
Precedence: bulk

On Tue, 2026-10-13 at 11:36 +0000, F=C3=A4rber, Franz-Josef (StMUK) wrote:
> > > [rant about PostgreSQL security restrictions]
>
> No no no, not about security restrictions. The rant is about lacking orth=
ogonality.
> 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 u=
sed
for, for security reasons.  We tried to explain that in this thread.

> > If the security restrictions of materialized views prevent you from usi=
ng them,
> > why don't you use a normal table instead, TRUNCATE it and INSERT into i=
t?
>
> The MAT VIEW is used from "outside". From other views, or from a client t=
hat connects to the database.
>=20
> * 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 custo=
m caching
>   logic. That'll work. But this is what a MAT VIEW is for, I thought.=20

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 ju=
st
as convenient as calling REFRESH MATERIALIZED VIEW.

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

Yours,
Laurenz Albe