[PATCH] Add enable_copy_program GUC to control COPY PROGRAM
| От | Ignat Remizov |
|---|---|
| Тема | [PATCH] Add enable_copy_program GUC to control COPY PROGRAM |
| Дата | |
| Msg-id | CAKiC8XZ+ZG_mL_RNna8vO-cqfSYnOJwj+ggk4XVH1dDmmgxvnw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: [PATCH] Add enable_copy_program GUC to control COPY PROGRAM
Re: [PATCH] Add enable_copy_program GUC to control COPY PROGRAM |
| Список | pgsql-hackers |
Hi Postgres hackers,
Attached is a patch that introduces a new server-level configuration
parameter, "enable_copy_program", that allows administrators to disable
COPY ... PROGRAM functionality at the PostgreSQL server level.
MOTIVATION
==========
COPY ... PROGRAM is a powerful feature that allows executing arbitrary
shell commands from within PostgreSQL. While access is controlled via
the pg_execute_server_program role, some deployments may want to
completely disable this capability as a defense-in-depth measure.
This GUC provides that option.
In practice, COPY ... PROGRAM is a common code execution vector in
PostgreSQL-based attacks. It is:
- Simple to use (single SQL statement)
- Requires no additional extensions or setup
- Frequently targeted by automated botnets and malware campaigns
- Often the first technique attempted by attackers who gain superuser access
While this GUC is not a comprehensive security solution, it serves as a
mitigating control that removes some of the lowest-hanging fruit for
attackers.
IMPORTANT SECURITY CONTEXT
==========================
This is a mitigating control, not a security boundary.
There is ongoing ecosystem friction around the disputed CVE-2019-9193 entry
in the NVD. The PostgreSQL Security Team has stated that this CVE does not
represent a security bug in PostgreSQL and was filed in error, but NVD and
other CVE databases still list it as a remote code execution issue via COPY
TO/FROM PROGRAM, and several commercial scanners and IDS/IPS signatures treat
it as a high-severity vulnerability. This patch is not intended as a fix for
that CVE; it simply provides an explicit configuration knob for administrators
whose security tooling or policies require disabling program execution via COPY.
Superusers retain multiple other avenues for executing operating system
commands, including but not limited to:
- Untrusted procedural languages (CREATE EXTENSION plpythonu, plperlu, etc.)
- Custom extensions that provide shell access
- The LOAD command to load arbitrary shared libraries
- pg_read_file() / pg_write_file() combined with other techniques
- Foreign data wrappers with program execution capabilities
- Background workers in custom extensions
Disabling COPY ... PROGRAM does NOT make PostgreSQL secure against a
malicious superuser. However, it does:
1. Block a very common and highly automated attack vector – many botnet
payloads and exploit scripts specifically target COPY ... PROGRAM
because it requires no prerequisites.
2. Raise the bar for exploitation – attackers must use more complex,
less portable, or more detectable methods.
3. Reduce drive-by attacks – automated scanners and opportunistic
attackers often give up when their standard payload fails.
4. Help meet compliance requirements – some security frameworks mandate
disabling specific high-risk features.
5. Provide defense in depth – one layer in a broader security strategy.
KEY CHANGES
===========
New GUC parameter:
- Name: enable_copy_program
- Type: boolean
- Default: on (preserves existing behavior)
- Context: PGC_POSTMASTER (requires server restart to change)
- Flags: GUC_SUPERUSER_ONLY | GUC_DISALLOW_IN_AUTO_FILE
Implementation:
- copy.c
* Add a check in DoCopy() to reject COPY PROGRAM when disabled.
- guc_parameters.dat
* Register the new GUC parameter.
- guc.h
* Declare the enable_copy_program variable.
Documentation:
- config.sgml
* Document the parameter in the authentication section, including its
scope and limitations.
- copy.sgml
* Cross-reference the new parameter in the COPY command documentation.
- postgresql.conf.sample
* Add a commented sample entry.
Tests:
- copy.sql (regression tests) verifying:
* Default value is "on".
* ALTER SYSTEM SET is rejected (due to GUC_DISALLOW_IN_AUTO_FILE).
* SET is rejected (due to PGC_POSTMASTER context).
- t/050_copy_program.pl (TAP test) verifying:
* COPY TO PROGRAM works when enabled.
* COPY FROM PROGRAM works when enabled.
* Both are rejected with the expected error when disabled.
- Updated expected output files accordingly.
Misc:
- Fixed a typo in a copy.c comment:
* "pstdout | pstdout" -> "pstdin | pstdout".
BEHAVIOR
========
enable_copy_program COPY PROGRAM behavior
------------------- ------------------------------------------
on (default) Allowed (subject to role privileges)
off Rejected with error, even for superusers
Error message when disabled:
ERROR: COPY PROGRAM is disabled
HINT: Set enable_copy_program = on to allow COPY TO/FROM PROGRAM.
TESTING
=======
- All regression tests pass.
- Recovery TAP tests pass (injection-point skips are expected).
- New TAP test t/050_copy_program.pl validates both enabled and
disabled scenarios.
BACKWARD COMPATIBILITY
======================
This change is fully backward compatible. The default value "on"
preserves existing behavior. No action is required for existing
deployments unless they wish to disable COPY PROGRAM.
--
Ignat Remizov
From c642f17d0b44112ba5426be6412004e03d1a5e03 Mon Sep 17 00:00:00 2001
From: Ignat Remizov <ignat980@gmail.com>
Date: Wed, 3 Dec 2025 11:01:31 +0200
Subject: [PATCH] Add enable_copy_program GUC to control COPY PROGRAM
Introduce a new postmaster-only GUC parameter enable_copy_program to
control the use of COPY ... PROGRAM. By default this parameter is set
to on, allowing the use of COPY ... PROGRAM and preserving existing
behavior. When set to off, attempts to use COPY ... PROGRAM are
rejected, even for superusers or roles with the pg_execute_server_program
privilege.
Key changes:
- Add enable_copy_program as a postmaster-only GUC parameter.
- Update COPY command logic to enforce the enable_copy_program setting.
- Update documentation to reflect the new parameter and its behavior.
- Add regression and TAP tests to verify the functionality.
This hardening measure allows administrators to disable COPY ... PROGRAM
at the server level as a defense-in-depth control. The enable_copy_program
parameter requires a server restart to take effect and cannot be altered
via ALTER SYSTEM.
---
doc/src/sgml/config.sgml | 21 +++++++
doc/src/sgml/ref/copy.sgml | 4 +-
src/backend/commands/copy.c | 10 +++
src/backend/utils/misc/guc_parameters.dat | 8 +++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/psql/copy.c | 2 +-
src/include/utils/guc.h | 1 +
src/test/recovery/meson.build | 1 +
src/test/recovery/t/050_copy_program.pl | 62 +++++++++++++++++++
src/test/regress/expected/copy.out | 11 ++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/copy.sql | 5 ++
12 files changed, 126 insertions(+), 3 deletions(-)
create mode 100644 src/test/recovery/t/050_copy_program.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 737b90736bf..9b6575999cd 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1171,6 +1171,27 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-copy-program" xreflabel="enable_copy_program">
+ <term><varname>enable_copy_program</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>enable_copy_program</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether <command>COPY ... PROGRAM</command> is allowed. The
+ default is <literal>on</literal>; when set to <literal>off</literal>,
+ attempts to use <literal>PROGRAM</literal> are rejected even for
+ superusers or roles with <literal>pg_execute_server_program</literal>.
+ This setting does not affect <command>\copy</command> or
+ <command>COPY</command> to or from <literal>STDIN</literal>/<literal>STDOUT</literal>.
+ Changes require a server restart and cannot be made with
+ <command>ALTER SYSTEM</command>. This is a hardening measure, but not a security
+ boundary; superusers retain other ways to execute operating system commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
<term><varname>krb_server_keyfile</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 53b0ea8f573..e7a02e8403d 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -590,7 +590,9 @@ COPY <replaceable class="parameter">count</replaceable>
<literal>pg_write_server_files</literal>,
or <literal>pg_execute_server_program</literal>, since it allows reading
or writing any file or running a program that the server has privileges to
- access.
+ access. To disable program execution for <command>COPY</command>, set
+ <xref linkend="guc-enable-copy-program"/> to <literal>off</literal>
+ (restart required).
</para>
<para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 28e878c3688..63fa55f631d 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -39,6 +39,9 @@
#include "utils/rel.h"
#include "utils/rls.h"
+/* Controls whether COPY PROGRAM is permitted at all. */
+bool enable_copy_program = true;
+
/*
* DoCopy executes the SQL COPY statement
*
@@ -78,6 +81,13 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
{
if (stmt->is_program)
{
+ if (!enable_copy_program)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("COPY PROGRAM is disabled"),
+ errhint("Set enable_copy_program = on to allow COPY "
+ "TO/FROM PROGRAM.")));
+
if (!has_privs_of_role(GetUserId(), ROLE_PG_EXECUTE_SERVER_PROGRAM))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 3b9d8349078..22f0618ea58 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -819,6 +819,14 @@
boot_val => 'true',
},
+{ name => 'enable_copy_program', type => 'bool', context => 'PGC_POSTMASTER', group => 'CONN_AUTH_AUTH',
+ short_desc => 'Enables COPY to and from an external program.',
+ long_desc => 'When disabled, COPY PROGRAM is rejected even for superusers. This can only be changed at server start.',
+ flags => 'GUC_SUPERUSER_ONLY | GUC_DISALLOW_IN_AUTO_FILE',
+ variable => 'enable_copy_program',
+ boot_val => 'true',
+},
+
{ name => 'enable_distinct_reordering', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
short_desc => 'Enables reordering of DISTINCT keys.',
flags => 'GUC_EXPLAIN',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..7f3a2a63766 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -97,6 +97,7 @@
#password_encryption = scram-sha-256 # scram-sha-256 or (deprecated) md5
#scram_iterations = 4096
#md5_password_warnings = on # display md5 deprecation warnings?
+#enable_copy_program = on # allow COPY ... PROGRAM commands (restart)
#oauth_validator_libraries = '' # comma-separated list of trusted validator modules
# GSSAPI using Kerberos
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index 92c955b637a..04553c3a33f 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -33,7 +33,7 @@
* \copy ( query stmt ) to filename [options]
*
* where 'filename' can be one of the following:
- * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdout | pstdout
+ * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdin | pstdout
* and 'query' can be one of the following:
* SELECT | UPDATE | INSERT | DELETE
*
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index f21ec37da89..a19c3ebb2a6 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -291,6 +291,7 @@ extern PGDLLIMPORT bool check_function_bodies;
extern PGDLLIMPORT bool current_role_is_superuser;
extern PGDLLIMPORT bool AllowAlterSystem;
+extern PGDLLIMPORT bool enable_copy_program;
extern PGDLLIMPORT bool log_duration;
extern PGDLLIMPORT int log_parameter_max_length;
extern PGDLLIMPORT int log_parameter_max_length_on_error;
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 523a5cd5b52..4055b782e30 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -58,6 +58,7 @@ tests += {
't/047_checkpoint_physical_slot.pl',
't/048_vacuum_horizon_floor.pl',
't/049_wait_for_lsn.pl',
+ 't/050_copy_program.pl',
],
},
}
diff --git a/src/test/recovery/t/050_copy_program.pl b/src/test/recovery/t/050_copy_program.pl
new file mode 100644
index 00000000000..5ec6c146377
--- /dev/null
+++ b/src/test/recovery/t/050_copy_program.pl
@@ -0,0 +1,62 @@
+#
+# Verify COPY PROGRAM behavior when enabled.
+#
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node_on = PostgreSQL::Test::Cluster->new('enable_copy_program_on');
+$node_on->init;
+$node_on->start;
+
+# use perl as the external program for portability; $^X is portable enough
+my $perlbin = $^X;
+my $out_file = $node_on->basedir . '/enable_copy_program.out';
+
+# COPY ... TO PROGRAM should succeed when enabled.
+$node_on->safe_psql(
+ 'postgres',
+ "COPY (SELECT 42) TO PROGRAM "
+ . "'$perlbin -e \"print qq(42\\\\n)\" > $out_file'"
+);
+is(slurp_file($out_file), "42\n",
+ 'COPY TO PROGRAM writes to external program when enabled');
+
+# COPY ... FROM PROGRAM should succeed when enabled.
+my $in_prog = $node_on->basedir . '/enable_copy_program.sh';
+append_to_file($in_prog, "printf \"99\\n\";\n");
+chmod 0755, $in_prog or die "chmod failed for $in_prog: $!";
+$node_on->safe_psql('postgres', "CREATE TABLE copy_program_enabled(a int)");
+$node_on->safe_psql('postgres',
+ "COPY copy_program_enabled FROM PROGRAM '$in_prog'");
+is(
+ $node_on->safe_psql('postgres',
+ "TABLE copy_program_enabled ORDER BY 1"),
+ "99",
+ 'COPY FROM PROGRAM reads from external program when enabled');
+
+# COPY PROGRAM can be disabled at postmaster start.
+my $node_off = PostgreSQL::Test::Cluster->new('enable_copy_program_off');
+$node_off->init;
+$node_off->append_conf('postgresql.conf', "enable_copy_program=off");
+$node_off->start;
+my $should_not_write = $node_off->basedir . '/should_not_write';
+my ($ret, $stdout, $stderr) = $node_off->psql(
+ 'postgres',
+ "COPY (SELECT 1) TO PROGRAM "
+ . "'$perlbin -e \"print qq(1\\\\n)\" > $should_not_write'"
+);
+isnt($ret, 0, 'COPY PROGRAM fails when disabled');
+like(
+ $stderr,
+ qr/COPY PROGRAM is disabled/,
+ 'COPY PROGRAM disabled error shown');
+
+$node_on->stop;
+$node_off->stop;
+
+done_testing();
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 24e0f472f14..1034f6431f7 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -393,3 +393,14 @@ id val
5 15
6 16
DROP TABLE PP;
+-- COPY PROGRAM guard is postmaster-only and not alterable at runtime
+SHOW enable_copy_program;
+ enable_copy_program
+---------------------
+ on
+(1 row)
+
+ALTER SYSTEM SET enable_copy_program = off; -- fail (auto conf)
+ERROR: parameter "enable_copy_program" cannot be changed
+SET enable_copy_program = off; -- fail (postmaster)
+ERROR: parameter "enable_copy_program" cannot be changed without restarting the server
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 3b37fafa65b..ac1ba6522f3 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -150,6 +150,7 @@ select name, setting from pg_settings where name like 'enable%';
--------------------------------+---------
enable_async_append | on
enable_bitmapscan | on
+ enable_copy_program | on
enable_distinct_reordering | on
enable_eager_aggregate | on
enable_gathermerge | on
@@ -173,7 +174,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 676a8b342b5..c98a6564479 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -419,3 +419,8 @@ CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
COPY pp TO stdout(header);
DROP TABLE PP;
+
+-- COPY PROGRAM guard is postmaster-only and not alterable at runtime
+SHOW enable_copy_program;
+ALTER SYSTEM SET enable_copy_program = off; -- fail (auto conf)
+SET enable_copy_program = off; -- fail (postmaster)
--
2.43.0
Attached is a patch that introduces a new server-level configuration
parameter, "enable_copy_program", that allows administrators to disable
COPY ... PROGRAM functionality at the PostgreSQL server level.
MOTIVATION
==========
COPY ... PROGRAM is a powerful feature that allows executing arbitrary
shell commands from within PostgreSQL. While access is controlled via
the pg_execute_server_program role, some deployments may want to
completely disable this capability as a defense-in-depth measure.
This GUC provides that option.
In practice, COPY ... PROGRAM is a common code execution vector in
PostgreSQL-based attacks. It is:
- Simple to use (single SQL statement)
- Requires no additional extensions or setup
- Frequently targeted by automated botnets and malware campaigns
- Often the first technique attempted by attackers who gain superuser access
While this GUC is not a comprehensive security solution, it serves as a
mitigating control that removes some of the lowest-hanging fruit for
attackers.
IMPORTANT SECURITY CONTEXT
==========================
This is a mitigating control, not a security boundary.
There is ongoing ecosystem friction around the disputed CVE-2019-9193 entry
in the NVD. The PostgreSQL Security Team has stated that this CVE does not
represent a security bug in PostgreSQL and was filed in error, but NVD and
other CVE databases still list it as a remote code execution issue via COPY
TO/FROM PROGRAM, and several commercial scanners and IDS/IPS signatures treat
it as a high-severity vulnerability. This patch is not intended as a fix for
that CVE; it simply provides an explicit configuration knob for administrators
whose security tooling or policies require disabling program execution via COPY.
Superusers retain multiple other avenues for executing operating system
commands, including but not limited to:
- Untrusted procedural languages (CREATE EXTENSION plpythonu, plperlu, etc.)
- Custom extensions that provide shell access
- The LOAD command to load arbitrary shared libraries
- pg_read_file() / pg_write_file() combined with other techniques
- Foreign data wrappers with program execution capabilities
- Background workers in custom extensions
Disabling COPY ... PROGRAM does NOT make PostgreSQL secure against a
malicious superuser. However, it does:
1. Block a very common and highly automated attack vector – many botnet
payloads and exploit scripts specifically target COPY ... PROGRAM
because it requires no prerequisites.
2. Raise the bar for exploitation – attackers must use more complex,
less portable, or more detectable methods.
3. Reduce drive-by attacks – automated scanners and opportunistic
attackers often give up when their standard payload fails.
4. Help meet compliance requirements – some security frameworks mandate
disabling specific high-risk features.
5. Provide defense in depth – one layer in a broader security strategy.
KEY CHANGES
===========
New GUC parameter:
- Name: enable_copy_program
- Type: boolean
- Default: on (preserves existing behavior)
- Context: PGC_POSTMASTER (requires server restart to change)
- Flags: GUC_SUPERUSER_ONLY | GUC_DISALLOW_IN_AUTO_FILE
Implementation:
- copy.c
* Add a check in DoCopy() to reject COPY PROGRAM when disabled.
- guc_parameters.dat
* Register the new GUC parameter.
- guc.h
* Declare the enable_copy_program variable.
Documentation:
- config.sgml
* Document the parameter in the authentication section, including its
scope and limitations.
- copy.sgml
* Cross-reference the new parameter in the COPY command documentation.
- postgresql.conf.sample
* Add a commented sample entry.
Tests:
- copy.sql (regression tests) verifying:
* Default value is "on".
* ALTER SYSTEM SET is rejected (due to GUC_DISALLOW_IN_AUTO_FILE).
* SET is rejected (due to PGC_POSTMASTER context).
- t/050_copy_program.pl (TAP test) verifying:
* COPY TO PROGRAM works when enabled.
* COPY FROM PROGRAM works when enabled.
* Both are rejected with the expected error when disabled.
- Updated expected output files accordingly.
Misc:
- Fixed a typo in a copy.c comment:
* "pstdout | pstdout" -> "pstdin | pstdout".
BEHAVIOR
========
enable_copy_program COPY PROGRAM behavior
------------------- ------------------------------------------
on (default) Allowed (subject to role privileges)
off Rejected with error, even for superusers
Error message when disabled:
ERROR: COPY PROGRAM is disabled
HINT: Set enable_copy_program = on to allow COPY TO/FROM PROGRAM.
TESTING
=======
- All regression tests pass.
- Recovery TAP tests pass (injection-point skips are expected).
- New TAP test t/050_copy_program.pl validates both enabled and
disabled scenarios.
BACKWARD COMPATIBILITY
======================
This change is fully backward compatible. The default value "on"
preserves existing behavior. No action is required for existing
deployments unless they wish to disable COPY PROGRAM.
--
Ignat Remizov
From c642f17d0b44112ba5426be6412004e03d1a5e03 Mon Sep 17 00:00:00 2001
From: Ignat Remizov <ignat980@gmail.com>
Date: Wed, 3 Dec 2025 11:01:31 +0200
Subject: [PATCH] Add enable_copy_program GUC to control COPY PROGRAM
Introduce a new postmaster-only GUC parameter enable_copy_program to
control the use of COPY ... PROGRAM. By default this parameter is set
to on, allowing the use of COPY ... PROGRAM and preserving existing
behavior. When set to off, attempts to use COPY ... PROGRAM are
rejected, even for superusers or roles with the pg_execute_server_program
privilege.
Key changes:
- Add enable_copy_program as a postmaster-only GUC parameter.
- Update COPY command logic to enforce the enable_copy_program setting.
- Update documentation to reflect the new parameter and its behavior.
- Add regression and TAP tests to verify the functionality.
This hardening measure allows administrators to disable COPY ... PROGRAM
at the server level as a defense-in-depth control. The enable_copy_program
parameter requires a server restart to take effect and cannot be altered
via ALTER SYSTEM.
---
doc/src/sgml/config.sgml | 21 +++++++
doc/src/sgml/ref/copy.sgml | 4 +-
src/backend/commands/copy.c | 10 +++
src/backend/utils/misc/guc_parameters.dat | 8 +++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/psql/copy.c | 2 +-
src/include/utils/guc.h | 1 +
src/test/recovery/meson.build | 1 +
src/test/recovery/t/050_copy_program.pl | 62 +++++++++++++++++++
src/test/regress/expected/copy.out | 11 ++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/copy.sql | 5 ++
12 files changed, 126 insertions(+), 3 deletions(-)
create mode 100644 src/test/recovery/t/050_copy_program.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 737b90736bf..9b6575999cd 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1171,6 +1171,27 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-copy-program" xreflabel="enable_copy_program">
+ <term><varname>enable_copy_program</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>enable_copy_program</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether <command>COPY ... PROGRAM</command> is allowed. The
+ default is <literal>on</literal>; when set to <literal>off</literal>,
+ attempts to use <literal>PROGRAM</literal> are rejected even for
+ superusers or roles with <literal>pg_execute_server_program</literal>.
+ This setting does not affect <command>\copy</command> or
+ <command>COPY</command> to or from <literal>STDIN</literal>/<literal>STDOUT</literal>.
+ Changes require a server restart and cannot be made with
+ <command>ALTER SYSTEM</command>. This is a hardening measure, but not a security
+ boundary; superusers retain other ways to execute operating system commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-krb-server-keyfile" xreflabel="krb_server_keyfile">
<term><varname>krb_server_keyfile</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 53b0ea8f573..e7a02e8403d 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -590,7 +590,9 @@ COPY <replaceable class="parameter">count</replaceable>
<literal>pg_write_server_files</literal>,
or <literal>pg_execute_server_program</literal>, since it allows reading
or writing any file or running a program that the server has privileges to
- access.
+ access. To disable program execution for <command>COPY</command>, set
+ <xref linkend="guc-enable-copy-program"/> to <literal>off</literal>
+ (restart required).
</para>
<para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 28e878c3688..63fa55f631d 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -39,6 +39,9 @@
#include "utils/rel.h"
#include "utils/rls.h"
+/* Controls whether COPY PROGRAM is permitted at all. */
+bool enable_copy_program = true;
+
/*
* DoCopy executes the SQL COPY statement
*
@@ -78,6 +81,13 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
{
if (stmt->is_program)
{
+ if (!enable_copy_program)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("COPY PROGRAM is disabled"),
+ errhint("Set enable_copy_program = on to allow COPY "
+ "TO/FROM PROGRAM.")));
+
if (!has_privs_of_role(GetUserId(), ROLE_PG_EXECUTE_SERVER_PROGRAM))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 3b9d8349078..22f0618ea58 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -819,6 +819,14 @@
boot_val => 'true',
},
+{ name => 'enable_copy_program', type => 'bool', context => 'PGC_POSTMASTER', group => 'CONN_AUTH_AUTH',
+ short_desc => 'Enables COPY to and from an external program.',
+ long_desc => 'When disabled, COPY PROGRAM is rejected even for superusers. This can only be changed at server start.',
+ flags => 'GUC_SUPERUSER_ONLY | GUC_DISALLOW_IN_AUTO_FILE',
+ variable => 'enable_copy_program',
+ boot_val => 'true',
+},
+
{ name => 'enable_distinct_reordering', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
short_desc => 'Enables reordering of DISTINCT keys.',
flags => 'GUC_EXPLAIN',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..7f3a2a63766 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -97,6 +97,7 @@
#password_encryption = scram-sha-256 # scram-sha-256 or (deprecated) md5
#scram_iterations = 4096
#md5_password_warnings = on # display md5 deprecation warnings?
+#enable_copy_program = on # allow COPY ... PROGRAM commands (restart)
#oauth_validator_libraries = '' # comma-separated list of trusted validator modules
# GSSAPI using Kerberos
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index 92c955b637a..04553c3a33f 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -33,7 +33,7 @@
* \copy ( query stmt ) to filename [options]
*
* where 'filename' can be one of the following:
- * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdout | pstdout
+ * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdin | pstdout
* and 'query' can be one of the following:
* SELECT | UPDATE | INSERT | DELETE
*
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index f21ec37da89..a19c3ebb2a6 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -291,6 +291,7 @@ extern PGDLLIMPORT bool check_function_bodies;
extern PGDLLIMPORT bool current_role_is_superuser;
extern PGDLLIMPORT bool AllowAlterSystem;
+extern PGDLLIMPORT bool enable_copy_program;
extern PGDLLIMPORT bool log_duration;
extern PGDLLIMPORT int log_parameter_max_length;
extern PGDLLIMPORT int log_parameter_max_length_on_error;
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 523a5cd5b52..4055b782e30 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -58,6 +58,7 @@ tests += {
't/047_checkpoint_physical_slot.pl',
't/048_vacuum_horizon_floor.pl',
't/049_wait_for_lsn.pl',
+ 't/050_copy_program.pl',
],
},
}
diff --git a/src/test/recovery/t/050_copy_program.pl b/src/test/recovery/t/050_copy_program.pl
new file mode 100644
index 00000000000..5ec6c146377
--- /dev/null
+++ b/src/test/recovery/t/050_copy_program.pl
@@ -0,0 +1,62 @@
+#
+# Verify COPY PROGRAM behavior when enabled.
+#
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node_on = PostgreSQL::Test::Cluster->new('enable_copy_program_on');
+$node_on->init;
+$node_on->start;
+
+# use perl as the external program for portability; $^X is portable enough
+my $perlbin = $^X;
+my $out_file = $node_on->basedir . '/enable_copy_program.out';
+
+# COPY ... TO PROGRAM should succeed when enabled.
+$node_on->safe_psql(
+ 'postgres',
+ "COPY (SELECT 42) TO PROGRAM "
+ . "'$perlbin -e \"print qq(42\\\\n)\" > $out_file'"
+);
+is(slurp_file($out_file), "42\n",
+ 'COPY TO PROGRAM writes to external program when enabled');
+
+# COPY ... FROM PROGRAM should succeed when enabled.
+my $in_prog = $node_on->basedir . '/enable_copy_program.sh';
+append_to_file($in_prog, "printf \"99\\n\";\n");
+chmod 0755, $in_prog or die "chmod failed for $in_prog: $!";
+$node_on->safe_psql('postgres', "CREATE TABLE copy_program_enabled(a int)");
+$node_on->safe_psql('postgres',
+ "COPY copy_program_enabled FROM PROGRAM '$in_prog'");
+is(
+ $node_on->safe_psql('postgres',
+ "TABLE copy_program_enabled ORDER BY 1"),
+ "99",
+ 'COPY FROM PROGRAM reads from external program when enabled');
+
+# COPY PROGRAM can be disabled at postmaster start.
+my $node_off = PostgreSQL::Test::Cluster->new('enable_copy_program_off');
+$node_off->init;
+$node_off->append_conf('postgresql.conf', "enable_copy_program=off");
+$node_off->start;
+my $should_not_write = $node_off->basedir . '/should_not_write';
+my ($ret, $stdout, $stderr) = $node_off->psql(
+ 'postgres',
+ "COPY (SELECT 1) TO PROGRAM "
+ . "'$perlbin -e \"print qq(1\\\\n)\" > $should_not_write'"
+);
+isnt($ret, 0, 'COPY PROGRAM fails when disabled');
+like(
+ $stderr,
+ qr/COPY PROGRAM is disabled/,
+ 'COPY PROGRAM disabled error shown');
+
+$node_on->stop;
+$node_off->stop;
+
+done_testing();
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 24e0f472f14..1034f6431f7 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -393,3 +393,14 @@ id val
5 15
6 16
DROP TABLE PP;
+-- COPY PROGRAM guard is postmaster-only and not alterable at runtime
+SHOW enable_copy_program;
+ enable_copy_program
+---------------------
+ on
+(1 row)
+
+ALTER SYSTEM SET enable_copy_program = off; -- fail (auto conf)
+ERROR: parameter "enable_copy_program" cannot be changed
+SET enable_copy_program = off; -- fail (postmaster)
+ERROR: parameter "enable_copy_program" cannot be changed without restarting the server
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 3b37fafa65b..ac1ba6522f3 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -150,6 +150,7 @@ select name, setting from pg_settings where name like 'enable%';
--------------------------------+---------
enable_async_append | on
enable_bitmapscan | on
+ enable_copy_program | on
enable_distinct_reordering | on
enable_eager_aggregate | on
enable_gathermerge | on
@@ -173,7 +174,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 676a8b342b5..c98a6564479 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -419,3 +419,8 @@ CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
COPY pp TO stdout(header);
DROP TABLE PP;
+
+-- COPY PROGRAM guard is postmaster-only and not alterable at runtime
+SHOW enable_copy_program;
+ALTER SYSTEM SET enable_copy_program = off; -- fail (auto conf)
+SET enable_copy_program = off; -- fail (postmaster)
--
2.43.0
В списке pgsql-hackers по дате отправления: