Skip to content

Commit 3d009e4

Browse files
hlinnakahlinnaka
committed
Add support for piping COPY to/from an external program.
This includes backend "COPY TO/FROM PROGRAM '...'" syntax, and corresponding psql \copy syntax. Like with reading/writing files, the backend version is superuser-only, and in the psql version, the program is run in the client. In the passing, the psql \copy STDIN/STDOUT syntax is subtly changed: if you the stdin/stdout is quoted, it's now interpreted as a filename. For example, "\copy foo from 'stdin'" now reads from a file called 'stdin', not from standard input. Before this, there was no way to specify a filename called stdin, stdout, pstdin or pstdout. This creates a new function in pgport, wait_result_to_str(), which can be used to convert the exit status of a process, as returned by wait(3), to a human-readable string. Etsuro Fujita, reviewed by Amit Kapila.
1 parent 73dc003 commit 3d009e4

File tree

21 files changed

+581
-149
lines changed

21 files changed

+581
-149
lines changed

‎contrib/file_fdw/file_fdw.c

+3-1
Original file line numberDiff line numberDiff line change
@@ -588,6 +588,7 @@ fileBeginForeignScan(ForeignScanState *node, int eflags)
588588
*/
589589
cstate = BeginCopyFrom(node->ss.ss_currentRelation,
590590
filename,
591+
false,
591592
NIL,
592593
options);
593594

@@ -660,6 +661,7 @@ fileReScanForeignScan(ForeignScanState *node)
660661

