From: Carsten Klein <c.klein@datagis.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Generic File Access Function to read program output
Date: Wed, 7 Feb 2024 11:28:25 +0100
Message-ID: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com> (raw)
Hi there,
on PostgreSQL 14, I'm using function pg_read_file to read a JSON file on
the server. After that, the JSON file gets casted to jsonb and with
function jsonb_array_elements I'm iterating over the "records", which I
transform into a PostgreSQL ROWTYPE with jsonb_populate_record...
Since the source files are actually XML files, these are turned into
JSON files with Node JS and the fast-xml-parser module (processing JSON
is much faster and more comfortable than processing XML in PostgreSQL).
The command line of this conversion process is like this:
# node /opt/my_node_apps/xml_to_json.js <path_to_xml_file>
In order to do this without temporary JSON files (which need to be
deleted at some time), it would be great to have a new Generic File
Access Function
pg_read_program_output(command)
Although one could argue, that it's not a Generic *File* Access
Function, that function would be a worthwhile addition and could use the
same semantics and rules as with the
COPY table_name FROM PROGRAM 'command'
statement. Also the implementation (running a command with the shell and
capture it's STDOUT) is nearly the same.
In contrast to the other Generic File Access Functions, it will be
almost impossible to restrict access to programs or commands within the
database cluster directory (could be a complex shell command). Aside
from that this makes no sense since, typically, there are no executable
programs in those directories.
Even worse, it's likely also not possible to restrict the source of the
content read (the STDOUT) to be any of these directories, since the
program could just dump anything to its STDOUT.
AFAIT, that's not really an issue but only makes this new Generic File
Access Function special, in that these restrictions and the meaning of
role pg_read_server_files just do not apply for it.
Do you know if there is already such a function, maybe provided by an
extension I do not yet know?
Cheers
Carsten
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: c.klein@datagis.com
Subject: Re: Generic File Access Function to read program output
In-Reply-To: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com>
References: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.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