pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
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: Re: AW: AW: AW: Why is materialized view creation a "security-restricted operation"?
Date: Wed, 14 Oct 2026 09:17:01 -0700
Message-ID: <5ce6f59d-2e18-4206-85ae-b8a7efa41726@aklaver.com> (raw)
In-Reply-To: <0e86d4ee4a96460a8ce7cef26c587ca2@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>
	<2fa339b7-ab41-4b33-8d4d-ced91798451b@aklaver.com>
	<0e86d4ee4a96460a8ce7cef26c587ca2@stmuk.bayern.de>

On 10/14/26 7:56 AM, Färber, Franz-Josef (StMUK) wrote:
> Yes I read your follow-up post. I understood the table is "global". My remaining question simply was:
> 
> Your function temp_table (as ill-named as it might be),
> 
> Can it run concurrently and will the contents be isolated? I suppose so.

I have not tested, though I suspect the DROP TABLE will not play nice 
with other transactions.  I'm going to say that something along the 
lines of what Laurenz Albe proposed below would work better:

https://www.postgresql.org/message-id/4d7467aa0ee958343fca8c6bd6720952b5bc08b1.camel%40cybertec.at

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

To that I would add, take a look at Row Level Security:

https://www.postgresql.org/docs/current/sql-createpolicy.html


If you want a more thought out solution you will need to provide a more 
detailed explanation of what the data flow is in your process and your 
expectations of security and throughput.

> 
> 
> Regards,
> Franz-Josef Färber
> 
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian.klaver@aklaver.com>
> Gesendet: Mittwoch, 14. Oktober 2026 16:30
> 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: AW: Why is materialized view creation a "security-restricted operation"?
> 
> 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


-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (3+ messages)  latest in thread

Message-ID: <5ce6f59d-2e18-4206-85ae-b8a7efa41726@aklaver.com>
Permalink:  ../5ce6f59d-2e18-4206-85ae-b8a7efa41726@aklaver.com/
Also on:    postgresql.org/message-id/5ce6f59d-2e18-4206-85ae-b8a7efa41726@aklaver.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, Franz-Josef.Faerber@stmuk.bayern.de, laurenz.albe@cybertec.at, Matthias.Haupt@stmuk.bayern.de
  Subject: Re: Why is materialized view creation a "security-restricted operation"?
  In-Reply-To: <5ce6f59d-2e18-4206-85ae-b8a7efa41726@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> <687d09da725449d18e88654dafd9efdd@stmuk.bayern.de> <2fa339b7-ab41-4b33-8d4d-ced91798451b@aklaver.com> <0e86d4ee4a96460a8ce7cef26c587ca2@stmuk.bayern.de> <5ce6f59d-2e18-4206-85ae-b8a7efa41726@aklaver.com>

* 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