pgsql: Prevent running pg_basebackup as root

Greetings,

* Michael Paquier (michael@paquier.xyz) wrote:

Prevent running pg_basebackup as root

Similarly to pg_upgrade, pg_ctl and initdb, a root user is able to use
--version and --help, but cannot execute the actual operation to avoid
the creation of files with permissions incompatible with the
postmaster.

This is a behavior change, so not back-patching is done.

While it's maybe not ideal, surely there isn't an actual issue if
pg_basebackup is run as root with -Ft, is there..?

There's possibly something to be said about the fact that we hard-code
the username/groupname in the tar file too (interestingly, we actually
do pass through the uid/gid..)- perhaps we should actually be passing
the username/groupname through, but if we did do something like that
then having pg_basebackup running as root would be necessary if we want
to preserve the file ownership.

In any case, sorry for not responding on this sooner (was traveling for
FOSDEM and such), but I'm not really convinced this is something we want
and it certainly breaks at least somewhat reasonable use-cases when you
think about using pg_basebackup with -Ft. In that vein, this change is
kinda like saying "you can't run pg_dump as root"..

Thanks,

Stephen

On Wed, Feb 05, 2020 at 12:22:59PM -0500, Stephen Frost wrote:

In any case, sorry for not responding on this sooner (was traveling for
FOSDEM and such), but I'm not really convinced this is something we want
and it certainly breaks at least somewhat reasonable use-cases when you
think about using pg_basebackup with -Ft. In that vein, this change is
kinda like saying "you can't run pg_dump as root"..

It seems to me that this is entirely different than the case of
pg_dump, as it is possible to restore a dump even as root, something
that cannot happen with physical backups without an extra chmod -R.
You have a point with -Ft as untaring the tarballs from a base backup
taken with pg_basebackup -Ft used by root generates files owned by the
original user. -Fp enforces the files to be owned by the user taking
the backup, which makes the most sense, so for consistency with the
other tools preventing root to run pg_basebackup makes sense to me
with -Fp. Any thoughts from others to restrict the tool with -Fp but
not with -Ft? The argument of consistency mattered for me first for
both formats.
--
Michael

On Thu, Feb 6, 2020 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Feb 05, 2020 at 12:22:59PM -0500, Stephen Frost wrote:

In any case, sorry for not responding on this sooner (was traveling for
FOSDEM and such), but I'm not really convinced this is something we want
and it certainly breaks at least somewhat reasonable use-cases when you
think about using pg_basebackup with -Ft. In that vein, this change is
kinda like saying "you can't run pg_dump as root"..

It seems to me that this is entirely different than the case of
pg_dump, as it is possible to restore a dump even as root, something
that cannot happen with physical backups without an extra chmod -R.

I don't see how that's relevant? And yes, you can restore physical
backups this way too, if the userids match. (though see Stephens
comment about the username, but that's independent of this issue)

And pg_basebackup is about taking backups, not restores :)

You have a point with -Ft as untaring the tarballs from a base backup
taken with pg_basebackup -Ft used by root generates files owned by the
original user. -Fp enforces the files to be owned by the user taking
the backup, which makes the most sense, so for consistency with the
other tools preventing root to run pg_basebackup makes sense to me
with -Fp. Any thoughts from others to restrict the tool with -Fp but
not with -Ft? The argument of consistency mattered for me first for
both formats.

I think having -Fp and -Ft consistent is a lot more important than
being consistent with other tools that aren't really that closely
related. And it's already inconsistent against probably the most
related command, being pg_dump.

So *very* strong objection to makeing -Fp and -Ft behave differently
in this regard.

I agree with Stephen that this seems to be misguided, and my vote is
to revert. I would've also objected had you given more than 2 days
warning before committing, and it happened to be during FOSDEM. I saw
the original email which clearly said it'd be in the March commitfest,
so I figured I'd have time...

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Greetings,

* Magnus Hagander (magnus@hagander.net) wrote:

On Thu, Feb 6, 2020 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Feb 05, 2020 at 12:22:59PM -0500, Stephen Frost wrote:

In any case, sorry for not responding on this sooner (was traveling for
FOSDEM and such), but I'm not really convinced this is something we want
and it certainly breaks at least somewhat reasonable use-cases when you
think about using pg_basebackup with -Ft. In that vein, this change is
kinda like saying "you can't run pg_dump as root"..

