Обсуждение: [PATCH] Add enable_copy_program GUC to control COPY PROGRAM

On Wed, Dec 3, 2025 at 4:08 PM Ignat Remizov <ignat980@gmail.com> wrote:
>
> 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.
>

If pg_execute_server_program is not granted to any user, the
functionality is already disabled right? Why do we need additional GUC
to enable/disable this feature?

--
Best Wishes,
Ashutosh Bapat

On Wed, Dec 3, 2025 at 6:07 PM Ignat Remizov <ignat980@gmail.com> wrote:
>
> Thanks for looking, Ashutosh.
>
> pg_execute_server_program is sufficient for non‑superusers, but superusers
> always bypass it. In the incident that prompted this, the attacker obtained
> superuser via weak/default creds on an exposed instance (common in shared dev
> or staging setups). From there, COPY PROGRAM is the simplest, most common RCE
> vector used by botnets. The GUC is a defense‑in‑depth knob to let an admin
> disable that specific path even for superuser, while leaving the feature
> available by default for existing users.
>
> The patch just removes the lowest‑hanging RCE primitive when you explicitly
> turn it off (requiring a restart, not ALTER SYSTEM/SET). Default remains on to
> preserve current behavior.
>

Adding a feature which allows a system to run with compromisable
superuser credentials doesn't seem like something the community
usually accepts.

--
Best Wishes,
Ashutosh Bapat

Ignat Remizov <ignat980@gmail.com> writes:
> pg_execute_server_program is sufficient for non‑superusers, but superusers
> always bypass it. In the incident that prompted this, the attacker obtained
> superuser via weak/default creds on an exposed instance (common in shared
> dev
> or staging setups). From there, COPY PROGRAM is the simplest, most common
> RCE
> vector used by botnets. The GUC is a defense‑in‑depth knob to let an admin
> disable that specific path even for superuser, while leaving the feature
> available by default for existing users.

This argument is nonsense, because if you've got superuser you can
just change the GUC's setting again.  Not to mention all the *other*
ways that a superuser can break out to the OS level.  I don't think
this proposal adds anything except more complication, which is not
a good attribute for security-critical considerations.

            regards, tom lane

On Wed, Dec 3, 2025, at 7:37 AM, Ignat Remizov wrote:
> 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.
>

You are blocking one of the various ways to run malicious code using the
postgres user. If it doesn't work the attacker will try another method. If you
want to prevent the majority of attacks, you need to forbid COPY [ TO | FROM ],
untrusted PLs, confine LOAD to a controlled list and/or path(s), large objects,
user-defined functions (LANGUAGE C), some file system access functions. (Maybe I
forgot other popular methods.) In summary, to (almost) close the gap that you
are concerned, you have to disallow some really popular features like COPY TO. I
don't think that's an acceptable solution. You are basically closing gap A but
there are still gap B, C and D.

> 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
>

A concrete plan involves thinking about (almost) all of these possibilities. If
we have a consensus on this topic there should be a central place to control
them.  Several options to close the gaps A, B, C and D is a really bad plan
when you can just pull the curtain aside.

> 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
>

That's not a review because I didn't read your patch. I would call it "enable"
because you cannot enable it unless you restart the service. Instead, I would
use "allow" (same verb as in allow_alter_system).

Why not GUC_DISALLOW_IN_FILE? A command-line option would be harder for the
attacker to control the proposed GUC. (Service files are generally owned by root
so to add/modify an option, it requires a non-default privileges.)

> Misc:
>
>   - Fixed a typo in a copy.c comment:
>       * "pstdout | pstdout" -> "pstdin | pstdout".
>

Please create a separate patch. That's an unrelated change.

> 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
>

We usually attach patches. Simple patches can be shared inline to rapidly
demonstrate your idea. IMO long inline patch takes some time to extract than if
you simply download it.


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/

On Wed, Dec 03, 2025 at 10:02:44AM -0500, Tom Lane wrote:
> This argument is nonsense, because if you've got superuser you can
> just change the GUC's setting again.  Not to mention all the *other*
> ways that a superuser can break out to the OS level.  I don't think
> this proposal adds anything except more complication, which is not
> a good attribute for security-critical considerations.

See also this recent discussion about a --with-copy-program compile flag:

    https://postgr.es/m/flat/CAGRrpza_WUY_jaN4P-xkN%3DTdqfxH%2BeJJazZAo5gg%3DkQoEaQnVw%40mail.gmail.com

