Re: Granting control of SUSET gucs to non-superusers

On Mon, May 3, 2021 at 2:41 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> In general, I agree that we should be looking at predefined roles as
> being similar to the Linux capabilities system- defining certain kinds
> of operations which the user who has that role is allowed to do, and
> then both in-core and extensions can make decisions based on what
> capabilities the user has been GRANT'd.

Cool.

> Hopefully that would limit the amount of cases where a given capability
> ends up being overly broad while at the same time allowing extensions to
> sensibly be able to use the defined capabilities for their own needs.

Yeah. I think it will be a little tricky to get right, as some of the
cases are a bit subjective, I think.

> As we do in other places, we should make it clear when a certain
> capability allows a user with that capability to gain superuser access
> as that may not always be clear to a user.

+1.

> One thing that seems missing from this discussion and is part of what
> paused my effort on the 'admin' role proposed towards the end of the
> last cycle is that we really need to consider how this all plays with
> ALTER SYSTEM and not just SUSET GUCs but also other (eg: POSTMASTER,
> SIGHUP) GUCs. That is- imv we should have a sensible solution for
> more-or-less all GUCs and which would allow a non-superuser to be able
> to set POSTMASTER and SIGHUP GUCs (and perhaps others..) through
> ALTER SYSTEM.

I missed the earlier discussion on this topic, but I agree that this
is very important. I think that the discussion of capabilities might
help us get there. For instance, if I'm a service provider, and I give
user "bob" the pg_put_whatever_you_want_in_the_server_log role, and
GUCs are tagged so we know what GUCs that affects, then it seems
natural to me to allow Bob to set those GUCs via ALTER SYSTEM as well
as via ALTER USER or ALTER DATABASE. However, if I don't give him the
pg_frob_shell_commands role, he can't set archive_command.

--
Robert Haas
EDB: http://www.enterprisedb.com