661662
festate->cstate = BeginCopyFrom(node->ss.ss_currentRelation,
662663
festate->filename,
664+
false,
663665
NIL,
664666
festate->options);
665667
}
@@ -993,7 +995,7 @@ file_acquire_sample_rows(Relation onerel, int elevel,
993995
/*
994996
* Create CopyState from FDW options.
995997
*/
996-
cstate = BeginCopyFrom(onerel, filename, NIL, options);
998+
cstate = BeginCopyFrom(onerel, filename, false, NIL, options);
997999

9981000
/*
9991001
* Use per-tuple memory context to prevent leak of memory used to read

‎doc/src/sgml/keywords.sgml

+7
Original file line numberDiff line numberDiff line change
@@ -3513,6 +3513,13 @@
35133513
<entry>reserved</entry>
35143514
<entry>reserved</entry>
35153515
</row>
3516+
<row>
3517+
<entry><token>PROGRAM</token></entry>
3518+
<entry>non-reserved</entry>
3519+
<entry></entry>
3520+
<entry></entry>
3521+
<entry></entry>
3522+
</row>
35163523
<row>
35173524
<entry><token>PUBLIC</token></entry>
35183525
<entry></entry>

‎doc/src/sgml/ref/copy.sgml

+45-5
Original file line numberDiff line numberDiff line change
@@ -23,11 +23,11 @@ PostgreSQL documentation
2323
<refsynopsisdiv>
2424
<synopsis>
2525
COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
26-
FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
26+
FROM { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDIN }
2727
[ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
2828

2929
COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
30-
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
30+
TO { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDOUT }
3131
[ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
3232

3333
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -72,6 +72,10 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
7272
<productname>PostgreSQL</productname> server to directly read from
7373
or write to a file. The file must be accessible to the server and
7474
the name must be specified from the viewpoint of the server. When
75+
<literal>PROGRAM</literal> is specified, the server executes the
76+
given command, and reads from its standard input, or writes to its
77+
standard output. The command must be specified from the viewpoint of the
78+
server, and be executable by the <literal>postgres</> user. When
7579
<literal>STDIN</literal> or <literal>STDOUT</literal> is
7680
specified, data is transmitted via the connection between the
7781
client and the server.
@@ -125,6 +129,25 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
125129
</listitem>
126130
</varlistentry>
127131

132+
<varlistentry>
133+
<term><literal>PROGRAM</literal></term>
134+
<listitem>
135+
<para>
136+
A command to execute. In <command>COPY FROM</command>, the input is
137+
read from standard output of the command, and in <command>COPY TO</>,
138+
the output is written to the standard input of the command.
139+
</para>
140+
<para>
141+
Note that the command is invoked by the shell, so if you need to pass
142+
any arguments to shell command that come from an untrusted source, you
143+
must be careful to strip or escape any special characters that might
144+
have a special meaning for the shell. For security reasons, it is best
145+
to use a fixed command string, or at least avoid passing any user input
146+
in it.
147+
</para>
148+
</listitem>
149+
</varlistentry>
150+
128151
<varlistentry>
129152
<term><literal>STDIN</literal></term>
130153
<listitem>
@@ -367,9 +390,13 @@ COPY <replaceable class="parameter">count</replaceable>
367390
they must reside on or be accessible to the database server machine,
368391
not the client. They must be accessible to and readable or writable
369392
by the <productname>PostgreSQL</productname> user (the user ID the
370-
server runs as), not the client. <command>COPY</command> naming a
371-
file is only allowed to database superusers, since it allows reading
372-
or writing any file that the server has privileges to access.
393+
server runs as), not the client. Similarly,
394+
the command specified with <literal>PROGRAM</literal> is executed directly
395+
by the server, not by the client application, must be executable by the
396+
<productname>PostgreSQL</productname> user.
397+
<command>COPY</command> naming a file or command is only allowed to
398+
database superusers, since it allows reading or writing any file that the
399+
server has privileges to access.
373400
</para>
374401

375402
<para>
@@ -393,6 +420,11 @@ COPY <replaceable class="parameter">count</replaceable>
393420
the cluster's data directory), not the client's working directory.
394421
</para>
395422

423+
<para>
424+
Executing a command with <literal>PROGRAM</literal> might be restricted
425+
by operating system's access control mechanisms, such as the SELinux.
426+
</para>
427+
396428
<para>
397429
<command>COPY FROM</command> will invoke any triggers and check
398430
constraints on the destination table. However, it will not invoke rules.
@@ -841,6 +873,14 @@ COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sq
841873
</programlisting>
842874
</para>
843875

876+
<para>
877+
To copy into a compressed file, you can pipe the output through an external
878+
compression program:
879+
<programlisting>
880+
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';
881+
</programlisting>
882+
</para>
883+
844884
<para>
845885
Here is a sample of data suitable for copying into a table from
846886
<literal>STDIN</literal>:

‎doc/src/sgml/ref/psql-ref.sgml

+22-11
Original file line numberDiff line numberDiff line change
@@ -830,7 +830,7 @@ testdb=&gt;
830830
<varlistentry id="APP-PSQL-meta-commands-copy">
831831
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
832832
{ <literal>from</literal> | <literal>to</literal> }
833-
{ <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
833+
{ <replaceable class="parameter">'filename'</replaceable> | program <replaceable class="parameter">'command'</replaceable> | stdin | stdout | pstdin | pstdout }
834834
[ [ with ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]</literal></term>
835835

836836
<listitem>
@@ -847,16 +847,14 @@ testdb=&gt;
847847
</para>
848848

849849
<para>
850-
The syntax of the command is similar to that of the
851-
<acronym>SQL</acronym> <xref linkend="sql-copy">
852-
command, and
853-
<replaceable class="parameter">option</replaceable>
854-
must indicate one of the options of the
855-
<acronym>SQL</acronym> <xref linkend="sql-copy"> command.
856-
Note that, because of this,
857-
special parsing rules apply to the <command>\copy</command>
858-
command. In particular, the variable substitution rules and
859-
backslash escapes do not apply.
850+
When <literal>program</> is specified,
851+
<replaceable class="parameter">command</replaceable> is
852+
executed by <application>psql</application> and the data from
853+
or to <replaceable class="parameter">command</replaceable> is
854+
routed between the server and the client.
855+
This means that the execution privileges are those of
856+
the local user, not the server, and no SQL superuser
857+
privileges are required.
860858
</para>
861859

862860
<para><literal>\copy ... from stdin | to stdout</literal>
@@ -870,6 +868,19 @@ testdb=&gt;
870868
for populating tables in-line within a SQL script file.
871869
</para>
872870

871+
<para>
872+
The syntax of the command is similar to that of the
873+
<acronym>SQL</acronym> <xref linkend="sql-copy">
874+
command, and
875+
<replaceable class="parameter">option</replaceable>
876+
must indicate one of the options of the
877+
<acronym>SQL</acronym> <xref linkend="sql-copy"> command.
878+
Note that, because of this,
879+
special parsing rules apply to the <command>\copy</command>
880+
command. In particular, the variable substitution rules and
881+
backslash escapes do not apply.
882+
</para>
883+
873884
<tip>
874885
<para>
875886
This operation is not as efficient as the <acronym>SQL</acronym>

0 commit comments

Comments
 (0)