[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

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.

--
Ignat Remizov

On Wed, Dec 3, 2025 at 1:31 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:

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

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:

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

Ashutosh,

I think there’s a misunderstanding. This doesn’t "allow" running with weak
superuser creds; it’s a hardening toggle for admins who already recognize
the risk and want to remove one of the easiest RCE primitives if superuser
does get compromised. Superuser remains fully trusted; the default stays on
to preserve behavior and is fully backwards compatible. When an operator
explicitly sets it to off and restarts, COPY … PROGRAM is blocked even for
superuser, reducing blast radius in misconfigured/legacy environments (e.g.,
weak/default creds on shared exposed dev/staging stacks).

The intent is defense-in-depth, not to encourage running with compromisable
credentials.

Tom Lane <tgl@sss.pgh.pa.us> writes:

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.

Tom,

A quick clarification: enable_copy_program is PGC_POSTMASTER and
GUC_DISALLOW_IN_AUTO_FILE. SET/ALTER SYSTEM both error (I added regression
tests), and a reload call won’t change it. The only way to flip it is to
edit
postgresql.conf (or startup params) and restart. So a compromised superuser
session cannot just turn it back on.

I agree it doesn’t sandbox superuser or block other breakout paths; the
intent
is narrowly to remove the most commonly exploited RCE primitive (COPY
PROGRAM)
when an admin explicitly opts out. Default remains on to preserve behavior.

--
Ignat Remizov

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/

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.

I'll make a separate patch for the typo. Thanks again.

--
Ignat Remizov

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:

/messages/by-id/flat/CAGRrpza_WUY_jaN4P-xkN=TdqfxH+eJJazZAo5gg=kQoEaQnVw@mail.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, 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.

From your example, a superuser can indeed overwrite postgresql.conf
(including
this GUC) using COPY or other mechanisms. But the attacker would then need
to
also restart the service somehow.

As far as I know at present, from SQL you cannot restart the postmaster to
make the change effective.

The threat model I am trying to address is the very common "compromised
superuser password over the wire" case, where the attacker has only a SQL
connection, no shell, and no ability to restart the service.

So the patch removes the one-line RCE primitive in the scenario currently
abused by botnets.

If an attacker already has enough control to edit config files and restart
the
service (or crash/restart it) or use other mechanisms, then yes, they can
regain code execution; this doesn’t sandbox the superuser. It just raises
the
bar in the common case by removing COPY PROGRAM when an admin explicitly
disables it.

If the consensus ends up being that we should instead design a more general
"dangerous features" control (covering all breakout paths) behind a single
switch, I would happily work on that in my free time. This patch is just a
small, concrete step in that direction that I already had working.

--
Ignat Remizov

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:

/messages/by-id/flat/CAGRrpza_WUY_jaN4P-xkN=TdqfxH+eJJazZAo5gg=kQoEaQnVw@mail.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

Hi Kirill,

These are great examples, thanks. I wasn't aware it was that easy to chain
config overwrite and crash/restart from plain SQL.

Taken together, that makes it clear this GUC buys less than I'd hoped, and
is probably not worth the extra complexity on its own.

Please consider this patch withdrawn for now. I'll go back and think about
a more comprehensive approach (e.g. a single control over superuser
features), and if something useful comes out of that I'll post a separate
proposal. I'll also play with the panic cases you mentioned as part of that.

Thanks again for the detailed explanations.

--
Ignat Remizov

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]/messages/by-id/flat/20220520225619.GA876272@nathanxps13.

[0]: /messages/by-id/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

On Wed, Dec 3, 2025, 22:01 Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

I definitely agree with the sentiment, but I also think that allowing
superuser to trivially run arbitrary shell commands is not needed for
basically any of our users. I can see how it's somewhat useful for
development, but the biggest benefactor of this command are by far
attackers. OSes also don't ship with tools installed that are only useful
for attackers, so why should we?

One idea would be to disallow FROM PROGRAM when connecting over the network
instead of a Unix socket. Because for development unix sockets should be
fine. Network FROM PROGRAM seems to really only serve attackers.

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.

Agreed that that's much better. I think we're still not at the point where
we can do this by default without basically everyone needing to turn it on.
But what we maybe can do, is only allow UNIX socket logins to super user by
default (maybe even only peer authentication). Requiring people to set a
dangerous_superuser_over_network GUC (in addition to configuring hba.

On Thu, 4 Dec 2025, 07:24 Jelte Fennema-Nio, <postgres@jeltef.nl> wrote:

On Wed, Dec 3, 2025, 22:01 Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

I definitely agree with the sentiment, but I also think that allowing
superuser to trivially run arbitrary shell commands is not needed for
basically any of our users. I can see how it's somewhat useful for
development, but the biggest benefactor of this command are by far
attackers. OSes also don't ship with tools installed that are only useful
for attackers, so why should we?

One idea would be to disallow FROM PROGRAM when connecting over the
network instead of a Unix socket. Because for development unix sockets
should be fine. Network FROM PROGRAM seems to really only serve attackers.

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.

Agreed that that's much better. I think we're still not at the point where
we can do this by default without basically everyone needing to turn it on.
But what we maybe can do, is only allow UNIX socket logins to super user by
default (maybe even only peer authentication). Requiring people to set a
dangerous_superuser_over_network GUC (in addition to configuring hba.

Hi! Superuser can change archive command to arbitrary bash, which is also
useful for attacker. What should we do in this case? We definitely cannot
restrict archive command management to localhost, are we?

On Thu, 4 Dec 2025 at 05:11, Kirill Reshke <reshkekirill@gmail.com> wrote:

Hi! Superuser can change archive command to arbitrary bash, which is also useful for attacker. What should we do in this case? We definitely cannot restrict archive command management to localhost, are we?

I'm curious why you think we cannot restrict archive command
management to localhost? I think we could even completely disallow
changing archive_command with ALTER SYSTEM, by marking it as
GUC_DISALLOW_IN_AUTO_FILE. What user is regularly changing their
archive_command through ALTER SYSTEM in practice, and why couldn't
they change postgresql.conf instead? And if any automation does that,
that could just as easy change postgresql.conf.

We'd still need to disallow writing postgresql.conf by superuser in
trivial ways, in particular COPY mytable TO
'/abs/path/to/datadir/postgresql.conf'. Maybe even disallow COPY
mytable to 'file', completely by default.

Yes, this means more is needed than just disallowing COPY PROGRAM. But
I really do think we could spend a little bit of effort to not make
attackers life's as easy as we do today, especially because these
features don't provide any benefit to the majority of our users. And
to make it clear that these blockages are not foolproof, we could
allow people to enable all this functionality again with a GUC like
"allow_trivial_exploits_with_superuser = true" (and add documentation
to make it clear that exploits with superuser access are always
possible, just not the most trivial ones).