pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: Why is materialized view creation a "security-restricted operation"?
12+ messages / 3 participants
[nested] [flat]
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-06 08:20 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 1 reply; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-06 08:20 UTC (permalink / raw)
To: pgsql-general; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>
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".
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!
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-06 21:38 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2026-10-06 21:38 UTC (permalink / raw)
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general
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] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-12 14:59 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 2 replies; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-12 14:59 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>
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.
* 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__-t5EjSzbY2068S
> 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] 12+ messages in thread
* Re: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-12 16:17 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2026-10-12 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/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__-t5EjSzbY2068S
>> 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] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 06:23 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 06:23 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Yes, Thank you. I mentioned that here, with its disadvantage: https://www.postgresql.org/message-id/f69b47dbbe374bac8088887c21193f26%40stmuk.bayern.de :
> 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: Christophe Pettus <xof@thebuild.com>
Gesendet: Dienstag, 13. Oktober 2026 08:19
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?
Hi,
> On Oct 12, 2026, at 23:12, Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de> wrote:
>
> 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.
PostgreSQL implements this using foreign data wrappers.
^ permalink raw reply [nested|flat] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 06:54 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 1 reply; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 06:54 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
> ... because PostgreSQL is more statically typed ...
> You might not think of that as a feature, but it's the way PostgreSQL works.
I disagree.
It is not a statically-typing problem. My use case would easily be feasible if ...
* ... the PostgreSQL COPY ... FROM PROGRAM statement could be used like a query, without having to use a temp table. Due to typing, one would have to use a FROM clause with column names and types defined, just like tablefunc: https://www.postgresql.org/docs/current/tablefunc.html
* ... or there would be some chance to create a temp table inside a plpgsql function ... which in the end is used in my use case inside a materialized view ... which is forbidden and which can still be seen in the subject of this mail as the first question I had.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Christophe Pettus <xof@thebuild.com>
Gesendet: Dienstag, 13. Oktober 2026 08:34
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> On Oct 12, 2026, at 23:23, Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de> wrote:
>
> Yes, Thank you. I mentioned that here, with its disadvantage: https://www.postgresql.org/message-id/f69b47dbbe374bac8088887c21193f26%40stmuk.bayern.de :
I'm not sure why scripting CREATE FOREIGN TABLE is that complex, but you know your application best. PostgreSQL cannot easily do what DuckDB does and just have a .csv thrown at it as a table, because PostgreSQL is more statically typed than DuckDB, and would need a way to be told what types the columns are. You might not think of that as a feature, but it's the way PostgreSQL works.
PostgreSQL is not the right tool for every single job, and this may be a case where a different data engine will suit you better.
^ permalink raw reply [nested|flat] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 07:00 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 07:00 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
> It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function.
Because contrary to a function a foreign table is not parametrizable, I claimed I would have to create multiple foreign tables - compared to just one function.
Concerning the DRY (don't repeat yourself) principle, I thought a function would be the better choice.
As I somewhere said, I miss modularity / orthogonality in SQL / Postgres. We already mentioned multiple places in this thread. This is the recurring theme here.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Christophe Pettus <xof@thebuild.com>
Gesendet: Dienstag, 13. Oktober 2026 08:55
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> On Oct 12, 2026, at 23:54, Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de> wrote:
> * ... the PostgreSQL COPY ... FROM PROGRAM statement could be used like a query, without having to use a temp table. Due to typing, one would have to use a FROM clause with column names and types defined, just like tablefunc: https://www.postgresql.org/docs/current/tablefunc.html
> * ... or there would be some chance to create a temp table inside a plpgsql function ... which in the end is used in my use case inside a materialized view ... which is forbidden and which can still be seen in the subject of this mail as the first question I had.
It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function.
^ permalink raw reply [nested|flat] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 11:36 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 2 replies; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-13 11:36 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>
>> [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.
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 13:47 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2026-10-13 13:47 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 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] 12+ messages in thread
* Re: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 16:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
1 sibling, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2026-10-13 16:23 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/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] 12+ messages in thread
* Re: AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-13 16:48 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2026-10-13 16:48 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/13/26 9:23 AM, Adrian Klaver wrote:
> 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()
Forgive the function name, it was created before I realized I was not
really going with a temporary 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
>
>
>>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* AW: Why is materialized view creation a "security-restricted operation"?
@ 2026-10-14 17:38 Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
0 siblings, 0 replies; 12+ messages in thread
From: Färber, Franz-Josef (StMUK) @ 2026-10-14 17:38 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general
Yes, I would have to predefine the columns.
In my case it's just one column, one row, which i wanted to load from an external file.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Rob Sargent <robjsargent@gmail.com>
Gesendet: Dienstag, 13. Oktober 2026 17:37
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org; Haupt, Matthias (StMUK) <Matthias.Haupt@stmuk.bayern.de>
Betreff: Re: Why is materialized view creation a "security-restricted operation"?
> On Oct 13, 2026, at 08:17, Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de> 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.
>
I am not clear on your workflow so these questions come to mind:
What do you do with the undefined “*” (if it were allowed)?
Would you not have to predefine the columns of the table variable?
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-10-14 17:38 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-06 08:20 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-06 21:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-12 14:59 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-12 16:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-13 06:23 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 06:54 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 07:00 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 11:36 ` Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
2026-10-13 13:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-13 16:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-13 16:48 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-14 17:38 AW: Why is materialized view creation a "security-restricted operation"? 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