From 3d009e45bde2a2681826ef549637ada76508b597 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Wed, 27 Feb 2013 18:17:21 +0200 Subject: 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. --- doc/src/sgml/keywords.sgml | 7 ++++++ doc/src/sgml/ref/copy.sgml | 50 +++++++++++++++++++++++++++++++++++++----- doc/src/sgml/ref/psql-ref.sgml | 33 ++++++++++++++++++---------- 3 files changed, 74 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index 0e7b3228514..576fd65f316 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -3513,6 +3513,13 @@ reserved reserved + + PROGRAM + non-reserved + + + + PUBLIC diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 2137c67cb4b..2854d9c0ca6 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -23,11 +23,11 @@ PostgreSQL documentation COPY table_name [ ( column_name [, ...] ) ] - FROM { 'filename' | STDIN } + FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } - TO { 'filename' | STDOUT } + TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: @@ -72,6 +72,10 @@ COPY { table_name [ ( PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server. When + PROGRAM is specified, the server executes the + given command, and reads from its standard input, or writes to its + standard output. The command must be specified from the viewpoint of the + server, and be executable by the postgres user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server. @@ -125,6 +129,25 @@ COPY { table_name [ ( + + PROGRAM + + + A command to execute. In COPY FROM, the input is + read from standard output of the command, and in COPY TO, + the output is written to the standard input of the command. + + + Note that the command is invoked by the shell, so if you need to pass + any arguments to shell command that come from an untrusted source, you + must be careful to strip or escape any special characters that might + have a special meaning for the shell. For security reasons, it is best + to use a fixed command string, or at least avoid passing any user input + in it. + + + + STDIN @@ -367,9 +390,13 @@ COPY count they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the - server runs as), not the client. COPY naming a - file is only allowed to database superusers, since it allows reading - or writing any file that the server has privileges to access. + server runs as), not the client. Similarly, + the command specified with PROGRAM is executed directly + by the server, not by the client application, must be executable by the + PostgreSQL user. + COPY naming a file or command is only allowed to + database superusers, since it allows reading or writing any file that the + server has privileges to access. @@ -393,6 +420,11 @@ COPY count the cluster's data directory), not the client's working directory. + + Executing a command with PROGRAM might be restricted + by operating system's access control mechanisms, such as the SELinux. + + COPY FROM will invoke any triggers and check 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 + + To copy into a compressed file, you can pipe the output through an external + compression program: + +COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz'; + + + Here is a sample of data suitable for copying into a table from STDIN: diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 465d3a1882d..fb63845a260 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -830,7 +830,7 @@ testdb=> \copy { table [ ( column_list ) ] | ( query ) } { from | to } - { filename | stdin | stdout | pstdin | pstdout } + { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ] @@ -847,16 +847,14 @@ testdb=> - The syntax of the command is similar to that of the - SQL - command, and - option - must indicate one of the options of the - SQL command. - Note that, because of this, - special parsing rules apply to the \copy - command. In particular, the variable substitution rules and - backslash escapes do not apply. + When program is specified, + command is + executed by psql and the data from + or to command is + routed between the server and the client. + This means that the execution privileges are those of + the local user, not the server, and no SQL superuser + privileges are required. \copy ... from stdin | to stdout @@ -870,6 +868,19 @@ testdb=> for populating tables in-line within a SQL script file. + + The syntax of the command is similar to that of the + SQL + command, and + option + must indicate one of the options of the + SQL command. + Note that, because of this, + special parsing rules apply to the \copy + command. In particular, the variable substitution rules and + backslash escapes do not apply. + + This operation is not as efficient as the SQL -- cgit v1.2.3