It seems to me that this is entirely different than the case of
pg_dump, as it is possible to restore a dump even as root, something
that cannot happen with physical backups without an extra chmod -R.

I don't see how that's relevant? And yes, you can restore physical
backups this way too, if the userids match. (though see Stephens
comment about the username, but that's independent of this issue)

Right.

And pg_basebackup is about taking backups, not restores :)

Yes- one of the downsides of pg_basebackup is that it doesn't really do
much for you when it comes to restores, in fact.. Something that will
have to change if it starts doing incrementals of some kind. That's
mostly orthogonal to this discussion though.

You have a point with -Ft as untaring the tarballs from a base backup
taken with pg_basebackup -Ft used by root generates files owned by the
original user. -Fp enforces the files to be owned by the user taking
the backup, which makes the most sense, so for consistency with the
other tools preventing root to run pg_basebackup makes sense to me
with -Fp. Any thoughts from others to restrict the tool with -Fp but
not with -Ft? The argument of consistency mattered for me first for
both formats.

Erm- no, with -Ft + untar-as-root they get owned by "postgres", NOT the
original user. That's what I was pointing out up-thread (since it seems
to be confusing- and clearly not always well understood..) and it's an
issue imv, but it's independent of this, so probably deserves its own
thread if someone wants to do something about that.

Having -Fp run-as-root result in the files being owned by root isn't
good and I agree that's unfortunate and it would be good to fix it, but
preventing pg_basebackup from ever being run as root isn't a good
solution to that issue.

I think having -Fp and -Ft consistent is a lot more important than
being consistent with other tools that aren't really that closely
related. And it's already inconsistent against probably the most
related command, being pg_dump.

Yeah, I agree on consistency here being important too, and that pg_dump
is a closer command to be thinking about than initdb and friends.

So *very* strong objection to makeing -Fp and -Ft behave differently
in this regard.

What we aren't consistent about today is what happens when you do:

- Backup as root with -Ft
- Untar results as root

- Backup as root with -Fp

and that really seems less than ideal, but I don't think the answer is
"don't allow backing up as root".

I agree with Stephen that this seems to be misguided, and my vote is
to revert. I would've also objected had you given more than 2 days
warning before committing, and it happened to be during FOSDEM. I saw
the original email which clearly said it'd be in the March commitfest,
so I figured I'd have time...

Yeah, I also agree with reverting this change. Even if we can come to
something we all agree on, I'm pretty confident it's not going to be
exactly this patch, so let's back it out for now and discuss it further
on the -hackers thread.

Thanks,

Stephen

On Thu, Feb 06, 2020 at 09:44:07AM -0500, Stephen Frost wrote:

* Magnus Hagander (magnus@hagander.net) wrote:

On Thu, Feb 6, 2020 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:

You have a point with -Ft as untaring the tarballs from a base backup
taken with pg_basebackup -Ft used by root generates files owned by the
original user. -Fp enforces the files to be owned by the user taking
the backup, which makes the most sense, so for consistency with the
other tools preventing root to run pg_basebackup makes sense to me
with -Fp. Any thoughts from others to restrict the tool with -Fp but
not with -Ft? The argument of consistency mattered for me first for
both formats.

Erm- no, with -Ft + untar-as-root they get owned by "postgres", NOT the
original user. That's what I was pointing out up-thread (since it seems
to be confusing- and clearly not always well understood..) and it's an
issue imv, but it's independent of this, so probably deserves its own
thread if someone wants to do something about that.

Hmm. I don't think that you are completely correct here either as it
depends on if the OS user "postgres" exists or not. As mentioned in
https://www.gnu.org/software/tar/manual/tar.html#SEC138, if the user
name cannot be found in /etc/passwd, then tar switches to the user ID
(if one does not have any user or group named "postgres", then the
files are untar'ed with the same user and group as the one running the
cluster and that's to the UID and GID set by tarCreateHeader, as you
say). I think that it is a problem to not have more documentation on
the matter (now there is just a small mention in the base backup
restore about being sure to have the proper permissions). And it may
be interesting to add into pg_basebackup options to enforce the user
and/or group similarly to what tar does with --owner and --group?

I agree with Stephen that this seems to be misguided, and my vote is
to revert. I would've also objected had you given more than 2 days
warning before committing, and it happened to be during FOSDEM. I saw
the original email which clearly said it'd be in the March commitfest,
so I figured I'd have time...