-- 
nathan

On Wed, 3 Dec 2025 at 21:45, Ignat Remizov <ignat980@gmail.com> wrote:
>
> Thanks for the feedback Euler.
>
> On Wed, Dec 3, 2025 at 5:59 PM Euler Taveira <euler@eulerto.com> wrote:
> > You are blocking one of the various ways to run malicious code using the
> > postgres user. If it doesn't work the attacker will try another method. If you
> > want to prevent the majority of attacks, you need to forbid COPY [ TO | FROM ],
> > untrusted PLs, confine LOAD to a controlled list and/or path(s), large objects,
> > user-defined functions (LANGUAGE C), some file system access functions. (Maybe I
> > forgot other popular methods.) In summary, to (almost) close the gap that you
> > are concerned, you have to disallow some really popular features like COPY TO. I
> > don't think that's an acceptable solution. You are basically closing gap A but
> > there are still gap B, C and D.
>
> This patch is intentionally "small": it only removes the most commonly
> exploited primitive (COPY PROGRAM). I agree a more comprehensive approach
> would be ideal (covering extensions/untrusted PLs, LOAD, filesystem functions,
> etc.), but that would take more time to design and implement properly, and was
> already in my plans over the next few weekends, ideally via a single control
> point. Something that flips the assumption that superuser is a trusted role,
> and instead requires explicit enabling of potentially dangerous features.
>
> I saw the earlier discussion about seccomp() filters, but that seemed
> orthogonal to the problem of controlling what features are available to
> superusers as it is.
>
> This small step was what I put together first for my own use and to share.
>
> I initially considered allow_copy_program as a GUC name, but went with
> enable_copy_program to match other boolean GUCs like enable_nestloop,
> enable_hashagg, etc. The idea is that it enables the feature when true,
> instead of only allowing users to use it.
>
> I chose GUC_DISALLOW_IN_AUTO_FILE so that deploys could wire it into
> postgresql.conf. GUC_DISALLOW_IN_FILE seemed too restrictive for a
> control people might change later if they want to re-enable the feature
> after additional system hardening. The intent was to allow conf edits
> while blocking ALTER SYSTEM from a compromised superuser.
>

HI! As mentioned here and in nearby threads there is no security
boundary there between pg superuser and os.

Particularly, PGC_POSTMASTER restricts nothing, and
GUC_DISALLOW_IN_AUTO_FILE does not prevent superuser access to
postgresql configure file

Example:

```


db1=# show data_directory;
          data_directory
----------------------------------
 /home/reshke/spqrclusterdata/sh4
(1 row)
db1=# create table t(t text);
CREATE TABLE
db1=# insert into t values ('a=b');
INSERT 0 1
db1=# copy t to '/home/reshke/spqrclusterdata/sh4/postgresql.conf';
COPY 1
```

Even without COPY TO/COPY FROM feature, I believe there are no
practical way of preventic superuser to execute arbitrary code with OS
user privileges

--
Best regards,
Kirill Reshke

On Wed, 3 Dec 2025 at 23:02, Ignat Remizov <ignat980@gmail.com> wrote:
>
> On Wed, Dec 3, 2025 at 7:23 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
> > HI! As mentioned here and in nearby threads there is no security
> > boundary there between pg superuser and os.
> >
> > Particularly, PGC_POSTMASTER restricts nothing, and
> > GUC_DISALLOW_IN_AUTO_FILE does not prevent superuser access to
> > postgresql configure file
> >
> > Example:
> >
> > ```
> >
> >
> > db1=# show data_directory;
> >           data_directory
> > ----------------------------------
> >  /home/reshke/spqrclusterdata/sh4
> > (1 row)
> > db1=# create table t(t text);
> > CREATE TABLE
> > db1=# insert into t values ('a=b');
> > INSERT 0 1
> > db1=# copy t to '/home/reshke/spqrclusterdata/sh4/postgresql.conf';
> > COPY 1
> > ```
> >
> > Even without COPY TO/COPY FROM feature, I believe there are no
> > practical way of preventic superuser to execute arbitrary code with OS
> > user privileges
>
> Hi Kirill,
>
> This patch does not create a hard boundary between PostgreSQL superuser and
> the OS user. Making enable_copy_program PGC_POSTMASTER +
> GUC_DISALLOW_IN_AUTO_FILE blocks SET/ALTER SYSTEM; flipping the GUC requires
> editing postgresql.conf *and* a restart.

