Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXjKa-00CJqd-Af for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 14:55:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXjKX-00BMcO-SK for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 14:55:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXjKX-00BMcG-G5 for pgsql-general@lists.postgresql.org; Wed, 07 Feb 2024 14:55:17 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXjKU-005xiL-CW for pgsql-general@postgresql.org; Wed, 07 Feb 2024 14:55:16 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-60499a12ca0so4402287b3.2 for ; Wed, 07 Feb 2024 06:55:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1707317712; x=1707922512; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:autocrypt:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=QtgwD9/iSD3X1BKIJ+rhw0UvrvtFlVL/df8ThKQ6ljc=; b=rFm5gsNWbvTgAwIn7VOC7UtuhNR5CaJX/1kY2UxXX8LFaHH5a0g9quJKo8S2ZVi4FR 5Ry7BbeY5NwDHgYsa6VbJfk71CIW8IhLZbyJNhXyARjeqTcenKm0JUubsWa4Ge3Uxue9 Qo7ubnYp8qi/Kry9YjE/hbrjzuy1WeqVP0LzuigyZXIIVEFoJeiQy3oDrBmBCMYLqvOx NiX/nVeWH+jF7i43yAej87RSVSkVGoMGTmmrZAqOXu7OR7DwpxHwwbw5PcyzNTjxabFy yX6ecI3j1O6qE/MmOMutlpsU/lFeLFdUpFpo/WgD0lcuopipACqGkrC56vYNuchuAwnK pWow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707317712; x=1707922512; h=content-transfer-encoding:in-reply-to:autocrypt:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=QtgwD9/iSD3X1BKIJ+rhw0UvrvtFlVL/df8ThKQ6ljc=; b=B22XfaENNYYAtdmAmI4kJg5FJZjmpfbS5utRPgnENUqvrrmcJujqcHETsVpLJ8NTRe rD9rDhDmrLi8fY6xV2EtOgPhEj8x0MoxHW/Jlcm1anLsZYsYhLeTm4I+pMzuGK4Lm0gW ERxIM8KA80rfmmJYc1D2YN3Xsr6pgnWSZ5T2Q94fQmzGfzIUI8T23ib5eyi0IgXJGS9v vTc3ugaLhU2E2SuYcr4QUd2HLeSRK7Kp0EfIgqrW4Q0dWNWKV+Wtlp6TH25diiBDLGLI 354EGx4nYm/N2y8aSJ4jg1wpcKCuGnSWy3dM3Pn8Jr/HINOYmWQzdUV21AbV9PXXH+mr +2zg== X-Gm-Message-State: AOJu0YzMo5LhVS3m8NZNdFU2piahX8UwalaMZnGRkgyTi/C2sLnAc/fh 9cuscFd0k55qAWXoHBclqxEV6fB0kJO0MErrXv0uEsM3G7BKaE4e3e5gpZWpXzA= X-Google-Smtp-Source: AGHT+IFvyN2W5XMwqy1fUJnbvZoyghJU1TQ8r2ZQntYcseUrRD70lks7bIP2M1WoQKkp75yxUSIlZQ== X-Received: by 2002:a81:aa46:0:b0:5f4:a5ab:4105 with SMTP id z6-20020a81aa46000000b005f4a5ab4105mr5397768ywk.8.1707317712602; Wed, 07 Feb 2024 06:55:12 -0800 (PST) X-Forwarded-Encrypted: i=1; AJvYcCXYtO0O16cE0ZL9qJGGR8WKUGibELliXwef5rYdaEF2XXEy4ZFsDa69gDuzEp4t8BA4DlZNFdRJpte+QMP62e+yuFqWMnw1A69lbFuY Received: from [192.168.4.41] (162-239-31-113.lightspeed.dybhfl.sbcglobal.net. [162.239.31.113]) by smtp.gmail.com with ESMTPSA id fv2-20020a05690c370200b005ff821bfdd3sm279058ywb.27.2024.02.07.06.55.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 07 Feb 2024 06:55:12 -0800 (PST) Message-ID: <27439ea4-9a05-4057-becc-09e4efce742a@joeconway.com> Date: Wed, 7 Feb 2024 09:54:49 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Generic File Access Function to read program output Content-Language: en-US To: Carsten Klein , pgsql-general References: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com> From: Joe Conway Autocrypt: addr=mail@joeconway.com; keydata= xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+ 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q= In-Reply-To: <198ed5ac-8cdd-48b7-876e-42abeb15a15e@datagis.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 > > 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