pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: AW: Why is materialized view creation a "security-restricted operation"?
7+ messages / 3 participants
[nested] [flat]
* AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-12 14:40 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
3 siblings, 0 replies; 7+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-12 14:40 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Thanks for trying that out.
Well, failed, as I expected.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Dienstag, 6. Oktober 2026 23:38
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general@postgresql.org
Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"?
On 10/6/26 1:20 AM, Färber, Franz-Josef (StMUK) wrote:
> Are you sure the extension pgtt would work in my case?
>
> https://github.com/darold/pgtt#how-the-extension-really-works says:
>
> "the first access to the table using a SELECT, UPDATE or DELETE statement will produce the creation of a temporary table"
>
> Well, my original point was: Creation of a temporary table is prohibited due to "security-restricted operation".
I tried your MATERIALIZED VIEW with pgtt and ran into the temporary table issue. I tried also against the 'template' table behind the temporary tables and got a little further but the behavior was unstable.
I suspect a bug that allowed me to get that far.
Questions:
1) Why the MATERIALIZED VIEW?
2) Can you provide an overview in pseudo code, including the part in the function, of the process you are trying create?
>
>
> Regards,
> Franz-Josef Färber
>
>
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian.klaver@aklaver.com>
> Gesendet: Freitag, 2. Oktober 2026 16:55
> An: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
> <pgsql-general@postgresql.org>
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>
> On 10/2/26 3:19 AM, Ron Johnson wrote:
>> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston
>> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>>
>> On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com
>> <mailto:ronljohnsonjr@gmail.com>> wrote:
>>
>> A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP
>> TABLE once (so that CREATE TEMP TABLE some_table everywhere
>> that my_func() is called) would also solve OP's problem.
>>
>>
>> Per the create table docs:
>>
>> "Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
>> TEMP. This presently makes no difference in PostgreSQL and is
>> deprecated; see Compatibility below."
>>
>> And I'm praying for "since future versions of PostgreSQLmight adopt a
>> more standard-compliant interpretation of their meaning" in the
>> Compatibility section you referenced.
>
> As an extension there is:
>
> https://github.com/darold/pgtt
>
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 06:12 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
3 siblings, 1 reply; 7+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 06:12 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Thanks for the suggestion.
But what exactly is the suggestion?
At least I understand DuckDB can directly read without having to craete a temp table:
* SELECT * FROM 'test.csv.gz';
* SELECT * FROM read_csv('test.csv', header = false);
... which is what Postgres is missing.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Montag, 12. Oktober 2026 18:17
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/12/26 7:59 AM, Färber, Franz-Josef (StMUK) wrote:
> Sorry for my late reply. Thanks for pointing some stuff out.
>
>> Other than that, you could use a variable that is an array of table rows (declared as my_table[]).
> A bit clumsy but yes, thank you.
>
> I appreciate PostgreSQL, but what sometimes baffles me is that SQL (or is it PostgreSQL) is ... what should I call it? Lacking modularity / orthogonality? Simply put: When you want to combine different parts, you will meet obstacles. More obstacles than one is accustomed to when coming from other languages. Imperative or functional programming languages, that is.
>
> If I need some temporary space for structured data and am restricted to avoid a temp table; if I am then forced to use a global table (which not only can be seen in other connections, but might introduce concurrency issues). Then work around using arrays. If even applicable.
>
> Because what I haven't said yet and should admit now, the temp table gets filled with COPY ... FROM PROGRAM.
>
> The next obstacle which occurs strangely to me is that COPY ... FROM PROGRAM only can dump data into tables. One cannot use it like other table-like objects, like a view, One cannot COPY ... FROM PROGRAM into an array.
>
> Postgres offers again no feasible way for what I was trying to archieve.
>
>
> 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.
>
> And I think some people want to dive into my special use case, perhaps consider it as kind-of-insane. So let me explain. And perhaps somebody can give good advice.
Something like this:
cat test.csv
id,type,active
1,dog,t
2,cat,f
3,fish,t
duckdb
DuckDB v1.4.4 (Andium) 6ddac802ff
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
D create temp table test_tbl as select * from 'test.csv'; D select * from test_tbl; ┌───────┬─────────┬─────────┐
│ id │ type │ active │
│ int64 │ varchar │ boolean │
├───────┼─────────┼─────────┤
│ 1 │ dog │ true │
│ 2 │ cat │ false │
│ 3 │ fish │ true │
└───────┴─────────┴─────────┘
Not exactly sure how this might fit into your scheme, see below for more
information:
https://duckdb.org/docs/current/data/overview
https://duckdb.org/docs/current/data/csv/overview
https://duckdb.org/docs/current/clients/overview
https://duckdb.org/docs/current/core_extensions/postgres/overview
>
> * 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.
>
>
> Regards,
> Franz-Josef Färber
>
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Donnerstag, 1. Oktober 2026 22:13
> An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>;
> 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 Thu, 2026-10-01 at 13:11 +0000, Färber, Franz-Josef (StMUK) wrote:
>> some questions about this 9-year-old post below.
>> [
>> https://postgr.es/m/flat/CAFBoRzf6HwFg1jovdOrbtC6x4xKV__-t5EjSzbY2068
>> S
>> 01pcTg%40mail.gmail.com ]
>>
>> I also stumbled over a similar case as the failing
>>
>> CREATE MATERIALIZED VIEW some_view AS SELECT * FROM my_func();
>>
>> . where my_func tries to create a temp table.
>>
>> When writing an arbitrarily complex function my_func, I claim there
>> are cases when you want to store intermediate results into variables. And what if the intermediate results are tables?
>> Well, Postgres/plpgsql does not support table-valued variables, so the next best choice are temp tables.
>> But here we have: Creating temp tables is forbidden inside a mat view, see the mail below.
>> Because we might have a side effect ("change of seesion state"): The creation of this very temp table.
>>
>> What to do now? Well it turns out I actually CAN create a NON-temp
>> table. Is that what you want me to do? Really? Isn't this the bigger side effect: Creating a table?
>>
>> It actually does not make sense to me, restricting one effect, while allowing the much bigger effect.
>
> Creating a temporary table and creating a permanent table are not the same thing:
>
> - it requires different permissions: TEMP on the database (which is granted to PUBLIC
> by default) and CREATE on a schema (which only the owner has by
> default)
>
> - the temporary schema by default is at the beginning of the search_path, so it can
> easily shadow objects in other schemas
>
>> * What I actually needed is a table-valued variable. One I can use inside my function. Which shall
>> also be local/unique (i. e. not being used by concurrent users or sessions, or even in the call
>> stack of the very same session).
>>
>> * The next best thing would be a temp table, local/unique in the sense as above, that gets destroyed
>> when leaving the function.
>> Invent some CREATE TEMP TABLE . ON EXIT FUNCTION DROP?
>> (.and wouldn't that be quite equivalent to table-valued
>> variables?)
>>
>> Any suggestions?
>
> If you use a permanent table instead, I recommend an UNLOGGED table.
>
> Other than that, you could use a variable that is an array of table rows (declared as my_table[]).
>
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 14:17 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
3 siblings, 1 reply; 7+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 14:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
>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.
> 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.
> 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.
> Are you searching for a usable solution for a problem? Then that should do.
I am searching for both
* A solution that does not feel like a workaround. (Well I WILL be able to live with the function solution. Somehow.)
* Some common ground that there is some lack of modularity / orthogonality / expressiveness in Postgres. Addressing the problem is the first step.
Thank You.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Dienstag, 13. Oktober 2026 15:48
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: Why is materialized view creation a "security-restricted operation"?
On Tue, 2026-10-13 at 11:36 +0000, 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.
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.
> > 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.
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.
Are you searching for a usable solution for a problem? Then that should do.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 15:52 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2026-10-13 15:52 UTC (permalink / raw)
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general
On 10/12/26 11:12 PM, Färber, Franz-Josef (StMUK) wrote:
> Thanks for the suggestion.
> But what exactly is the suggestion?
That you also read the links included, where it shows that DuckDb is
available from a multitude of languages and can connect to a Postgres
database to share information. Think it of as in-memory database to do
the scratch work that you then pass into Postgres. It is definitely a
round about way of tackling the problem.
>
> At least I understand DuckDB can directly read without having to craete a temp table:
> * SELECT * FROM 'test.csv.gz';
> * SELECT * FROM read_csv('test.csv', header = false);
>
> ... which is what Postgres is missing.
>
>
> Regards,
> Franz-Josef Färber
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 06:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2026-10-14 06:24 UTC (permalink / raw)
To: Färber, "Franz-Josef (StMUK)" <Franz-Josef.Faerber@stmuk.bayern.de>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general; +Cc: Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
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] 7+ messages in thread
* AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 13:16 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
3 siblings, 1 reply; 7+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-14 13:16 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>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 14:29 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2026-10-14 14:29 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 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] 7+ messages in thread
end of thread, other threads:[~2026-10-14 14:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-12 14:40 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 06:12 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 15:52 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-13 14:17 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-14 06:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-14 13:16 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-14 14:29 ` Adrian Klaver <adrian.klaver@aklaver.com>
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