Yes, editing postgresql.conf and restarting. This is still the same as
editing postgresql.conf, efficiently.
requiring restart does not make the system any more safe.

For example, superuser can provoke postgresql to panic using plain sql
by corrupting critical files.
maybe something like
```
 copy (select 1) to '$datadir/global/pg_control'
```

will do. We can also corrupt pgwal. (I did derive the exact example
when postgresql immediately restarts after some SQL but im 100% there
is such thing )

--
Best regards,
Kirill Reshke

On Wed, 3 Dec 2025 at 23:17, I wrote:
> (I did derive the exact example
> when postgresql immediately restarts after some SQL but im 100% there
> is such thing )

Shame on me


select repeat('a',1024*1024*1023) from generate_series(1, 100);


-- 
Best regards,
Kirill Reshke

On Wed, Dec 3, 2025 at 9:03 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
> See also this recent discussion about a --with-copy-program compile flag:
>
>         https://postgr.es/m/flat/CAGRrpza_WUY_jaN4P-xkN%3DTdqfxH%2BeJJazZAo5gg%3DkQoEaQnVw%40mail.gmail.com

Yeah, these conversations tend to get stuck right at this point.
Restricting superuser so that it's somehow not superuser is a huge
(intractable?) undertaking. Doing it a piece at a time doesn't make a
lot of sense if we're not sure that an endpoint exists. But the
ability to escape from the database into the system around it still
seems like a legitimate concern.

A lot of work has been done recently to split apart these privileges
into smaller roles. So what if we just didn't hand out superuser by
default?

Could initdb be made to instead give you a user with the power to
manage almost all of the database (i.e. pg_maintain/pg_monitor), but
without the power to touch anything outside it or execute arbitrary
code? When you needed true superuser, you could still unlock it from
the outside, and at that point it shouldn't be surprising that you can
escape.

--Jacob

On Wed, Dec 03, 2025 at 11:35:07AM -0800, Jacob Champion wrote:
> Yeah, these conversations tend to get stuck right at this point.
> Restricting superuser so that it's somehow not superuser is a huge
> (intractable?) undertaking. Doing it a piece at a time doesn't make a
> lot of sense if we're not sure that an endpoint exists. But the
> ability to escape from the database into the system around it still
> seems like a legitimate concern.

Yeah, I have a feeling that we're going to continue to receive proposals in
this area.  Perhaps a good first step is to start listing all the
functionality that crosses the OS/database user boundary.  Then we might be
able to better approximate the effort required and whether we feel
comfortable maintaining such a boundary.

> A lot of work has been done recently to split apart these privileges
> into smaller roles. So what if we just didn't hand out superuser by
> default?
> 
> Could initdb be made to instead give you a user with the power to
> manage almost all of the database (i.e. pg_maintain/pg_monitor), but
> without the power to touch anything outside it or execute arbitrary
> code? When you needed true superuser, you could still unlock it from
> the outside, and at that point it shouldn't be surprising that you can
> escape.

IIRC there's been some discussion about that over the years, including in
my old thread about compiling out untrusted languages [0].

[0] https://postgr.es/m/flat/20220520225619.GA876272@nathanxps13

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> On Wed, Dec 03, 2025 at 11:35:07AM -0800, Jacob Champion wrote:
>> Could initdb be made to instead give you a user with the power to
>> manage almost all of the database (i.e. pg_maintain/pg_monitor), but
>> without the power to touch anything outside it or execute arbitrary
>> code? When you needed true superuser, you could still unlock it from
>> the outside, and at that point it shouldn't be surprising that you can
>> escape.

> IIRC there's been some discussion about that over the years, including in
> my old thread about compiling out untrusted languages [0].

I think the idea of putting training wheels on superuser is pretty
hopeless; there's too many ways in which that allows escape to the OS,
and even if we could close them all, the resulting system would be
very much less useful than today.

The right thing is to move people away from using superuser so much.
Compare this to the Unix root situation.  The OS guys have not tried
to cripple root, but they have started to offer setups where there's
no way to log in as root.  And there's protections like sshd not
allowing login as root (with its default settings anyway).  I like
Jacob's idea of requiring some external input, eg a config file
change, before you could become superuser.  I don't necessarily
want to be forced to operate in that world, but we could make it
easier to set up installations that have such restrictions.

            regards, tom lane