Yeah, I also agree with reverting this change. Even if we can come to
something we all agree on, I'm pretty confident it's not going to be
exactly this patch, so let's back it out for now and discuss it further
on the -hackers thread.

OK, done that part as of dcddc3f.
--
Michael

Hi,

On 2020-02-06 13:02:07 +0100, Magnus Hagander wrote:

I agree with Stephen that this seems to be misguided, and my vote is
to revert.

+1. I honestly don't think we should increase the number of "root
disallowed" tools unless actually necessary.

Maybe that's looking too far into the future, but I'd like to see
improvements to pg_basebackup that make it integrate with root requiring
tooling, to do more efficient base backups. E.g. having pg_basebackup
handle start/stop backup and WAL handling, but do the actual backup of
the data via a snapshot mechanism (yes, one needs start/stop backup in
the general case, for multiple FSs), would be nice.

Btw, I think it's good form in a discussion like this to CC the original
author. I'll also add a reference to this discussion from the -hackers
thread.

Greetings,

Andres Freund

On 2020/02/07 11:07, Andres Freund wrote:

Hi,

On 2020-02-06 13:02:07 +0100, Magnus Hagander wrote:

I agree with Stephen that this seems to be misguided, and my vote is
to revert.

+1. I honestly don't think we should increase the number of "root
disallowed" tools unless actually necessary.

Maybe that's looking too far into the future, but I'd like to see
improvements to pg_basebackup that make it integrate with root requiring
tooling, to do more efficient base backups. E.g. having pg_basebackup
handle start/stop backup and WAL handling, but do the actual backup of
the data via a snapshot mechanism (yes, one needs start/stop backup in
the general case, for multiple FSs), would be nice.

Btw, I think it's good form in a discussion like this to CC the original
author. I'll also add a reference to this discussion from the -hackers
thread.

Thanks for the notification.

Points raised upthread seem reasonable enough; to be honest I was expecting
this patch to hang around a bit longer anway, because (as so often) there's
some aspect which wouldn't have occurred to me.

Regards

Ian Barwick

--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Greetings,

* Michael Paquier (michael@paquier.xyz) wrote:

On Thu, Feb 06, 2020 at 09:44:07AM -0500, Stephen Frost wrote:

Erm- no, with -Ft + untar-as-root they get owned by "postgres", NOT the
original user. That's what I was pointing out up-thread (since it seems
to be confusing- and clearly not always well understood..) and it's an
issue imv, but it's independent of this, so probably deserves its own
thread if someone wants to do something about that.

Hmm. I don't think that you are completely correct here either as it
depends on if the OS user "postgres" exists or not.

Yes, I do know what happens if the named user doesn't exist, but in the
general case, where the 'postgres' user does exist, they'll get owned by
'postgres'.

As mentioned in
https://www.gnu.org/software/tar/manual/tar.html#SEC138, if the user
name cannot be found in /etc/passwd, then tar switches to the user ID
(if one does not have any user or group named "postgres", then the
files are untar'ed with the same user and group as the one running the
cluster and that's to the UID and GID set by tarCreateHeader, as you
say). I think that it is a problem to not have more documentation on
the matter (now there is just a small mention in the base backup
restore about being sure to have the proper permissions). And it may
be interesting to add into pg_basebackup options to enforce the user
and/or group similarly to what tar does with --owner and --group?

Yes, I agree with improving the documentation and with adding such
options.

I agree with Stephen that this seems to be misguided, and my vote is
to revert. I would've also objected had you given more than 2 days
warning before committing, and it happened to be during FOSDEM. I saw
the original email which clearly said it'd be in the March commitfest,
so I figured I'd have time...

Yeah, I also agree with reverting this change. Even if we can come to
something we all agree on, I'm pretty confident it's not going to be
exactly this patch, so let's back it out for now and discuss it further
on the -hackers thread.

OK, done that part as of dcddc3f.

Great, thanks!

Stephen

Greetings,

(Moving to -hackers, changing thread title)

* Andres Freund (andres@anarazel.de) wrote:

Maybe that's looking too far into the future, but I'd like to see
improvements to pg_basebackup that make it integrate with root requiring
tooling, to do more efficient base backups. E.g. having pg_basebackup
handle start/stop backup and WAL handling, but do the actual backup of
the data via a snapshot mechanism (yes, one needs start/stop backup in
the general case, for multiple FSs), would be nice.

