From: Joe Conway <mail@joeconway.com>
To: Carsten Klein <c.klein@datagis.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Generic File Access Function to read program output
Date: Wed, 7 Feb 2024 09:54:49 -0500
Message-ID: <27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com> (raw)
In-Reply-To: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com>
References: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com>
On 2/7/24 05:28, Carsten Klein wrote:
> 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?
Maybe write your own in plpython or plperlu?
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
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: mail@joeconway.com, c.klein@datagis.com
Subject: Re: Generic File Access Function to read program output
In-Reply-To: <27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com>
References: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com> <27439ea4-9a05-4057-becc-09e4efce742a@joeconway.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