On Mon, Apr 19, 2021 at 10:31:18PM -0700, Mark Dilger wrote:
> I think you are conflating the concept of an operating system
> adminstrator with the concept of the database superuser/owner. If
> the operating system user that postgres is running as cannot execute
> any binaries, then "copy from program" is not a way for a database
> admistrator to escape the jail. If Bob does not have ssh access to
> the system, he cannot run pg_basebackup.
You don't need much to be able to take a base backup once you have a
connection to the backend as long as your have the permissions to do
so. In this case that would be just the replication permissions.
> The argument is that the operating system user that postgres is
> running as, perhaps user "postgres", can read the files in the
> $PGDATA directory, but Bob can only see the MVCC view of the data,
> not the raw data. Installing contrib/amcheck allows Bob to get a
> peak behind the curtain.
In my world, a superuser is considered as an entity holding the same
rights as the OS user running the PostgreSQL instance, so that's wider
than the definition you are thinking of here. There are many fancy
things one can do in this case, just to name a few that give access to
any files of the data directory or even other paths:
- pg_read_file(), and take the equivalent of a base backup with a
RECURSIVE CTE.
- BASE_BACKUP, doable from a simple psql session with a replication
connection.
- Untrusted languages.
So I don't understand your argument with amcheck here because any of
its features *requires* superuser rights unless granted. Coming back
to your example, Alice actually gave up the control of her database to
Bob the moment she gave him superuser rights. If she really wanted to
protect her privacy, she'd better think about a more restricted set of
ACLs for Bob before letting him manage her data, even if she considers
herself "safe" after she deleted it, but she's really not safe by any
means. I still stand with the point of upthread to put all that in
contrib/ for now, without discarding that this could be moved
somewhere else in the future.
--
Michael