The challenge with this approach is that you need to drop the 'backup
label' file into place as part of this operation, either by putting it
into the snapshot after it's been taken, or by putting it into the data
directory at restore time. Of course, you have to keep track of WAL
anyway from the time the snapshots are taken until the restore is done,
so it's certainly possible, as with all of this, it's just somewhat
complicated.

Certainly open to ideas on how to improve this.

Thanks,

Stephen

Hi,

On 2020-02-07 14:56:47 -0500, Stephen Frost wrote:

* Andres Freund (andres@anarazel.de) wrote:

Maybe that's looking too far into the future, but I'd like to see
improvements to pg_basebackup that make it integrate with root requiring
tooling, to do more efficient base backups. E.g. having pg_basebackup
handle start/stop backup and WAL handling, but do the actual backup of
the data via a snapshot mechanism (yes, one needs start/stop backup in
the general case, for multiple FSs), would be nice.

The challenge with this approach is that you need to drop the 'backup
label' file into place as part of this operation, either by putting it
into the snapshot after it's been taken, or by putting it into the data
directory at restore time. Of course, you have to keep track of WAL
anyway from the time the snapshots are taken until the restore is done,
so it's certainly possible, as with all of this, it's just somewhat
complicated.

It's not dead trivial, but also doesn't seem *that* hard to me compared
to the other challenges of adding features like this? How to best
approach it I think depends somewhat on what exact type of backup
(mainly whether to set up a new system or to make a PITR base backup)
we'd want to focus on. And what kind of snapshotting system / what kind
of target data store.

Plenty of snapshotting systems allow write access to the snapshot once
it finished, so that's one way one can deal with that. I have a hard
time believing that it'd be hard to have pg_basebackup delay writing the
backup label in that case. The WAL part would probably be harder, since
there we want to start writing before the snapshot is done. And copying
all the WAL at the end isn't enticing either.

For the PITR base backup case it'd definitely be nice to support writing
(potentially with callbacks instead of implementing all of them in core)
into $cloud_provider's blob store, without having to transfer all data
first through a replication connection and then again to the blob store
(and without manually implementing non-exclusive base backup). Adding
WAL after the fact to the same blob really a thing for anything like
that (obviously - even if one can hack it by storing tars etc).

Wonder if the the WAL part in particular would actually be best solved
by having recovery probe more than one WAL directory when looking for
WAL segments (i.e. doing so before switching methods). Much faster than
using restore_command, and what one really wants in a pretty decent
number of cases. And it'd allow to just restore the base backup
(e.g. mount [copy of] the snapshot) and the received WAL stream
separately, without needing more complicated orchestration.

Perhaps I am also answering something completely besides what you were
wondering about?

Greetings,

Andres Freund

Greetings,

* Andres Freund (andres@anarazel.de) wrote:

On 2020-02-07 14:56:47 -0500, Stephen Frost wrote:

* Andres Freund (andres@anarazel.de) wrote:

Maybe that's looking too far into the future, but I'd like to see
improvements to pg_basebackup that make it integrate with root requiring
tooling, to do more efficient base backups. E.g. having pg_basebackup
handle start/stop backup and WAL handling, but do the actual backup of
the data via a snapshot mechanism (yes, one needs start/stop backup in
the general case, for multiple FSs), would be nice.

The challenge with this approach is that you need to drop the 'backup
label' file into place as part of this operation, either by putting it
into the snapshot after it's been taken, or by putting it into the data
directory at restore time. Of course, you have to keep track of WAL
anyway from the time the snapshots are taken until the restore is done,
so it's certainly possible, as with all of this, it's just somewhat
complicated.

It's not dead trivial, but also doesn't seem *that* hard to me compared
to the other challenges of adding features like this? How to best
approach it I think depends somewhat on what exact type of backup
(mainly whether to set up a new system or to make a PITR base backup)
we'd want to focus on. And what kind of snapshotting system / what kind
of target data store.

I'm also not sure that pg_basebackup is the right tool for this though,
really, given the complications and how it's somewhat beyond what
pg_basebackup's mandate is. This isn't something you'd like do
remotely, for example, due to the need to take the snapshot, mount the
snapshot, etc. I don't see this as really in line with "just another
option to -F", there'd be a fair bit of configuring, it seems, and a
good deal of what pg_basebackup would really be doing with this feature
is just running bits of code the user has given us, except for the
actual calls to PG to do start/stop backup.

