Re: pg_amcheck option to install extension - Mailing list pgsql-hackers

> On Apr 20, 2021, at 5:54 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, Apr 20, 2021 at 1:31 AM Mark Dilger
> <mark.dilger@enterprisedb.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'swhere 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
runspostgres databases which can be rented out to a tenant (Bob) who is the owner of his database, but not privileged
onthe underlying system in any way.  Bob has enough privileges to run CREATE EXTENSION, but is limited to the
extensionsthat 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
isuseful 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
thepg_amcheck client installed.  If Alice's databases don't allow him to run amcheck, pg_amcheck is not useful relative
tothose 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
thatBob needs to install the postgresql-contrib rpm in order to get the pg_amcheck client.  That rpm is mostly a bunch
ofmodules, and may even have a package dependency on postgresql-server.  Bob doesn't want either of those.  He just
wantsthe clients. 



The discussion about using amcheck as a privilege escalation attack was mostly to give some background for why Alice
mightnot want to install amcheck.  I think it got a bit out of hand, in no small part because I was being imprecise
aboutBob's exact privilege levels.  I was being imprecise about that part because my argument wasn't "here's how to
leverageamcheck to exploit postgres", but rather, "here's what Alice might rationally be concerned about."  To run
CREATEEXTENSION does not actually require superuser privileges.  It depends on the package.  At the moment, you can't
loadamcheck 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
privilegesnecessary to load amcheck have changed. 

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