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

> On May 27, 2021, at 11:06 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>
> On Tue, May 25, 2021 at 01:33:54PM -0700, Mark Dilger wrote:
>> v3-0001 adds a new pg_logical_replication role with permission to manage publications and subscriptions.
>
>> v3-0004 adds a new pg_database_security role with permission to perform many
>> actions that would otherwise require superuser, so long as those actions do
>> not compromise the security of the host or network. This role, along with
>> pg_logical_replication, is intended to be safe to delegate to the tenant of
>> a database provided as a service.
>
> pg_logical_replication would not be safe to delegate that way:
> https://postgr.es/m/flat/CACqFVBbx6PDq%2B%3DvHM0n78kHzn8tvOM-kGO_2q_q0zNAMT%2BTzdA%40mail.gmail.com

Oh, I agree that this patch set does not go the extra step to make it safe. You are quite right to push back, as my email was poorly worded. I should have said "intended to be eventually made safe to delegate". The idea is that the patch set addresses most places in the sources where we test for superuser and tests instead for (superuser || <SOME_ROLE>), and then uses that same set of roles to control who has sufficient privileges to set GUCs. The pg_host_security and pg_network_security roles are not intended to eventually be safe to delegate. Or at least, I can't see any clear path to getting there. The pg_database_security and pg_logical_replication roles should be ones we can make safe. If we can agree as a community which set of roles are appropriate, then we can have separate patches as needed for tightening the security around them.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company