DKIM2-Signature: i=1; m=1; t=1791988178; d=aklaver.com;
	mf=PGFkcmlhbi5rbGF2ZXJAYWtsYXZlci5jb20+;
	rt=PHBnc3FsLWdlbmVyYWxAcG9zdGdyZXNxbC5vcmc+;
	s=fm1:rsa-sha256:Hcmv1IkpqxytEUVsLGY1pVFhvUbZJp5HXQT36qMabPPjyo4
	vf8io0/UKwmSzxLl2Imq4FZZOgodcThEEZ8ZlIdEL6RHjD7YDn/pK4EbyAs3b8OK
	3dDf84W/WWptClBv2CipkTXdJH55Nc89gBehnh08JwwWT0KEYWa5NLFVe96i46Tu
	RdI8MdlC6Hq14Ifo/xASx/5YfgjVzOhcvWHSNyo3Sk35IUg7cJCnaZBtdCTfQapC
	lkhciZs5izEsXls13uzOLuhou+4HRu+e69rHtsT2YSOxYsPBh42Da/GWq1RP80gB
	S3AaJYQx9OE5q7301fVchCpHJj0WUEIKupI4B1g==;
Message-Instance: m=1;
 h=sha256:MlLJun6cEPfdUvn10AVQSB692Ek9cp+0A04voynGroo=:55sEfeJh7ho1F2bH9hfoVEe7BRlXdO3PfeB1zRcZeKA=;
Feedback-ID: i76984098:Fastmail
Message-ID: <2fa339b7-ab41-4b33-8d4d-ced91798451b@aklaver.com>
Date: Wed, 14 Oct 2026 07:29:37 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: AW: AW: Why is materialized view creation a "security-restricted
 operation"?
To: =?UTF-8?Q?F=C3=A4rber=2C_Franz-Josef_=28StMUK=29?=
 <Franz-Josef.Faerber@stmuk.bayern.de>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Cc: "Haupt, Matthias (StMUK)" <Matthias.Haupt@stmuk.bayern.de>
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>
 <687d09da725449d18e88654dafd9efdd@stmuk.bayern.de>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <687d09da725449d18e88654dafd9efdd@stmuk.bayern.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2fa339b7-ab41-4b33-8d4d-ced91798451b%40aklaver.com>
Precedence: bulk

On 10/14/26 6:16 AM, Färber, Franz-Josef (StMUK) wrote:
> 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?

As I indicated in a follow on post, my naming the function temp_table() 
was a mistake. This actually creates a 'permanent' table that survives a 
run until dropped on next run. Like I said this was a quick and dirty 
example. Without more information on what your process flow and security 
expectations this is about as in depth as I can get.

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com