From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
cc: Michael Banck <mbanck@gmx.net>, Jelte Fennema-Nio <postgres@jeltef.nl>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: [PATCH] New predefined role pg_manage_extensions
In-reply-to: 
 <CA+TgmoZ_JzehEBLPecSnHfavDvwd5beDWXSnyV7GZxUjXqhYZQ@mail.gmail.com>
References: <65a1524e.050a0220.49266.7670@mx.google.com>
 <CAGECzQQ2HB85N9PjTAdDTpFCciQmpeE2PcXbc8EKhSF=RPi3fA@mail.gmail.com>
 <CA+Tgmoa3OA+1T-SBDLkVqgYW1cFSjuSF=L_wh=CJM+k=P+8OAA@mail.gmail.com>
 <CAGECzQRjkyiQ9b4vB2UDwppX4T3_SNhjYxMog4jxCSc6PEPKag@mail.gmail.com>
 <CA+TgmoZw-+qLZnFSa-6PvkBVFa2iuJVTarP0EnRUgwBe-47XfA@mail.gmail.com>
 <67cb04a8.170a0220.4ffef.a81e@mx.google.com>
 <CA+TgmoZ_JzehEBLPecSnHfavDvwd5beDWXSnyV7GZxUjXqhYZQ@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Fri, 07 Mar 2025 10:56:59 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1021639.1741364484.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 07 Mar 2025 11:21:24 -0500
Message-ID: <1021640.1741364484@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1021640.1741364484%40sss.pgh.pa.us>
Precedence: bulk

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 7, 2025 at 9:37 AM Michael Banck <mbanck@gmx.net> wrote:
>> Also, I think there is case to be made that a cloud provider (or site
>> admin) would like to delegate the decision whether users with CREATE
>> rights on a particular database are allowed to install some extensions
>> or not. Or rather, assign somebody they believe would make the right
>> call to do that, by granting pg_manage_extensions.

> Hypothetically, somebody could want a feature at various levels of
> granularity. The most fine-grained would be something like: [1] allow
> user X to install extension Y. Then, more broadly, you could have: [2]
> allow any user who can install extensions to install extension Y. Or
> conversely: [3] allow user X to install any extension. This patch
> implements [3], but you could make an argument for any of the others.

It's not apparent to me how [3] is meaningfully different from
giving user X superuser.  If you have the ability to install and
use, say, file_fdw, then nothing except honesty stands between you
and a superuser bit.  Is the argument for this feature that cloud
providers won't realize that?  Or perhaps the argument is that the
provider will only provide pre-vetted extensions to install ---
but then the existing "trusted extension" feature does everything
they need.

While I'm all for chipping away at what superuser privilege is
needed for, we have to tread VERY carefully about chipping away
at things that allow any outside-the-database access.

			regards, tom lane