pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (3+ messages)  latest in thread

Message-ID: <27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com>
Permalink:  ../27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com/
Also on:    postgresql.org/message-id/27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com

 · 

reply

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