Plenty of snapshotting systems allow write access to the snapshot once
it finished, so that's one way one can deal with that. I have a hard
time believing that it'd be hard to have pg_basebackup delay writing the
backup label in that case. The WAL part would probably be harder, since
there we want to start writing before the snapshot is done. And copying
all the WAL at the end isn't enticing either.

pg_basebackup already delays writing out the backup label until the end.

But, yes, there's also timing issues to deal with, which are complicated
because there isn't just a syscall we can use to say "take a snapshot
for us" or to say "mount this snapshot over here" (at least, not in any
kind of portable way, even in places where such things do exist). Maybe
we could have shell commands that a user provides for "take a snapshot"
and "mount this snapshot", but putting all of that on the user has its
own drawbacks (more on that below..).

For the PITR base backup case it'd definitely be nice to support writing
(potentially with callbacks instead of implementing all of them in core)
into $cloud_provider's blob store, without having to transfer all data
first through a replication connection and then again to the blob store
(and without manually implementing non-exclusive base backup). Adding
WAL after the fact to the same blob really a thing for anything like
that (obviously - even if one can hack it by storing tars etc).

We seem to be mixing things now.. You've moved into talking about 'blob
stores' which are rather different from snapshots, no? I certainly agree
with the general idea of supporting blob stores (pgbackrest has
supported s3 for quite some time, with a nicely pluggable architecture
that we'll be using to write drivers for other blob storage, all in very
well tested C99 code, and it's all done directly, if you want, without
going over the network in some other way first..).

I don't really care for the idea of using callbacks for this, at least
if what you mean by "callback" is "something like archive_command".
There's a lot of potential failure cases and issues, writing to most s3
stores requires retries, and getting it all to work right when you're
going through a shell to run some other command to actually get the data
across safely and durably is, ultimately, a bit of a mess. I feel like
we should be learning from the mess that is archive_command and avoiding
anything like that if at all possible when it comes to moving data
around that needs to be confirmed durably written. Making users have to
piece together the bits to make it work just isn't a good idea either
(see, again, archive command, and our own documentation for why that's a
bad idea...).

Wonder if the the WAL part in particular would actually be best solved
by having recovery probe more than one WAL directory when looking for
WAL segments (i.e. doing so before switching methods). Much faster than
using restore_command, and what one really wants in a pretty decent
number of cases. And it'd allow to just restore the base backup
(e.g. mount [copy of] the snapshot) and the received WAL stream
separately, without needing more complicated orchestration.

That looks to be pretty orthogonal to the original discussion, but it
doesn't seem like a terrible idea. I'd want David's thoughts on it, but
it seems like this might work pretty well for pgbackrest- we already
pull down WAL in advance of the restore_command asking for it and store
it nearby so we can swap it into place about as fast as possible. Being
able to give a directory instead would be nice, although you have to
figure out which WAL is going to be needed (which timeline, what time or
recovery point for PITR, etc) and that information isn't passed to the
recovery_command currently. We are working presently on adding support
to pgbackrest to better understand the point in time being asked by the
user for a restore, and we have plans to scan the WAL and track recovery
points, and we should know the timeline they're asking for, so maybe
once all that's done we will just 'know' what PG is going to ask for and
can prep it into a directory, but I don't think it really makes sense to
assume that all of the WAL that might ever be asked for is going to be
in one directory or that users will necessairly be happy with having
what would potentially be a pretty large volume have all of the WAL to
perform the restore with. Having something fetch WAL and feed it into
the directory, maintaining some user-defined size, and then having
something (PG maybe?) remove WAL when done might work..

If we were doing all of this from scratch, or without a
'restore_command' kind of interface, I feel like we'd have 3 or 4
different patches to choose from that implemented s3 support in core,
potentially with all of this pre-fetching and queue'ing. The restore
command approach does mean that older versions of PG can leverage a tool
like pgbackrest to get these features though, so I guess that's a
positive for it. Certainly, one of the reasons we've hacked on
pgbackrest with these things is because we can support *existing*
deployments, whereas something in core wouldn't be available until at
least next year and you'd have to get people upgraded to it and such..

Perhaps I am also answering something completely besides what you were
wondering about?

There definitely are a few different threads and thoughts in here...
They're mostly about backups and PITR of some sort though, so I'm happy
to chat about them. :)

Thanks,

Stephen