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







view thread (3+ messages)  latest in thread

Message-ID: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com>
Permalink:  ../198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com/
Also on:    postgresql.org/message-id/198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.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: 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