Обсуждение: [PATCH] New predefined role pg_manage_extensions

Hi,

I propose to add a new predefined role to Postgres,
pg_manage_extensions. The idea is that it allows Superusers to delegate
the rights to create, update or delete extensions to other roles, even
if those extensions are not trusted or those users are not the database
owner.

I have attached a WIP patch for this.


Thoughts?

Michael

On Fri, 12 Jan 2024 at 15:53, Michael Banck <mbanck@gmx.net> wrote:
> I propose to add a new predefined role to Postgres,
> pg_manage_extensions. The idea is that it allows Superusers to delegate
> the rights to create, update or delete extensions to other roles, even
> if those extensions are not trusted or those users are not the database
> owner.

I agree that extension creation is one of the main reasons people
require superuser access, and I think it would be beneficial to try to
reduce that. But I'm not sure that such a pg_manage_extensions role
would have any fewer permissions than superuser in practice. 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.

Hi,

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