Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

> On Aug 27, 2021, at 3:47 PM, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> Making roles owners of roles they create, and giving them the power to manipulate objects which belong to roles they own (recursively), seems to solve most of our problems we have been discussing.

Not hearing any responses, this part is implemented in v6-0018 and v6-0019.

> The remaining problem is that roles without createrole or superuser cannot create other roles. We don't want tenants to need either of those things, at least not as they are currently defined. We could either modify the createrole privilege to be far less powerful, or create a new privilege.
>
> If role owners can alter and drop roles they own (and ones those roles own, etc.) then we could redefine CREATEROLE to really just mean the ability to create new roles. The ability to alter or drop roles would not stem from having CREATEROLE, but rather from owning the role. For setups where one admin role has CREATEROLE and creates all other roles (except the superuser which created the admin) nothing changes. In setups with multiple admins, where none own the others, each admin would have its own fiefdom, managing everything downstream from itself, but having no special privilege over the other fiefdoms. I think that setup wasn't implemented for 8.1 more for lack of time than because it was unwanted.

This really deserves more thought from the list. CREATEROLE's behavior is unchanged in this patch set.

> On Aug 23, 2021, at 11:13 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
>> Patches v5-0002 through v5-0005 create four new roles for managing host resource settings, vacuum settings, autovacuum settings, and logging settings. That last one excludes "where to log" settings, because we don't want the role to be able to write to arbitrary locations on the server. Remaining guc variables not in these four categories continue to belong to the superuser.
>
> We do have a role today who is allowed to write to arbitrary locations
> on the server, so I wonder if for those log settings we'd include a
> requirement for the user to have both of those roles instead..?

Following your advice, pg_manage_logging_settings + pg_write_server_files is made sufficient to set "where to log" settings in v6-0005.

Patches v6-0002 through v6-0005 add roles intended to allow tenants to set values for a limited number of appropriate guc variables. That seems fine for the purpose of facilitating postgres-as-a-service.

There is another reason to have roles with the power to SET or ALTER SYSTEM SET guc variables, though. For large deployments of postgres databases in the cloud, being able to log in as a non-superuser role in order to configure the database means having one fewer reasons to need to allow superuser connections to the server. That's valuable for its own sake.

Patches v6-0006 through v6-0012 add yet more roles with authority to set additional guc variables. They don't go quite so far as including all gucs, but the majority of gucs are covered, and we can add additional groupings if anybody has suggestions.