From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
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: Wed, 14 Oct 2026 13:16:44 +0000
Message-ID: <687d09da725449d18e88654dafd9efdd@stmuk.bayern.de> (raw)
In-Reply-To: <f5e1f18d-baa1-4ba4-9264-c26b1226cd98@aklaver.com>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
<2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
<f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
<4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel@cybertec.at>
<5628a96712e14b9fac594b34b0eb9161@stmuk.bayern.de>
<f5e1f18d-baa1-4ba4-9264-c26b1226cd98@aklaver.com>
Thanks for the suggestion. Could work.
* Then we have a table at global scope.
* However it won't be seen by other transactions, and is deleted in the same transaction. Sounds safe at first glance?
* Well, what about two transactions running concurrently, both executing public.temp_table()? Is safe, too, as far as I understand about transactions? Each transaction has its own table, even though on global scope?
Quite a bunch of things to consider, but sounds safe enough for me. Thank you.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Dienstag, 13. Oktober 2026 18:23
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; Laurenz Albe <laurenz.albe@cybertec.at>; 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 10/13/26 4:36 AM, 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.
Quick and dirty example.
cat test.csv
1,dog,t
2,cat,f
3,fish,t
CREATE OR REPLACE FUNCTION public.temp_table()
RETURNS TABLE(id integer, type character varying, active boolean)
LANGUAGE plpgsql
AS $function$
BEGIN
DROP TABLE IF EXISTS test_csv;
CREATE TABLE test_csv(id integer, type varchar, active boolean);
COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
RETURN QUERY EXECUTE
'SELECT id, type, active FROM test_csv'; END; $function$
select * from temp_table();
id | type | active
----+------+--------
1 | dog | t
2 | cat | f
3 | fish | t
>
>> 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.
>
>
> Regards,
> Franz-Josef Färber
>
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Dienstag, 13. Oktober 2026 13:06
> 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: Why is materialized view creation a "security-restricted operation"?
>
> On Mon, 2026-10-12 at 14:59 +0000, Färber, Franz-Josef (StMUK) wrote:
>
>> [rant about PostgreSQL security restrictions]
>>
>> Ok, I think some people get the point that I claim SQL / Postgres is
>> lacking modularity / orthogonality in the way as I tried to define above.
>
> I don't. 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.
>
>> So let me explain. And perhaps somebody can give good advice.
>>
>> * I am running Postgres in a container.
>> * I am using https://github.com/pramsey/pgsql-http to query REST services using credentials.
>> * I took it as good advice to pass environment variables into the container, and to pass
>> passwords as single files, in mounted container directory /run/secrets/ .
>> * Inside postgres I am trying to load that data, using memoization pattern. For example using
>> CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat /run/secrets/mypassword'.
>> This part is encapsulated as a function.
>>
>> * Now there was one point where I was using a MATERIALIZED VIEW accessing that function.
>> And well, that failed.
>>
>> What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw
>> FOREIGN TABLE, but this way it isn't parametrizable. I would have to
>> create as many foreign tables as I have passwords / environment variables, I think.
>
> 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?
>
> Yours,
> Laurenz Albe
--
Adrian Klaver
adrian.klaver@aklaver.com
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, adrian.klaver@aklaver.com, laurenz.albe@cybertec.at, Matthias.Haupt@stmuk.bayern.de
Subject: Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
In-Reply-To: <687d09da725449d18e88654dafd9efdd@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