Re: pg_amcheck option to install extension

> On Apr 20, 2021, at 5:54 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Apr 20, 2021 at 1:31 AM Mark Dilger
> <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>> I think you are conflating the concept of an operating system adminstrator with the concept of the database superuser/owner.
>
> You should conflate those things, because there's no meaningful
> privilege boundary between them:

This discussion started in response to the idea that pg_amcheck needs to be moved into contrib, presumably because that's where amcheck lives. I am arguing against the move.

The actual use case I have in mind is "Postgres as a service", where a company (Alice) rents space in the cloud and runs postgres databases which can be rented out to a tenant (Bob) who is the owner of his database, but not privileged on the underlying system in any way. Bob has enough privileges to run CREATE EXTENSION, but is limited to the extensions that Alice has made available. Alice evaluates packages and chooses not to install most of them, including amcheck. Or perhaps Alice chooses not to install any contrib modules. Either way, the location of amcheck in contrib is useful to Alice because it makes her choice not to install it very simple.

Bob, however, is connecting to databases provided by Alice, and is not trying to limit himself. He is happy to have the pg_amcheck client installed. If Alice's databases don't allow him to run amcheck, pg_amcheck is not useful relative to those databases, but perhaps Bob is also renting database space from Charlie and Charlie's databases have amcheck installed.

Now, the question is, "In which postgres package does Bob think pg_amcheck should reside?" It would be strange to say that Bob needs to install the postgresql-contrib rpm in order to get the pg_amcheck client. That rpm is mostly a bunch of modules, and may even have a package dependency on postgresql-server. Bob doesn't want either of those. He just wants the clients.

The discussion about using amcheck as a privilege escalation attack was mostly to give some background for why Alice might not want to install amcheck. I think it got a bit out of hand, in no small part because I was being imprecise about Bob's exact privilege levels. I was being imprecise about that part because my argument wasn't "here's how to leverage amcheck to exploit postgres", but rather, "here's what Alice might rationally be concerned about." To run CREATE EXTENSION does not actually require superuser privileges. It depends on the package. At the moment, you can't load amcheck without superuser privileges, but you can load some others, such as intarray:

bob=> create extension amcheck;
2021-04-20 07:40:46.758 PDT [80340] ERROR: permission denied to create extension "amcheck"
2021-04-20 07:40:46.758 PDT [80340] HINT: Must be superuser to create this extension.
2021-04-20 07:40:46.758 PDT [80340] STATEMENT: create extension amcheck;
ERROR: permission denied to create extension "amcheck"
HINT: Must be superuser to create this extension.
bob=> create extension intarray;
CREATE EXTENSION
bob=>

Alice might prefer to avoid installing amcheck altogether, not wanting to have to evaluate on each upgrade whether the privileges necessary to load amcheck have changed.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company