> On Aug 27, 2021, at 3:47 PM, Mark Dilger <mark.dilger@enterprisedb.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
toneed either of those things, at least not as they are currently defined. We could either modify the createrole
privilegeto 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
toreally 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
(exceptthe 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
privilegeover the other fiefdoms. I think that setup wasn't implemented for 8.1 more for lack of time than because it
wasunwanted.
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@snowman.net> wrote:
>
>> Patches v5-0002 through v5-0005 create four new roles for managing host resource settings, vacuum settings,
autovacuumsettings, and logging settings. That last one excludes "where to log" settings, because we don't want the
roleto be able to write to arbitrary locations on the server. Remaining guc variables not in these four categories
continueto 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"
settingsin v6-0005.
Patches v6-0002 through v6-0005 add roles intended to allow tenants to set values for a limited number of appropriate
gucvariables. 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
deploymentsof postgres databases in the cloud, being able to log in as a non-superuser role in order to configure the
databasemeans having one fewer reasons to need to allow superuser connections to the server. That's valuable for its
ownsake.
Patches v6-0006 through v6-0012 add yet more roles with authority to set additional guc variables. They don't go quite
sofar as including all gucs, but the majority of gucs are covered, and we can add additional groupings if anybody has
suggestions.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company