pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
AW: AW: AW: Why is materialized view creation a "security-restricted operation"?
3+ messages / 2 participants
[nested] [flat]

* AW: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 14:56  Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
  1 sibling, 1 reply; 3+ messages in thread

From: Färber, Franz-Josef (StMUK) @ 2026-10-14 14:56 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>

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.


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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: AW: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 16:17  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Adrian Klaver @ 2026-10-14 16:17 UTC (permalink / raw)
  To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* AW: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-15 09:35  Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Färber, Franz-Josef (StMUK) @ 2026-10-15 09:35 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>; Adrian Klaver <adrian.klaver@aklaver.com>

> If you want to hear my opinion of what the problem underlying the annoying limitation you are hitting is: it is PostgreSQL's "search_path" parameter. 
Yes, one can frame it like that. And 'pg_temp' being in front, and in the consequence, the MAT VIEW refresh security limitation.

> That's why you test the functionality of your application ahead of time, right?
Yes. In this case, it was a CHANGE which now affects the MATVIEW, and it looks like I now have to replace the MATVIEW (which already existed for some time) with a FUNCTION, which has additional consequences (modifing every calling point of that MATVIEW to now call a FUNCTION).

> I understand your unease with the situation, but I am surprised by your stubbornness.
> There are limitations with every architecture.  You just hit one in PostgreSQL.
> Work around it and move on.
Yes.
I can work around and move on.
It was surprising how many obstacles there are in my way.
I previously wanted to point out that it needn't be that limited, especially at last the "COPY ... FROM ... " limitation that a table is needed (DuckDB doesn't have that).

I will explore the possibility of a non-temp TABLE here but was a bit afraid about concurrency issues on concurrent execution of CREATING/DROPPING/TRUNCATING that table, as talked about in the concurrent [pun intended] discussion with Adrian.


Thank You.


Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at> 
Gesendet: Mittwoch, 14. Oktober 2026 08:24
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: AW: Why is materialized view creation a "security-restricted operation"?

On Tue, 2026-10-13 at 14:17 +0000, Färber, Franz-Josef (StMUK) wrote:
> > 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.

Point 0 is incomprehensible to my limited brain.  Point 1 is a missing feature.
Point 2 is easily solved with array variables, which arguably are pretty much that.

I understand your unease with the situation, but I am surprised by your stubbornness.
There are limitations with every architecture.  You just hit one in PostgreSQL.
Work around it and move on.

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

One of these limits being security.  Your setup doesn't care for security considerations.  That's okay, but security might be relevant in other situations.

If you want to hear my opinion of what the problem underlying the annoying limitation you are hitting is: it is PostgreSQL's "search_path" parameter.  It is very powerful, but by virtue of that it has opened the way to many privilege escalation attacks.
I'd assume that with perfect hindsight, PostgreSQL would never have introduced that parameter.  The annoying security restriction you are hitting is supposed to prevent privilege escalation attacks when a superuser restores a dump with a maliciously crafted materialized view in it, I believe.

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

I understand that that can be annoying.  That's why you test the functionality of your application ahead of time, right?

Yours,
Laurenz Albe


^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-10-15 09:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-14 14:56 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-14 16:17   ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-15 09:35 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>

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