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 <[email protected]>
Gesendet: Dienstag, 13. Oktober 2026 13:06
An: Färber, Franz-Josef (StMUK) <[email protected]>; Adrian Klaver
<[email protected]>; [email protected]
Cc: Haupt, Matthias (StMUK) <[email protected]>
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
[email protected]
