Re: [PATCH] New predefined role pg_manage_extensions

Hi,

Even though there has not been a lot of discussion on this, here is a
rebased patch. I have also added it to the upcoming commitfest.

On Sat, Jan 13, 2024 at 09:20:40AM +0100, Michael Banck wrote:
> On Fri, Jan 12, 2024 at 04:13:27PM +0100, Jelte Fennema-Nio wrote:
> > But I'm not sure that such a pg_manage_extensions role would have any
> > fewer permissions than superuser in practice.
>
> Note that just being able to create an extension does not give blanket
> permission to use it. I did a few checks with things I thought might be
> problematic like adminpack or plpython3u, and a pg_manage_extensions
> user is not allowed to call those functions or use the untrusted
> language.
>
> > Afaik many extensions that are not marked as trusted, are not trusted
> > because they would allow fairly trivial privilege escalation to
> > superuser if they were.
>
> While that might be true (or we err on the side of caution), I thought
> the rationale was more that they either disclose more information about
> the database server than we want to disclose to ordinary users, or that
> they allow access to the file system etc.
>
> I think if we have extensions in contrib that trivially allow
> non-superusers to become superusers just by being installed, that should
> be a bug and be fixed by making it impossible for ordinary users to
> use those extensions without being granted some access to them in
> addition.
>
> After all, socially engineering a DBA into installing an extension due
> to user demand would be a thing anyway (even if most DBAs might reject
> it) and at least DBAs should be aware of the specific risks of a
> particular extension probably?

Michael